Hlookup function

[Soupy]

I am currently creating a page that has a column of calculations in it.
The calculations are amounts of Future options. at the bottom of the
column I calculate an average 6 month total by simply suming 6 months
into the future. The question I have is how do I automate the Total sum
number at the bottom, because every time I update my worksheet I have to
redo all my calculations because the previous months value is now 0
because there is no future value anymore, so I have to rename my range
and do my calculation over again. I originally thought of the Hlookup
function but I can't get it to work for some reason, Can anybody help
me?

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[firefytr]

HLOOKUP and VLOOKUP syntax is identical, just looking in opposit
directions. the syntax is as follows:

HLOOKUP(-cell reference to lookup- *,* -table range to look through
*,* -row number to bring back if found- *,* -[type-set true/false]- )

type-set can be changed to 1/0 also. false or 0 will find an exac
match, 1 an approximate. the help files explain these functions prett
decent. ht

 

[davisro]

I have a solution below, but if anyone has a more elegant solution
please let me know.

If I read your question correctly, you have a matrix of columns, on
for each month of the year, and you only want to sum the columns tha
are for the current month and forward. Is this correct?

This requires Excel to recognize the current date, and then choose as
dynamic range only the columns for the current month and future months
and then perform an aggregate function (Sum, Average, etc) on tha
range.

If this is the case, read the discussion a
http://www.beyondtechnology.com/geeks021.shtml
on creating Dynamic Ranges using the offset formula. The offset formul
has the following arguments: Starting point, row offset, column offset
height of range, width of range.

The column offset argument (which establishes the upper left corner o
the range you are creating) requires you to use an expression to find
the current month in the header row (assume it is row 1). So, if toda
is May 18. the expression =match(today(),1:1,1) will search your heade
row of months for the cell with the date that is the nearest matc
before today, in this case 5/1/04. You might have it formatted t
display "May 04", but the formula actually looks for the date/inde
number, so how you format your date is not a factor.

Then you can sum your range name with =sum(your range name). Does tha
get you there