Reference sheet name from cell value

[Elso]

Hello

There was a similar questions posted I think but it was so vague I really couldn’t understand what was actually be asked or the answer provided. So please do excuse me if this has been answered already.

I want to use a cell value to reference a sheet.
Column “B” contains item_code that matches a sheet name for example TILKGW68

Price column for example contains =TILKGW68.F2.

Challenge: Instead of having to spell out each sheet name in the
Price column I would like to reference column B.

I have a simple example ODS file to attach but it seems I am too new and need 3 points before i can upload. Since I am new, I don’t know what these points are or how to acquire them. Hopefully I have included enough detail above to explain my objective.

Thank you

[Lupp]

Sorry. I did not understand. I cannot imagine how to get a reference from a value. Do you want to extract the sheetname from the reference occurring in the formula in, say, cell B2 of the (different) current sheet? What do you mean by “spell out” in the context? I will try an answer assuming I guessed right.

[Lupp]

I am really wondering for what reason you think to need what you asked for?

[Elso]

Thanks for your comment. To satisfy your curiosity, the first sheet is a product list that a copy would be saved as a csv file for my web site. The other sheets are volume discount pricing structure, one sheet per product. Sheets are named with the product code such as TILKGW68 in my example. Cell F2 in every sheet is the single or default price that needs to be in my csv file for my site. If I do any price structure updates, I would like the default price to automatically keep current.

[Elso]

By using the column B value in the first sheet in the call for the price, then I can simply copy/paste this call to every price cell in that column instead of having to type it in for each item.

[Lupp]

If, say, B2 is containing a formula just consisting of the equal sign and a reference into a different sheet, you can get the sheetname by standardfunctions as shown here:

=SUBSTITUTE(MID(SUBSTITUTE(FORMULA(B2);"$";"");2;FIND(".";SUBSTITUTE(FORMULA(B2);"$";""))-2);" ";"")

If you can assure that there are no superfluous spaces and no $-character (absolute referencing) in front of the sheetname, you can use the much simpler formula:

=MID(FORMULA(B2);2;FIND(".";FORMULA(B2))-2)

[mark_t]

If the price is always in cell F2 of the sheet named by column B then you could use the INDIRECT function for the price column.

=INDIRECT($B2 & ".F2")

Assuming this is on row 2 of your spreadsheet and you then copy this cell to the other rows of your spreadsheet for the price column.

Note. F2 does not include $ as this is in quotes as a string and will not be modified when the formula is copied to other cells.

[Elso]

Thank you, this is exactly what I needed to do. I am just starting to find my way around the latest help/documentation but I didn’t really know what to be searching for in the help file.

[YuriR.Sucupira]

I wanted to vote this answer up aswell, but because of the nonsensical “5 points required to upvote” rule (that ends up discouraging us to login and participate in these forums), I’m unable to do so. Hence, here’s my “thank you, it worked” comment.