Absolute or relative cell reference in source doc

Correct, a simple cell reference does not exist to do what you want. It is just like Excel in this aspect. Believe me, there used to be tons of complaints on this forum when the cell references adjusted the way you are asking. Neither way is a panacea. But won't a lookup formula better suit your needs anyway? You type in the product and the price per ounce automatically appears. And it is sort-safe.

Another formula that will work in the example shown in the screenshot is

=IF(A<>"",VLOOKUP(A,Inventory::A:B,2,0),"")

Hi Ron,

My cheat sheet for INDEX and MATCH:

=INDEX(Column I want a return value from,

MATCH(My Lookup Value,

Column I want to Lookup against,

Enter “0″ ))

- See more at: http://www.randomwok.com/excel/how-to-use-index-match/#sthash.L2WFkFjw.dpuf

Written for Excel, but it works in Numbers

Regards,

Ian.

You could use INDIRECT with the Header Names as Labels.

First set Numbers Preferences  > Cell References > Use Header Names as Labels

Then construct the tables with Headers & Footers.

Using the tables proposed by Badunit, you could use the INDIRECT function like this:

INDIRECT("Inventory::"&C$1&" "&$A4)

C$1 refers to the header in column C (Dollar per Ounce)

$A4 refers to the header in the corresponding row (“Cheese”)

These references should be “SORT” proof (when you SORT the Inventory table by column A).

Warning: use text characters as header labels (no operators +, -, /, *, or $).

Paul.

" I want the formula to look back to the source document and, if the "cost per ounce" (column D) of "butter" (column A) which was on line 204 originally, and the "butter" line is now on line 105, I want the formula to follow to the new line of the source document."

Let's compress that 'want' statement to a simple statement of the end goal:

"I want the formula to look to the source document, find "cost per ounce"of "butter" , and return it to this cell "

What Badunit is suggesting is that you can find the cost per ounce of Butter on whatever 'line' (actually 'row') it is on using the inventory list as a Lookup Table, then using MATCH to find "butter" in column A and INDEX to return the price per ounce number from that row of column D.

IF MATCH is told what to look for ( the text "butter") and where to look for it (Column A of the source table) it will find that value ("butter") and report it's position in Column A to INDEX.

INDEX then follows its instructions: bring back the value in the cell at 'this position' in column D of the source table, returns the price per ounce of butter to the cell containing the formula.

If 'butter' (and its price per ounce) have been moved to a different row of the source table when it's price is again needed, MATCH does the same job it did before, and returns the new position. Index looks in the new position and brings back the price from that row.

Regards,

Barry

Perhaps I am being dense or misunderstood the original question (won't be the first time, that's for sure). Is there a simple cell reference formula that will follow cells of other rows around when sorted? A relative reference (as in =B2) keeps the relative position (such as continuing to point to a cell 5 rows up from the row with the formula), an absolute reference (as in =B$2) that will continue to point to that exact same cell after a sort, and there are INDIRECT and OFFSET formulas, but I cannot think of a reference or other formula that will follow a row around in a sort so if B2 gets sorted to row 23 the reference will point to B23. Numbers used to sort like that way back when and we were often posting formulas to make it NOT do that.

you should use formulas that are sort-safe. Can you share some of the details of the table with the data. Also include the view you want to see

Way back a lot of versions ago, Numbers used to work like you are wanting it to do. But Excel doesn't do it that way so, in the name of compatibility, Numbers changed to be like Excel. It made a lot of other things easier but made what you want to do harder. You can accomplish what you wany but not with a simple cell reference. I suggest a lookup formula. It might be easier for you in the end anyway vs having to search for your inventory item and making a reference to it, with no guarantee or check that you referenced the correct cell. A lookup formula will find the cost for you automatically and it does not matter how the inventory table is sorted

Here is a short example

The inventory table is all typed in data, no formulas.

The recipe table:

• Column A is typed in. Each item MUST be exactly as it appears in the inventory list or it will not be found and you will get an error triangle. Capitalization does not matter but spelling, punctuation, etc. do.
• Column B is typed in
• Column C formula (all cells in column C except header and footer rows) =IF(A<>"",INDEX(Inventory::B, MATCH(A,Inventory::A,0)),"")
• Column D formula =IF(A<>"",B*C,"")

The heart of column C is an INDEX(MATCH()) formula (i.e., a lookup formula). The MATCH part of it says "find a match in Inventory column A for what is in column A of this recipe table and tell me what row it is in, and I only want an EXACT match". The INDEX part says "give me the results from Inventory column B at that row."

The IF() formula that is wrapped around the formulas in C and D is there to take care of empty rows, where there is no data in column A to be looked up or multiplied. It eliminates error triangles.

To be perfectly correct, MATCH doesn't give "the row number" it gives you a number relative to the top of the specified range. Because I used the entire column as my range, it is the same as the row number. If I had used the range A2:A20, it would be relative to row 2.