Apple launches Apple Store app in India

The Apple Store app provides customers with the most personalized way to shop for Apple’s innovative lineup of products and services. Learn more >

You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Number - Cell reference on another sheet based on a cell value

Hi all,

I need to pick up a value on another sheet (in the same spreadsheet) and port it on the main sheet.

I know the normal syntax i.e. 'Sheet 1' :: 'Table 1' :: A$1

My problem is that the sheet name (Sheet 1 in the example) to point to is based on a cell value on the main sheet (that came from a slider selector, but I suppose this will not affect the solution).

So I need to compose a formula or a particular syntax to compose the string that point to that precise sheet choose from that slider (table name is not a problem couse tables has the same name across all the sheets).

Is that possible in any way?

Thank yoy so much for helping me

Daniele

Posted on Sep 25, 2021 3:56 PM

Top-ranking reply

Barry

Level 9

59,957 points

Posted on Sep 25, 2021 10:52 PM

Hi Daniele,

INDIRECT is your friend here.

The upper table in the image below is the table on the main sheet, named Sheet 1.

The table contains the text "Home" in cell A1, Slicer cells with values 1, 2, 3 and 4 in cells B2, B3 and B4, and the formula shown below the table, entered in cell C2, and filled down to C4.

Before adding the sliders, text and formula to the table, three new sheets were added to the document, their default tables were removed, and copies of the (empty) table on Sheet 1 were pasted ontoSheets 2, 3 and 4. One piece of data, the sheet number of the sheet the table was on was entered as text in cell A1 of that table for use as the target cell of the formula.

After creating the tables, the sliders on the main table were set to 1, and that table was duplicated to generate the second table on Sheet 1.

Each of the sliders was set to a different value to demonstrate the results of that action.

The tables:

Details of the INDIRECT function can be found in the Function Browser, which opens when you select cell in a Numbers Table and type = to indicate you are entering a formula.

Regards,

Barry

View in context

Number - Cell reference on another sheet based on a cell value

Similar questions