Using hlookup to return a cell Address

  • Thread starter Thread starter bvasek1082
  • Start date Start date
B

bvasek1082

Hello all!

I believe my problem is similar to that on thread:

http://www.excelforum.com/showthread.php?threadid=223489&highlight=return+cell+address+function

But I wasn't able to make sense of all the examples.

I have a table of data with dates at the top and numerical values belo
the dates. I am trying to make a simple sum() function for year t
date. I need to be able to return the cell address of the curren
month's data.

[image
http://community.webshots.com/s/image9/2/44/83/151224483otIleS_ph.jpg


I want my sum() function to look like sum(A3:D3) to capture data fro
Jan 1st up to the current month (actually the prior month, April). Th
term D3 in the sum function must come from a function that finds th
current month minus 1(April) then uses hlookup() or a similar fuctio
to match the months and return "D3", the cell containing the data fo
Apr-04. I am trying to create a function with either =address() o
=cell(). ex. =address(hlookup(month(today()).......

In summary....I need to find the current month less one, lookup tha
month in a row, find the cell 2 below, and return that cell's address.
Thanks for any help.

Brya
 
Hi
why so complicated?. If row stores real date values (just formated as
MM-YYYY) try the following formula
=SUMPRODUCT(--(A1:X1<DATE(YEAR(TODAY()),MONTH(TODAY()),1)),A3:X3)
 
Thanks. That worked great. Now how could that be applied to a rollin
6-month and 12-month calculation
 
Hi
maybe
=SUMPRODUCT(--(A1:X1<DATE(YEAR(TODAY()),MONTH(TODAY()),1)),--(A1:X1>=DA
TE(YEAR(TODAY()),MONTH(TODAY())-6,1)),A3:X3)
 
Back
Top