indirect function

biglb79

Active Member
Joined
Oct 17, 2007
Messages
299
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Do you mean something like this:
Code:
=IF(INDIRECT("S8")<>0,(IF(INDIRECT("S8")=0,100%,INDIRECT("GA8")/ABS(INDIRECT("S8")))),0)
 
Last edited:
Upvote 0
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)))
 
Upvote 0
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.
 
Last edited:
Upvote 0
Do you mean something like this:
Code:
=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?
 
Upvote 0
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.
Code:
=IF(INDIRECT("S" & ROW())<>0,(IF(INDIRECT("S" & ROW())=0,100%,INDIRECT("GA" & ROW())/ABS(INDIRECT("S" & ROW())))),0)
 
Upvote 0
Do you mean something like this:
Code:
=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?
 
Upvote 0
I am not sure what you are asking. What is the logic for determining which column should be used?
 
Upvote 0

Forum statistics

Threads
1,217,361
Messages
6,136,103
Members
449,991
Latest member
IslandofBDA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top