cell reference problem after sorting

Hi YLWT,

I had some difficulty reading your screen shots, as they included the entire screen, and were reduced in size and resolution when inserted here.

To take a shot of PART of the screen:

• Place the mouse pointer at the top left corner of the area you want to include.
• Press shift-command-4 (the pointe will change to a cross hair)
• Press the mouse button and drag a selection rectangle to enclose the area you want to include.
• Release the button.
• The screen shot will be saved to a .png file on your desktop, with the name "screen shot date at time.png"

As can be seen below, the result, dragged here or inserted using the camera icon above the composition window, is much easier to read.

I've made partial copies of one of the meal tables and the inventory table, including only the data (made up in the case of the unit price) needed for the question here. Table 1 is the meal table, Table 2 is the inventory table. The formula used is shown in the editor, below the first table.

The first part of the formula is a switch that suppresses the calculation on that line if no entry has been made in column C (quantity). When an entry has been made, IF passes control to the part starting C x...

VLOOKUP gets the search-for value in column A of Table 1, searches for it in the leftmost column of the lookup table (columns A to J of Table 2), and returns the value the same row of column 10 as it finds the search-for value. The returned value is multiplied by the value in column C (or Table 1), and the result placed in the cells containing the formula.

As the formula does not rely on referencing a specific cell, it will survive a sort of the inventory table that places and ingredient (along with its unit price) into a different row of that table. Example below:

Copy and paste version of the formula. Note that you will need to change "Table 2" in "Table 2::A:J" to match the name of your inventory table.

IF(LEN(C)<1,"",C×VLOOKUP(A,Table 2::A:J,10,FALSE))

Enter into the first cell on your meal table where a cost is to be calculated, then fill down to the last cell before the footer row.

Regards,

Barry

I am having a similar issue. Is there no way to use "=" to lock in what is in that cell before and after sorting? I have a spreadsheet I have made for baseball stats and need to reduce my formulas. As an example I have been using vlookup to return the score(column d)based on column c info into balt team page and their opponent sheet for that day. When i use "=" it works fine as well until I sort column a by date. Then all of the MLB teams that played on 3/31 show up above Balt 4/2 and throws everything off.

Thanks for the feedback and the green checkmark, Yalin.

Happy to hear it's working for you.

Regards,

Barry

"I am having a similar issue. Is there no way to use "=" to lock in what is in that cell before and after sorting?"

Hi aswad,

A direct cell reference, if I read it correctly, was what YLWT was using, and what was 'losing contact' with the correct cell when the source table was sorted. Although you haven't provided much detail on your situation, I suspect the solution is the same as the one above, assuming the situations are directly comparable.

"When i use "=" it works fine as well until I sort column a by date. Then all of the MLB teams that played on 3/31 show up above Balt 4/2 and throws everything off."

Every formula begins with "=" (even though Numbers 3 displays the fx symbol in the editor in place of the = sign). What do you mean by "When i use "=""?

If you sort the table by the contents of column A, ascending, and the values in column A are Date & Time values displaying only the month and day of the date part, then it will sort all the dates in March before the first date in April (provided all dates are in the same year), and will pay no attention whatever to information other than what's in column A when performing the sort.

With a difference setup of the date and team information,a sort on column C would likely work.

There's enough different about this question that it should be the seed of a new thread, rather than a hijack of this one. Please clarify, and ask it as a new question.

Regards,

Barry