Problem with equation linking to cells after the data has been moved

[grubarbarian]

Dear Gurus,

I have a problem with my equation linking to cells after the data has been moved.

I have my data lined up left to right in my sheet in row 3 and an equation that will move the values in a certain cell and push them out to the right in row 3 if it does not honor a set constraint.

I have a separate equation (see below) that links to the data in row 3.

=IF(Y3="","",IF(Y3<>"",Y3-G3)*-1)

The problem I am facing is that when the data in row 3 does not meet the constraint it is moved to the right until it does honor the constraint. For example the data in cell Y3 may be moved to cell AB3. So in this example even though cell Y3 now has no data (and therefore ="") the equation links to cell AB3 and gives a value using that data instead of linking to cell Y3 where it should return a "" value.

Any advice on how I can keep my equation linked to cell Y3 instead of linking it to cell AB3 after the data is moved would be greatly appreciated.

Many thanks,

Chris.

 

[inactiveuserps07]

Chris, first off, your second IF() is redundant and, therefore unnecessary, since you already tested for the condition in the first IF().

As for your problem, just use the $ to keep absolute the columns you don't want to adjust (i.e., $Y3 not Y3; same for G3 if you don't want that to adjust).

 

[grubarbarian]

Erik,

Many thanks for your quick reply. I guess I wasn't clear enough in my description. I have 2 tables of data, the first one containing the data in row 3 (as per original post above). The data in this table (multiple rows) are daily production numbers and correspond to associated dates. I have set up my sheet so that if the cumulative production data on a given day exceeds a certain max value then an equation moves a row of the data into the future (ie. to the right) to a point where the total of the rows does not exceed the maximum constraint. The cells where the data once was are now blank.

The problem i have is that I have linked the original equation posted above into a separate table and used it in a separate calculation. When the data in the first table is moved I would like the second table to record a null value, however what it does is maintain its link to the data that was moved into the future (ie. to the right in the sheet). Using $ has no effect in this case.

Thanks,

Chris.

 

[inactiveuserps07]

Hi, Chris. As ever, it's very hard for "us out here" to understand individual issues without seeing the same data you're looking at. It's clear to you, because you have the visual aid and, in fact, created the system. But it would help greatly if you could share that here (including sheet names, column letters and row numbers).

That said, another approach you could try is using an indirect reference, so ...

Y3

becomes

INDIRECT("Y3")

You can see if this holds as you intend.

 

[grubarbarian]

Erik,

The indirect("Y3") works amazingly.

Many thanks,

Chris.

 

[inactiveuserps07]

Glad it worked out, Chris. Thanks for closing the loop.