Ask Your Question
0

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

asked 2018-10-07 20:20:41 +0200

Archetype gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2018-10-11 17:14:55.226789

1 Answer

Sort by » oldest newest most voted
0

answered 2018-10-08 15:10:50 +0200

erAck gravatar image

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

=INDIRECT(ADDRESS(248,AQ264,1,,SheetName))

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.

edit flag offensive delete link more

Comments

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.

Archetype gravatar imageArchetype ( 2018-10-09 13:00:11 +0200 )edit

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

erAck gravatar imageerAck ( 2018-10-09 17:36:05 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2018-10-07 20:20:41 +0200

Seen: 31 times

Last updated: Oct 08 '18