Get data from sheet number, not sheet name

[tezza]

Hi all,

I don't know why I struggle so much with getting data from another sheet, but I do, and it's frustrating.

I just want to make a master sheet if you like using the following information:

Sheet number	A4	A5	A5
sheet4!B2	sheet4!A4	sheet4!A5	sheet4!A6
sheet5!B2	sheet5!A4	sheet5!A5	sheet5!A6
sheet6!B2	sheet6!A4	sheet6!A5	sheet6!A6
sheet7!B2	sheet7!A4	sheet7!A5	sheet7!A6

It's pretty basic really.

Starting at sheet 4 to how many tabs there are (sheet 4 - sheet xx)

Col A will be sheet 4 cell B2 - drag it down so only the sheet number increases - sheet 5 = B2 and so on.

Col B will be sheet 4 cell A4 - again drag down - sheet 5 A5

When I try =sheet4!B2 it pulls up another window looking for a file.

All cell values are in the same place on each sheet, I just want to put them all into one sheet.

Can you help please?

 

[Special-K99]

Try

in Column A
=INDIRECT("Sheet"&3+ROWS(INDIRECT("1:"&ROW(A1:A100)))&"!B2")
in Column B
=INDIRECT("Sheet"&3+ROWS(INDIRECT("1:"&ROW(A1:A100)))&"!A4")
in Column C
=INDIRECT("Sheet"&3+ROWS(INDIRECT("1:"&ROW(A1:A100)))&"!A5")
in Column D
=INDIRECT("Sheet"&3+ROWS(INDIRECT("1:"&ROW(A1:A100)))&"!A6")

If =sheet4!B2 is looking for a file then "sheet4" does not exist

 

[Joe4]

I am not sure I fully understand your question, as it is described. I find the post a bit confusing. However, I think that there are two pieces of information that may help you get what you want.

1. You can build dynamic range references with the INDIRECT function. You can create a formula that returns the range reference exactly how you need it, and then surround it in the INDIRECT function to let Excel know that it is a range reference and not literal text.

2. You can use the ROW() function to dynamically return the current row number that the formula is located in. Why is that helpful? Put =ROW() in any cell, and then drag it down, and you will see the values increasing by 1 as the row number increases by 1.

So let's say that in cell A4 on my sheet, I wanted to return the value from Sheet2, cell A4, and want to drag it down so that A5 returns the value from Sheet3, cell A4 and A5 returns the value from Sheet 4, cell A4, etc.

Then I just need to put a formula like this in cell A4 and copy down:

=INDIRECT("sheet" &ROW()-2 & "!A4")

Note that if you take the inner part only to see what it returns:

="sheet" & ROW()-2 & "!A4"

you will see that looks like the range reference that you desire:

sheet2!A4

Hopefully that help gives you the tools to do what you need to.

 

[tezza]

Try

in Column A
=INDIRECT("Sheet"&3+ROWS(INDIRECT("1:"&ROW(A1:A100)))&"!B2")
in Column B
=INDIRECT("Sheet"&3+ROWS(INDIRECT("1:"&ROW(A1:A100)))&"!A4")
in Column C
=INDIRECT("Sheet"&3+ROWS(INDIRECT("1:"&ROW(A1:A100)))&"!A5")
in Column D
=INDIRECT("Sheet"&3+ROWS(INDIRECT("1:"&ROW(A1:A100)))&"!A6")

If =sheet4!B2 is looking for a file then "sheet4" does not exist

It just shows a #REF! in the cell

I agree that "sheet4" doesn't exist, but I'm looking for the 4th sheet, no matter what the name is.

 

[Joe4]

I agree that "sheet4" doesn't exist, but I'm looking for the 4th sheet, no matter what the name is.

I don't know that you can do that with an Excel function - something like that may require VBA.

 

[Special-K99]

It just shows a #REF! in the cel

I agree that "sheet4" doesn't exist, but I'm looking for the 4th sheet, no matter what the name is.

Formula works for me.
More importantly your problem is this...

Re - Sheet4 doesn't exist:

That's not what "Sheet4!B2" means.
Sheet4!B2 returns the cell value in B2 on the sheet NAMED Sheet4, not the 4th sheet in the workbook.

Not sure there is a formula that refers to the "4th sheet", VBA solution maybe, I believe most, if not all, formulas have to contain the NAME of the sheet.

 

[tezza]

I am not sure I fully understand your question, as it is described. I find the post a bit confusing. However, I think that there are two pieces of information that may help you get what you want.

1. You can build dynamic range references with the INDIRECT function. You can create a formula that returns the range reference exactly how you need it, and then surround it in the INDIRECT function to let Excel know that it is a range reference and not literal text.

2. You can use the ROW() function to dynamically return the current row number that the formula is located in. Why is that helpful? Put =ROW() in any cell, and then drag it down, and you will see the values increasing by 1 as the row number increases by 1.

So let's say that in cell A4 on my sheet, I wanted to return the value from Sheet2, cell A4, and want to drag it down so that A5 returns the value from Sheet3, cell A4 and A5 returns the value from Sheet 4, cell A4, etc.

Then I just need to put a formula like this in cell A4 and copy down:

=INDIRECT("sheet" &ROW()-2 & "!A4")

Note that if you take the inner part only to see what it returns:

="sheet" & ROW()-2 & "!A4"

you will see that looks like the range reference that you desire:

sheet2!A4

Hopefully that help gives you the tools to do what you need to.

I think there is an issue with my sheet, as I open a new sheet and used some basic data and yours worked fine. I could do

sheet2!A4

On the spreadsheet I wanted to use it on, that code won't work and I've no idea why. It must have something to do with the current macros in place.

I appreciate your time though, thank you.

 

[tezza]

I don't know that you can do that with an Excel function - something like that may require VBA.

After testing further, I think you're right.

How would I go about using VBA to do what I'm after?

On sheet3 make a list From the 4th tab onwards, of what's in B2, A4, A5, A6 on each sheet?

 

[Joe4]

In VBA, if you wanted to refer to the 4th sheet in your workbook, regardless of name, you can use:

Sheets(4)

For example, if you wanted to get name of the 4th sheet, it would look something like this:

MsgBox Sheets(4).Name

So you would probably either need to get the name of the sheet and build the formulas using that, or have VBA populate your desired values directly via a loop (and skip the formula creation part altogether).