Ask Your Question

reference sheet name from cell value [closed]

asked 2016-09-11 15:37:02 +0100

Elso gravatar image

updated 2020-08-31 19:29:19 +0100

Alex Kemp gravatar image


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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-31 19:29:45.726023


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 gravatar imageLupp ( 2016-09-11 18:53:03 +0100 )edit

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

Lupp gravatar imageLupp ( 2016-09-11 19:13:19 +0100 )edit

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 gravatar imageElso ( 2016-09-12 04:25:20 +0100 )edit

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.

Elso gravatar imageElso ( 2016-09-12 04:28:41 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2016-09-12 01:00:25 +0100

mark_t gravatar image

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.

edit flag offensive delete link more


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.

Elso gravatar imageElso ( 2016-09-12 04:10:47 +0100 )edit

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.

Yuri R. Sucupira gravatar imageYuri R. Sucupira ( 2019-11-15 23:18:27 +0100 )edit

answered 2016-09-11 19:08:50 +0100

Lupp gravatar image

updated 2016-09-11 19:10:41 +0100

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:


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:

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2016-09-11 15:37:02 +0100

Seen: 5,438 times

Last updated: Sep 12 '16