Calling on and Excel Guru

Gardenstate · 12-21-2006, 04:41 AM

I've got 1 exel document with sheets of information on different tabs. On one of these sheets, I have vertical columns of data that are the products of some basic adding formulas from data elsewhere in the sheet. We'll say that one column runs from A1 - A50, and that the sheet is called 'TEST'.

I want to convert these to horizontal stretches of data on a different sheet, and so in column A1 of the second sheet, I would go ='TEST'!A1 This gives me the value. Now I want to copy that formula and paste it horzontally so that it would run ='TEST'!A2 and ='TEST'!A3 etc. However, if you do this the formula will instead go in the follow this sequential pattern ='TEST'!B1 and ='TEST'!C1 etc.

Even if I manually enter the first 2 or 3 formulas, and then try to copy and paste so that excel can logically follow what I am doing, it won't work.

Any suggestions!!

**Glaswegian** · 12-21-2006, 02:11 PM

Hi

Easiest way is to use ASAP Utilities Add-in - it has a function that allows you to transpose values and formulae - it's exactly what you need.

http://www.asap-utilities.com/

Gardenstate · 12-22-2006, 04:31 AM

Thanks Glaswegian. Never knew this existed, so I'm going to give it a whirl

Gardenstate · 12-22-2006, 07:51 AM

I've downloaded it, and there's actually a load of really useful new functions on there. Unfortunately I still can't figure out how to solve my problem, even though there is a function called 'transpose data with formulas'. I just can't figure out how to use it properly.

DK0 · 12-23-2006, 09:21 PM

try copy
=OFFSET('TEST'!$A$1,(ROW()-1)*5+COLUMN()-1,0)
in your second sheet then copy across

12-21-2006, 04:41 AM	#1 (permalink)
Gardenstate Registered User Join Date: Sep 2006 Posts: 6 OS: XP	Calling on and Excel Guru I've got 1 exel document with sheets of information on different tabs. On one of these sheets, I have vertical columns of data that are the products of some basic adding formulas from data elsewhere in the sheet. We'll say that one column runs from A1 - A50, and that the sheet is called 'TEST'. I want to convert these to horizontal stretches of data on a different sheet, and so in column A1 of the second sheet, I would go ='TEST'!A1 This gives me the value. Now I want to copy that formula and paste it horzontally so that it would run ='TEST'!A2 and ='TEST'!A3 etc. However, if you do this the formula will instead go in the follow this sequential pattern ='TEST'!B1 and ='TEST'!C1 etc. Even if I manually enter the first 2 or 3 formulas, and then try to copy and paste so that excel can logically follow what I am doing, it won't work. Any suggestions!!

12-21-2006, 02:11 PM	#2 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,505 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Hi Easiest way is to use ASAP Utilities Add-in - it has a function that allows you to transpose values and formulae - it's exactly what you need. http://www.asap-utilities.com/ __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

12-22-2006, 04:31 AM	#3 (permalink)
Gardenstate Registered User Join Date: Sep 2006 Posts: 6 OS: XP	Thanks Glaswegian. Never knew this existed, so I'm going to give it a whirl

12-22-2006, 07:51 AM	#4 (permalink)
Gardenstate Registered User Join Date: Sep 2006 Posts: 6 OS: XP	I've downloaded it, and there's actually a load of really useful new functions on there. Unfortunately I still can't figure out how to solve my problem, even though there is a function called 'transpose data with formulas'. I just can't figure out how to use it properly.

12-23-2006, 09:21 PM	#5 (permalink)
DK0 Registered User Join Date: Jul 2006 Posts: 25 OS: XP	try copy =OFFSET('TEST'!$A$1,(ROW()-1)*5+COLUMN()-1,0) in your second sheet then copy across