CHOOSE Function Question

**CycloneBrian** · 09-28-2011, 02:15 PM

Long time lurker, first time poster...

Love this site

Have tried searching this question and either can't find it or I don't know what to search.

I have a tab called Master which uses the Choose() formula to retrieve information from other tabs in the same file to populate the master page. As I get new information I add another page(tab) and fill info in on that tab.

So say today I have 3 pages where the tabs are Sheet 1, Sheet 2 and Sheet 3, my Master tab page has a Formula in Cell B2 as follows:

=CHOOSE($A$1,'Sheet 1'!B2,'Sheet 2'!B2,'Sheet 3'!B2)

$A$1 on master page is where I get a number based on what Sheet information I am looking for. That is updating fine and works correctly when adding a new page.

Say I get new info so I can add Sheet 4 and type in the information in there and then make that available as an option for the Master Page, I then have to go alter the equation in every cell using the Choose() formula so it shows as follows:

=CHOOSE($A$1,'Sheet 1'!B2,'Sheet 2'!B2,'Sheet 3'!B2,'Sheet 4'!B2)

Then I can retrieve information from any individual Sheet from 1 to 4 but I have a lot of formulas in the Master page so updating the formula each time I add a sheet takes me quite a bit of time. Is there an easy way to do this?

I tried to go and change the formula on the master page so I had sheet numbers from 4 to 10 even though I didn't have sheet names above 4 thinking it would work once I got the sheet added by the correct name, but I did that and get REF# errors on every cell that has the choose function even though the number in $A$1 works as it's supposed to and the names on the sheet tab and in the formula match.

Is there any easy way for me to save all this work?
Thank you in advance.....

**Colin Legg** · 09-28-2011, 02:21 PM

Welcome to the forum.

One choice would be:

=INDIRECT("'Sheet "&$A$1&"'!B2")

**CycloneBrian** · 09-28-2011, 02:29 PM

Wow, that was fast and looks to be exactly what I needed.

Is there a way I can drag that formula and make it work relatively? As in if I type it as you shown and drag it once to the right it would fill C2 or is that asking too much.

It would take a while to update all the cells in each cell (over 400) but I guess I would only have to do it once and it would be done.

Thanks a lot

**CycloneBrian** · 09-28-2011, 03:30 PM

Originally Posted by Colin Legg

Welcome to the forum.

One choice would be:

=INDIRECT("'Sheet "&$A$1&"'!B2")

Thanks a lot for your help. And I figured out how to make it so you could drag the formula as well in case anyone else searches something like this.

I added a row at the top and typed in the column letter in each top cell. I then added a column to the right and typed in the row number in each cell of the column I needed.

Then I changed my formula to :

=INDIRECT("'Sheet "&$A$1&"'!"&B$1&""&$A2)

And I was able to drag it.

Anyway. Thanks a ton. Hopefully I can help someone along the way.