Excel Formula using COLUMN()

Posted by Bob Ernesto on December 17, 2001 10:14 AM

I am puzzled by the meaning of COLUMN() when used in:

=IF(CJ29="","",(2594/25740)*VLOOKUP(494200,E_Bldg_Usage_Table,COLUMN()-COLUMN($A49)+1,FALSE))

The result should be a column index number from the first column.

Posted by Mark W. on December 17, 2001 10:17 AM

It's the number of the column where this formula
resides. As the Help Topic for the COLUMN worksheet
function states, "...If COLUMN is entered in C5,
then: COLUMN() equals COLUMN(C5)...".

Posted by Aladin Akyurek on December 17, 2001 10:19 AM

Bob --

What range does E_Bldg_Usage_Table refers to? You can see it via Insert|Name|Define or by simply selecting this name from the Name Box.

And, In which cell do you have this VLOOOKUP formula?

Aladin

===========

Posted by Bob Ernesto on December 17, 2001 12:32 PM

All of this formula takes place in the same worksheet. CJ49 is the cell the formula is in. CJ29 is the first cell in the top row of the table. It is populated.
='Bldg. Usage (E)'!$A$27:$CI$98
Another clue is this spreadsheet is repetitive by month for many years. I just inherited it and this was the first month for which the formulas expired. There are litterally thousands of formulas in this group of spreadsheets and worksheets. The author retired four years ago.

Posted by Bob Ernesto on December 17, 2001 12:39 PM

Thank you Mark. I have a dozen help sheets printed out in front of me and that is one of them. I have no excuse.

Posted by Aladin Akyurek on December 17, 2001 2:06 PM

Bob,

By now, you know what COLUMN() with or without an argument means. It seems the value (2594/25740) by looking up 494200 in the table in column 88-49+1=40 which becomes 41 when the formula is copied to CK29. If I were you, I'd try to reorganize the thing a bit. I'd advice to put such constants as (2594/25740) and 494200 in cells of their own in a worksheet, named e.g., Admin (from Administrator) and give these cells sensible names and use these names in the formulas. Also, put the table in a worksheet names e.g., Tables if it is referred to in multiple worksheets in the same workbook.

Hope this helps.

Aladin

======== All of this formula takes place in the same worksheet. CJ49 is the cell the formula is in. CJ29 is the first cell in the top row of the table. It is populated.