Can Indirect adressing use a cell value to change the "sheet" value?

Good evening

The work here is to prepare a large spreadsheet for Cancer Research. Intended for physical chemistry undergraduates and doctorate researchers, the idea is to give them a map of the whole spreadsheet (an Index, a “yellow pages”) so that they can select the z values from a different sheet that will be investigated compared to an x,y plot on the sheet where they work.

To do this, at the base of a column of data (of 248 rows), they’re offered the column number of a different sheet (which they can look up in the ‘map’ or ‘index’). The column above (for fetching that data) is programmed: =INDIRECT(ADDRESS(248,AQ264,1,“Cancerdata”))

(this is the formula at cell AQ248 only, for the example).

In column AQ, AQ264 is the cell (highlighted in bright yellow) where they choose the other sheet column number. It works very well. The difficulty is that “cancerdata” is not the only sheet involved. It can be any other disease or indicator of disease.

Some user effort is required (of course), so it were ideal if they type once in one cell (correctly it’s hoped) the name of the sheet to be fetched - Cancerdata; the intention is that their typing changes the “abcd” value in all the rows of the column above in quotes. Indirect adressing of a cell value to modify indirect adressing of a column to fetch data from a different sheet.

Without this, it’s possible but laborious to edit the whole column above using ‘find and replace’. I can do it to construct the spreadsheet (as author) yet have my doubts that users (3000 or more international) will find an IT specialist just around the corner to do it for them.

I hope this question is clear, ready for questions if not.

One solution could be to not use a literal string in the formula expression but a cell reference or better a named cell that contains a sheet name to use. So (under Sheet → Named Ranges and Expressions) define a named range SheetName or some such pointing to, for example, A1, and let your formula be


To help the user you can setup data validity for A1 (under Data → Validity) and define a list with possible sheet names. Best and easiest to maintain would be to use a cell range that contains a list of names, so for Criteria, Allow, select Cell range and specify the cell range reference as Source.

Hi Eike

All done precisely as you describe. Works. Marvellous! There already was an Index sheet, so the Cell Range criteria is kept/looks there for the source. The columns of data change and all x,y,z scatter plots update. In the range I added text hints for the user to edit the Index if needed (40 sheet names should be enough!).

Bionic Beaver is extremely stable OS too. Thankyou for the method. Thankyou for the photos of the LO Conferences and yachting. - purrrified website! Matt.

Thanks :slight_smile:
If the answer helped to solve the problem then please Up-vote and click the check mark to mark the answer as being correct.