Excel question: progressing sheet number in formula

ugaboy

New Member
Joined
Nov 16, 2005
Messages
3
How do I progress a sheet number in a formula. For example if I would like to generate a list of the entries that are in the same cell in several worksheets. For the first sheet, I have typed =sheet1!A1. Then I would like to repeat this formula with the next cell =sheet2!A1 and so on. Is there any way to "automate" this? Any help is appeciated.
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,458
Office Version
  1. 365
Platform
  1. Windows
WELCOME TO THE BOARD!

If you want row 1 to return the value from Sheet1, row 2 to return the value from Sheet2, etc., then enter this formula on row 1 and copy down:
=INDIRECT("Sheet" & ROW() & "!A1")
 

ugaboy

New Member
Joined
Nov 16, 2005
Messages
3
Thanks for your assistance...but

what I actually need is for the cell (row and column) to stay the same as I copy the formula down but I need the sheet # to progress by one each time. Any ideas?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,458
Office Version
  1. 365
Platform
  1. Windows
ugaboy,

Did you try my response? That is exactly what it does (note the "A1" is hardcoded in the formula).

What the ROW() function is doing is telling it which Sheet to look at. If the formula is entered on Row 1, the formula will look at Sheet1, if the formula is on row 2, it looks at Sheet2, etc. Hence, if you are copying your formula down the column, it will do exactly what you want it to.

Enter the function without the INDIRECT part, and you will see what it is returning.

="Sheet" & ROW() & "!A1"

You just need to use INDIRECT function when doing range references based on a formula.
 

ugaboy

New Member
Joined
Nov 16, 2005
Messages
3
I'm sorry

I misunderstood your original post. Sorry about that. I just tried your suggestion and it worked. Thanks so much, I really appreciate your help. Thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,909
Messages
5,574,983
Members
412,630
Latest member
Eireangel
Top