Formula to prevent auto changes when Columns Added

[tbakbradley]

Good afternoon all. I wasn't expecting this issue, but now that it happens, I should have expected it.

I have a worksheet "FIM Data" with hardcoded data in columns A through L. This is sent to another Employee who performs a System run and Copies/Pastes their Data into Columns M through Z.

Once that is sent back to me, I click a Macro button that I created that Adds THREE new columns to the left of column M and then I force the Heading Names for the NEW columns M, N and O. That all works fine. I have to send this to another employee who runs this through another system to provide extra data and pastes it into the new M-O Columns.

On another worksheet, that performs many different calculations, Prior to the need for a Macro to add Columns M-O on the "FIM Data" Sheet, it was this:

=IFERROR(IF('FIM Data'!S2="","",'FIM Data'!S2),"")

Because I'm now going to be inserting Columns M-O, I changed to this:

=IFERROR(IF('FIM Data'!S2="","",IF('FIM Data'!M2="Yes", 200, 'FIM Data'!S2)),"")

However, what happens is that because I insert the Rows, the Column references change in the formula above. I need it to Automatically change for S2, but NOT M2. I always need this formula to point to M (I copy it down to rows below, so could be M2, M3, M4....). I have no idea how to LOCK the M and allow the S to remain relative to where that data is now located after the column adds.

Any ideas?

 

[Fluff]

Put a $ sign in front of the M like
FIM Data'!$M2="Yes",

 

[mikerickson]

If you use absolute addressing (the $) that will adjust to Insert and Delete. INDIRECT("M2") will always return the value of M2 and not change in response to Insert or Delete.

 

[tbakbradley]

If you use absolute addressing (the $) that will adjust to Insert and Delete. INDIRECT("M2") will always return the value of M2 and not change in response to Insert or Delete.

Thanks. I knew that $ would not work after I tried it. I had read about the Indirect, but some of the info on it was confusing to me. If it's just a matter of INDIRECT("M2"), then that may work out. I'm not sure how that works when pointing to a WS name. I'll mess around with it and see what happens.

 

[tbakbradley]

If you use absolute addressing (the $) that will adjust to Insert and Delete. INDIRECT("M2") will always return the value of M2 and not change in response to Insert or Delete.

The Indirect method is working. However, I have a need to copy this Formula down to multiple Rows. When I do, the Non Indirect portions Auto Change to S3, S4, S5, etc. However, the INDIRECT M2 remains the same. Is there a way to alter this a bit so that as I copy/paste the formula to various other cells below this one, it will auto change?

=IFERROR(IF('FIM Data'!V2="","",IF(INDIRECT("'FIM Data'!$M2") ="Yes", 200, 'FIM Data'!V2)),"")

 

[tbakbradley]

The Indirect method is working. However, I have a need to copy this Formula down to multiple Rows. When I do, the Non Indirect portions Auto Change to S3, S4, S5, etc. However, the INDIRECT M2 remains the same. Is there a way to alter this a bit so that as I copy/paste the formula to various other cells below this one, it will auto change?

=IFERROR(IF('FIM Data'!V2="","",IF(INDIRECT("'FIM Data'!$M2") ="Yes", 200, 'FIM Data'!V2)),"")

Let me rephrase...the Non Indirect (V3, V4, V5, etc. not S3, S4, S5.) change as normal, but the Indirect M2 stays as M2 even when copying/pasting to down rows.

 

[mikerickson]

Something like =INDRECT("M"&ROW(A2)) perhaps.
The Row(A2) will adjust to A3, A4, etc as it is dragged down, or if rows are inserted above.

But the column "M" will remain constant, even when columns are inserted/deleted.

To protect against column A being deleted, the A2 should actually refer to the column in which the formula resides.

 

[tbakbradley]

Something like =INDRECT("M"&ROW(A2)) perhaps.
The Row(A2) will adjust to A3, A4, etc as it is dragged down, or if rows are inserted above.

But the column "M" will remain constant, even when columns are inserted/deleted.

To protect against column A being deleted, the A2 should actually refer to the column in which the formula resides.

Mike.....this worked perfectly. Thank you so much!!!