indirect function

[biglb79]

how would I get this formula to work using the indirect function on cell S8? I want that cell to stay the same when I insert a new column


=+IF($S8<>0,(IF($S8=0,100%,$GA8/ABS($S8))),0)

Thanks

 

[Joe4]

Do you mean something like this:

=IF(INDIRECT("S8")<>0,(IF(INDIRECT("S8")=0,100%,INDIRECT("GA8")/ABS(INDIRECT("S8")))),0)

 

[arthurbr]

If I understand correctly, no INDIRECT needed, just use absolute references
=IF($S$8<>0,0,IF($S$8=0,100%,$GA8/ABS($S$8)))

 

[Joe4]

If I understand correctly, no INDIRECT needed, just use absolute references
=IF($S$8<>0,0,IF($S$8=0,100%,$GA8/ABS($S$8)))

No, that doesn't work.

Try placing that formula anywhere on your sheet, and then insert a blank column anywhere before column S. You will see all those column references change to T, regardless of the absolute value reference.

 

[biglb79]

Do you mean something like this:

=IF(INDIRECT("S8")<>0,(IF(INDIRECT("S8")=0,100%,INDIRECT("GA8")/ABS(INDIRECT("S8")))),0)

yeah that's exactly what I wanted, thanks! one quick question, is there anything I can do to it if I wanted to copy and paste the formula down to row 49 to change the row number instead of just being locked on S8?

 

[Joe4]

Possibly, if there is any direct relation between the row number used in the formula and the row the formulas are being placed in.
For example, if you are always placing the formula in the same row that the formula is referencing, we can use the ROW() function, i.e.

=IF(INDIRECT("S" & ROW())<>0,(IF(INDIRECT("S" & ROW())=0,100%,INDIRECT("GA" & ROW())/ABS(INDIRECT("S" & ROW())))),0)

 

[biglb79]

perfect thanks!

 

[Joe4]

You are welcome.

 

[biglb79]

Do you mean something like this:

=IF(INDIRECT("S8")<>0,(IF(INDIRECT("S8")=0,100%,INDIRECT("GA8")/ABS(INDIRECT("S8")))),0)

I made one minor mistake on this. I need column GA to change each month I insert a column. I tried just removing the indirect but that doesn't work. thoughts?

 

[Joe4]

I am not sure what you are asking. What is the logic for determining which column should be used?