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

 

[biglb79]

it's calculating a percentage change from the current month to the current month last year. the current month last year will always be in column P when I insert a new column by copying column D and inserting copied cells (I had the incorrect column labels up above but just went with it). However the column that calculates the current month to current month last year will move from FV to FW then to FX then FY, etc... so if I updated the correct columns below it looks like this. I need column P to always stay column P, but column FV to shift to the right when I insert a column. I hope that makes sense

=IF(INDIRECT("P8")<>0,(IF(INDIRECT("P8")=0,100%,INDIRECT("FV8")/ABS(INDIRECT("P8")))),0)

 

[Joe4]

but column FV to shift to the right when I insert a column.

Then don't use INDIRECT on this part, and it should automatically shift when you insert your column, i.e.

[COLOR=#333333]=IF(INDIRECT("P8")<>0,(IF(INDIRECT("P8")=0,100%,FV8/ABS(INDIRECT("P8")))),0)[/COLOR]

 

[biglb79]

that's what I thought but then everything calculates as 0.00% when I do that

 

[biglb79]

Then don't use INDIRECT on this part, and it should automatically shift when you insert your column, i.e.

[COLOR=#333333]=IF(INDIRECT("P8")<>0,(IF(INDIRECT("P8")=0,100%,FV8/ABS(INDIRECT("P8")))),0)[/COLOR]

perhaps it was something wrong with our linking here because now it's calculating a percentage

 

[biglb79]

perhaps it was something wrong with our linking here because now it's calculating a percentage

or it was user error as the indirect P8 remained on P8 when I copied/pasted