Want to pull values based on same cell # but from multiple sheets

[pamc]

I need to pull a list of values from multiple sheets in another file. Eventually will need this to go from Sheet 1 to about 200. Hope there's an easy way & can replicate this formula all the way down so I won't have to keep changing the Sheet# for each.

Here are the values I pulled as an example & the formula used beside it.

Thanks in advance for any help!

Contract#
BE1308 ='[2013 E-Flyers Results Backup - Fishing.xlsx]Sheet1'!$B$6
BE1292 ='[2013 E-Flyers Results Backup - Fishing.xlsx]Sheet2'!$B$6
BE1290 ='[2013 E-Flyers Results Backup - Fishing.xlsx]Sheet3'!$B$6

[Mayda89]

You can easily do it with indirect function!
Before writing a sample formula, I need to know what is the condition for changing sheet1 to sheet2 to sheet3...
Should the sheet# be incremented by 1 for each increment in row number, or is there another rule?

[pamc]

Yes, incremented by 1 for each increment in row number. Thanks!

[FDibbins]

Hi

Not sure if this will work for you, it is dependant on the sheets having a number...but maybe you can modify it to fit?

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

[FDibbins]

duplicate post

[Mayda89]

Looks like FDibbins has already done it

[pamc]

Test.xlsx2013 E-Flyers Results Backup - Fishing.xlsxI've never used that before! I attached the two spreadsheets if you wouldn't mind helping me get started.

[pamc]

Still lost on this.

FDibbins or Mayda89 - will you please take a look at my spreadsheets attached & put in the first couple of formulas?

Thanks again!

[Mayda89]

Hi,

In the workbook you uploaded, I realized that you did not put the indirect function.
Anyway, I entered the formula in the cell A3.
It works smoothly.
Careful: Although you can copy this formula down, (at it will update the sheet number without problem), if you copy this formula to the cells on the right, I think you should modify the cell reference.

For example, in the contract code column, you want to lookup cell $B$6, because in the source sheet it is the cell that contains the contract info. When you copy this formula to the cell in the right, which is supposed to look up flyer name info, the formula will still lookup cell $b$6, which is a problem. You need to change it to the cell address you're interested.

P.S: In the formula, you might see this string as the address: "C:\Users\Gokalp\Downloads\"
If not, no problem. If you do see this, change it to suit the location of the workbook of interest in your own computer.

Cheers,

[pamc]

Thanks Mayda89!! That works perfectly!

[Mayda89]

Glad to help