Ask Your Question
0

reference sheet name from cell value

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

Elso gravatar image

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

edit retag flag offensive close merge delete

Comments

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 +0200 )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 +0200 )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 +0200 )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 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

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

Lupp gravatar image

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

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)
edit flag offensive delete link more
0

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

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

Comments

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 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 3,289 times

Last updated: Sep 12 '16