Referencing a sheet by index number with formula (no VBA)

[DashboardNovice]

I have a combo box which returns a value to cell B4 on the first sheet. Based on the value returned, I want to use that number as an index and refer to the nth sheet in my workbook, then to a particular cell on that worksheet.

For example, the combo box returns a value of 2. Therefore I want to refer to the 2nd sheet in my workbook. I want to refer to that sheet by index, not by the sheet name. After referring to that sheet by index, then I want to refer to a particular cell on the 2nd sheet.

I was thinking something like =sheets(B4)!A2. So if the value in B4 on the first sheet was 2, this formula would go to the 2nd sheet, and then cell A2, and return whatever was in cell A2.

I don't want to use VBA. There is a function called sheets, but that just returns the total number of sheets in the workbook.

Does anybody have any ideas? There has got to be a way to do this.

 

[Hui]

You can use the Indirect() function
eg:
A direct Cell
=Indirect("Sheet"&B4&"!A2")

Sum a Range
=sum(Indirect("Sheet"&B4&"!A2:A10")

Or you can use something more complex like:
=INDEX(CHOOSE(B4,Sheet1!A2:B10,Sheet2!A2:B10,Sheet3!A2:B10),1,1)

 

[Smallman]

Hi DN

I think you need a table where you have your sheet names in the order they appear (left to right in your sheet but the list should be from top to bottom. For example the top name (C2) is the first sheet, second name (C3) is the second etc. Then you can use the indirect function to draw on the number your combo box comes up with. Here is an example.

=INDIRECT(OFFSET(C1,B4,)&"!"&"A2")

I test this at my end and it works OK.

Take care

Smallman

 

[DashboardNovice]

Hi DN

I think you need a table where you have your sheet names in the order they appear (left to right in your sheet but the list should be from top to bottom. For example the top name (C2) is the first sheet, second name (C3) is the second etc. Then you can use the indirect function to draw on the number your combo box comes up with. Here is an example.

=INDIRECT(OFFSET(C1,B4,)&"!"&"A2")

I test this at my end and it works OK.

Take care

Smallman

Hi Smallman,

This is exactly what I had in mind after replying to Hui (post #3) right here:
http://chandoo.org/forum/threads/changing-the-value-returned-by-an-option-button-w-o-vba.16666/

But I am having trouble with the cell reference on the destination sheet.

 

[Smallman]

@DashboardNovice

The attached file is an example of what I was talking about. I think it is a bit of a long way around the issue as I would have the sheet names in the combo box as this takes 1 step out, but should get you started.

Take care

Smallman

 

[DashboardNovice]

@DashboardNovice

The attached file is an example of what I was talking about. I think it is a bit of a long way around the issue as I would have the sheet names in the combo box as this takes 1 step out, but should get you started.

Take care

Smallman

Thank you sir. Hui replied to me last night so I don't want to reinvent my spreadsheet, however I will keep your solution in mind because I'm sure it will help me in the future.