Cell references in a table

[fsudawg85]

I have a table that can be sorted with formulas that reference rows above/below a formula. As I sort the table, using absolute references or not, I cannot get the formulas to stay with the correct row as it is sorted. I also have formulas in the row that refer to other columns of that row so the answer is not as easy as switching my rows and columns.

Example showing the formulas I'm using:


Currently B3 will result "40". If I want to sort the table by the sample (column A) alphabetically, the formula currently in B3 will move to B4. Whether I use absolute ref's or not ($:$), my formula continues to display the same "=B2-B4" and this will now give me a circular reference because the formula is in B4.

Desired Result: After sorting, make the formula now located in B4 still refer to the correct sample/row as it moves. It should now say =B2-B3 to still give me "40".

(Columns C&D only shows my need of formulas inside of a row also)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Sample[/TD]
[TD]Gal/Min[/TD]
[TD]Lb/Gal[/TD]
[TD]Lb/Min[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Tank A[/TD]
[TD]50[/TD]
[TD]10[/TD]
[TD]=B2*C2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Tank ZZZZZZ[/TD]
[TD]=B2-B4[/TD]
[TD]10[/TD]
[TD]=B3*C3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Tank B[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]=B4*C4[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Maybe...

Instead of =B2-B4 in B3 try this formula
=INDEX(B:B,MATCH("Tank A",A:A,0))-INDEX(B:B,MATCH("Tank B",A:A,0))

Hope this helps

M.

 

[JoeMo]

If you sort A-Z on col A, that formula (now in cell B4) should become =B3-B5 which does not create a circular reference. But, it doesn't return the value you want. One way to avoid this is to name the cells B2 (say, Top) and B4 (say, Bottom) before you sort. Change the formula in B3 (prior to sorting) to: =Top-Bottom. This will remain intact after the sort and return the value you want.

 

[fsudawg85]

Yes, it should become B3-B5 but it doesn't and this is the issue that I'm trying to understand. Please excuse the short example but there's a few hundred rows so naming the cells would be more work than its worth.

 

[fsudawg85]

Perfect! Except... here's the next layer of the onion... Technically my actual spreadsheet has a date column first and my hope for a simple answer didn't come true. So column A is actually a date, and B is the sample. What complicates this is that I take all of these sample each day meaning I have the same date several times over, and then all of these samples repeat with each new date. And for the sake of a short example to you all, I didn't bother with saying that each row in my actual spreadsheet already has 12 columns of data and formulas.

So take line 2-4, and then repeat it for tomorrow, and then the day after (lines 5-7 tomorrow, lines 8-10 the day after) with the same line in a repeat fashion. So your answer is perfect to my original question and is a huge help but it is my fault that I didn't provide the entire picture. I was hoping that I was doing something simply wrong with the cell references. Assuming that wasn't the case, I was figuring INDEX would come in to play here, and I'm thinking it will now take another layer of INDEX. First to match the date, then to match the sample. ​

 

[fsudawg85]

Perfect! Except... here's the next layer of the onion... Technically my actual spreadsheet has a date column first and my hope for a simple answer didn't come true. So column A is actually a date, and B is the sample. What complicates this is that I take all of these sample each day meaning I have the same date several times over, and then all of these samples repeat with each new date. And for the sake of a short example to you all, I didn't bother with saying that each row in my actual spreadsheet already has 12 columns of data and formulas.

So take line 2-4, and then repeat it for tomorrow, and then the day after (lines 5-7 tomorrow, lines 8-10 the day after) with the same line in a repeat fashion. So your answer is perfect to my original question and is a huge help but it is my fault that I didn't provide the entire picture. I was hoping that I was doing something simply wrong with the cell references. Assuming that wasn't the case, I was figuring INDEX would come in to play here, and I'm thinking it will now take another layer of INDEX. First to match the date, then to match the sample. ​

Sorry, this was directed towards Marcelo Branco

 

[Marcelo Branco]

Perfect! Except... here's the next layer of the onion... Technically my actual spreadsheet has a date column first and my hope for a simple answer didn't come true. So column A is actually a date, and B is the sample. What complicates this is that I take all of these sample each day meaning I have the same date several times over, and then all of these samples repeat with each new date. And for the sake of a short example to you all, I didn't bother with saying that each row in my actual spreadsheet already has 12 columns of data and formulas.

So take line 2-4, and then repeat it for tomorrow, and then the day after (lines 5-7 tomorrow, lines 8-10 the day after) with the same line in a repeat fashion. So your answer is perfect to my original question and is a huge help but it is my fault that I didn't provide the entire picture. I was hoping that I was doing something simply wrong with the cell references. Assuming that wasn't the case, I was figuring INDEX would come in to play here, and I'm thinking it will now take another layer of INDEX. First to match the date, then to match the sample. ​

Could you provide an example - a small data sample and before/after sort alphabetically?

M.

 

[fsudawg85]

To anyone still following this:
After having a friend check out the issue on his computer, it sounds like the issue may be a setting problem. He didn't have the same problem that I did with references staying correctly placed with the line item I wanted them associated with. So if anyone has an idea of what setting may have changed, thanks ahead of time for the help.

P.S. We both used Windows 10 and Office 13 when troubleshooting.