MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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