HELP! Simple thing proving tricky! Horizontal/Vertical formula auto copy

**Pudding** · 07-22-2012, 07:30 AM

Something I thought would be simple is proving to be a nightmare because I can't auto copy the formulas correctly.

I want to import totals from other sheets in the document. Problem is that the sheet I'm working on is in horizontal format and the sheets where the data is are in vertical format. When I auto copy my formula it increases the formula horizontally rather than vertically. Any suggestions???

ie using formula =SUM(Income!A20)

I want the cell underneath to be =SUM(Income!B20) however when I auto drag the formula it goes =SUM(Income!A21)

I've tried using =sum(Income!A$20) but doesn't work.

I can't manually input all the formulas going forward as it is far too time consuming!!

**martindwilson** · 07-22-2012, 07:33 AM

what is your real formula..you wouldn't use =SUM(Income!A20)
its easy enough using index to do this sort eg if you want a20,b20,c20........
when dragging down
=INDEX(income!A:AA,20,ROWS($A$1:A1))
or
=INDEX(income!$20:$20,ROWS($A$1:A1))

**Pudding** · 07-22-2012, 08:53 AM

In order to select the correct field I typed =sum( then clicked on relevant cell - then closed brackets.

What should I have done?

I thought I was quite good at Excel but I'm starting to realise I haven't even scratched the surface!

**martindwilson** · 07-22-2012, 08:55 AM

just
=income!A20
will do to reference a single cell

**Pudding** · 07-22-2012, 09:06 AM

Thanks for your help! Unfortunately I'm just as confused as I was.

Entering =income!A20 imports exactly the same info as =sum(income!A20)

So when I try to copy the formula drag down - wanting the cell underneath to say =income!B20 (...C20/D20/E20 etc) - it still thinks I want it to say =income!A21 (...A22/A23/A24 etc).

**martindwilson** · 07-22-2012, 09:39 AM

i told you the answer in post#2!

**Cutter** · 07-22-2012, 09:42 AM

Try this

=INDEX(Income!$20:$20,ROW(1:1)) dragged down

**Pudding** · 07-22-2012, 10:01 AM

THANK YOU, THANK YOU, THANK YOU!!1

I thought it didn't work but it was just me being a numpty.

Really appreciate the help!

**Cutter** · 07-22-2012, 10:08 AM

If you're talking to me, you're welcome. Oh, and welcome to the forum!

Please remember to mark your thread as Solved.

New quick method:
Select Thread Tools (above your first post) -> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save