The vlookup function works perfectly with the indirect function to accomplish a variable sheet name lookup in Excel. No matter what I do, this identical function does not work in Numbers when using my iPad or my MacBook. I have a spreadsheet in Numbers with multiple sheets, all with different names.
I need to use a formula to find a sheet a name and then perform the vlookup required and return the desired results.
iPad Pro, iOS 12
An example of VLOOKUP using INDIRECT to find a value in column A of Sheet 2::Table 1 and return the corresponding value from column C of that table.
B2 contains the text "Sheet 2" (without the quotes)
C2 contains the text "Table 1" (without the quotes)
D2 contains the value to look up
E2=VLOOKUP(D2,INDIRECT(B2&"::"&C2&"::A:C"),3,0)
On an unrelated topic, is anyone else having problems entering text in the edit window of the forum? I am often unable to select text or place the cursor with the mouse. I can select some places but not others, with no apparent pattern or reason. Sometimes I have to click a little above the line of text for it to work. Once I am able to get the cursor to show up, I can move it around with the arrow keys and select text with the mouse but it is making me place the cursor nearby where I really want it then cursor over to the right spot. It makes it very difficult to write a response.
Table names are more important than Sheet names in Numbers.
Date is in Cells. Cells are in Tables. Tables are on Sheets, which in Numbers are large sheets of 'canvas' on which may be placed several kinds of objects, including Tables.
If your Tables have names which are distinct within the document, Numbers does not need the Sheet name as part of a cell address, and will drop it if you include it in a cell reference.
If your reference is to the same cell on several tables with the same name, but on different sheets, then you will need to include the Sheet name, Table name and cell or range address in any formula referencing one or more cells on one of those tables.
More precise responses rquire additional detail.
Regards,
Barry
So, translating to Numbers vocabulary:
You have a Document which contains several Tables:
The Database table should be on a sheet of its own, and will need to be visited only when you need to edit the list of ingredients and their per unit nutritional values.
The Daily Summary table should also be on a sheet of its own, possibly shared with one or more charts displaying the summary data graphically.
The Recipe tables, depending on their number, can share one or more sheets. If more than one, you may want to place them by category—type of food, or which meal it would be eaten at, or by some other classification system.
In daily use, the only sheet that needs to be visible is the one containing the Daily Summary.
With this c=general structure, you would be using VLOOKUP for each retrieval, the multiplication operator to convert the per unit values to 'value for this recipe', and an IF statement to prevent calculations that would throw an error due to having no data to work with.
Regards,
Barry
Hi NvsE,
On March 17, 2019, SGIII wrote, is response to your initial post:
"If you don't share what the "identical function" is and what your data and document look like and what you are trying to do, we'll just be engaging in a mind reading exercise. There are some good mind readers here but perhaps not that good!"
On April 2, 2019, you wrote:
"I’ve written out the highlights of how my tables are set."
Two aphorisms come to mind:
The devil is in the details.
A Picture is worth a thousand words.
Screenshots of each type of table, showing the row and column headers, and enough rows and columns to determine the structure of the table would be useful.
Regards,
Barry
Thank you for your quick response and sorry for the delay on my end...it’s been one of those weeks. Your explanation was very helpful in understanding the difference between Excel and Numbers. Please let me know if the information below is specific enough to help solve my problem.
I have a workbook with 3 types of worksheets. One is a database, the other is a recipe worksheet, and the third would be a daily result. There are multiple recipe worksheets and they’re all formatted the same.
Each recipe worksheet pulls information from the database worksheet to give a final nutritional result. The daily worksheet needs to reference a cell where the recipe name is listed, find the corresponding worksheet, and return the nutritional results of that recipe. The vlookup, indirect formula was a way to accomplish the variable sheet name formula. I’m looking to track a full day’s intake by doing so.
Hi Barry,
Thank you again for your help. I’m not sure if I’m misunderstanding or if my data is not set up in the most efficient way. I’ve written out the highlights of how my tables are set. Please let me know if the vlookup function by itself is still the answer for the daily value spreadsheet.
The nutrient database has the nutrients (i.e. protein, fat, etc.) in the first column and all ingredients are listed on the top row with the serving sizes in the second row. The value of each ingredient’s nutrient is listed in the intersecting cell.
Example - Calcium is listed in cell A3. Strawberries is listed in cell B1. The serving size is listed in cell B2. The value of calcium for the specified serving of strawberries is listed at their intersection in cell B3.
The recipes are each on their own worksheet but are formatted the same. The first column has the ingredient which uses a formula to pull the serving size from the nutrient database (2nd column). From there I have a third column where the amount used in the recipe is entered. On the far right is a second table where the total nutrients are listed for both the amount of each ingredient used and then a sum of all nutrients included. This table uses a different formula to calculate the nutrients derived from that amount of ingredient used.
Example - ingredient 1 is strawberries. Nutrients list begins in cell G2. Strawberries is listed in cell H1. The nutrients for the amount of strawberries used is listed from cell H2 down to the last row of the list of nutrients. This continues for multiple columns to account for all ingredients and then everything is summed up in the final column.
What I want is a daily value worksheet where I can have a column of nutrients listed and a row of recipes. With my current pattern, I was planning something similar to having the nutrients start in cell A2 and the recipe names would be listed in row 1 starting with column B. When I select a recipe in row 1, I would like a formula to automatically fill the nutrient sums from the corresponding recipe worksheet.
I would consider myself very familiar with vlookup functions, but with the manner in which my data is currently organized, I can’t inderstand how to use that function to achieve my goal. Sorry if I’m just confused!
Also, is there a way to concatenate text in a cell with the table format like you would in a variable worksheet Excel formula? I don’t seem to be able to accomplish this if so.
Aside to **:
"On an unrelated topic, is anyone else having problems entering text in the edit window of the forum? I am often unable to select text or place the cursor with the mouse. I can select some places but not others, with no apparent pattern or reason. Sometimes I have to click a little above the line of text for it to work. Once I am able to get the cursor to show up, I can move it around with the arrow keys and select text with the mouse but it is making me place the cursor nearby where I really want it then cursor over to the right spot. It makes it very difficult to write a response. "
It appears to be an 'undocumented feature' of the most recent update to the ASC software.
The 'dead zone is near the bottom of the composition window. Current workaround is to add a few (four or more) returns after your initial text, then remember to scroll the message to keep its end in the top 2/3 of the work space.
Barry
Hi NvsE,
There was a nagging sense of deja vu on reading this thread. Take a look at this similar question:
Numbers - Nutrition Input Help - Apple Community
It doesn't cover the concatenation question, but up to there it seems to be on topic.
Regards,
Barry
Thanks, I will give that a shot.
If you don't share what the "identical function" is and what your data and document look like and what you are trying to do, we'll just be engaging in a mind reading exercise. There are some good mind readers here but perhaps not that good!😀
SG
Variable Sheet Name Formula for Numbers
