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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,243
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
63,243
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,498
Messages
5,832,060
Members
430,109
Latest member
tinezi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top