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.
Matthew
