Change Formula
Posted by AL on January 01, 2002 1:35 PM
: =IF($BZ$4=1,HLOOKUP($BZ$5,$AM$4:$BR$39,36,0),"")
I am using the above formula supplied by Scott and Cory, and I was wondering if there was any way that when I drag this across columns that the 1 in BZ4=1 could change to 2,3,ETC because I use it for January thru December and there are a lot of rows to fill and I use a lot of different boxes with this formula. Thanks for any help.
AL.
Posted by John on January 01, 2002 1:43 PM
You could use the column() function which returns the column of cell that contains the formula.
=IF($BZ$4=column(),HLOOKUP($BZ$5,$AM$4:$BR$39,36,0),"")
or if you start in Column B:
=IF($BZ$4=column()-1,HLOOKUP($BZ$5,$AM$4:$BR$39,36,0),"")
Posted by AL on January 01, 2002 2:08 PM
John, The formula starts in CA7 and goes to CL7,
the formula I showed you does work, except that I have to keep changing the 1 to 2 ETC in BZ4=1 everytime I move it to a different column.
When I tried your formula it would not work. any
suggestions?
AL
Posted by Paul Johnson on January 02, 2002 9:44 AM
If you have room in your spreadsheet - insert a row above your data. Put a 1 above jan, 2 above feb, etc.... Then in your formula change the "=1" to the cell reference above. That way when you copy across, the cell reference changes, as does the number.
Good Luck
Posted by AL on January 02, 2002 3:15 PM
Good Luck
:Paul, here is my problem, the formula you suggested is getting its information from a chart in AM4: BR59, which changes every month. So when this chart changes to February, everything in January disappears, and the new chart is reflected in February. How do I solve this problem?
AL
