Challenge! HLOOKUP??

msimone

New Member
Joined
Sep 30, 2006
Messages
17
I posted this once before and didn't get a resolution so I'm posting again.

I've built a calendar in excel to track transactions per day. At the top of each calendar square is the date (1 - 31 obviously) and underneath I list transactions (+/-). At the bottom of each calendar square is the sum of those transactions, pitifully simple I know. I'd like to have a cell that displays the subtotal for the current day when I open the spreadsheet. I've been using this:

=HLOOKUP(J88,A75:U83,9,FALSE)

IN J88 I've put =DAY(TODAY()) and A75:U83 are the coordinates of my "calendar square" and "9" equals the number of rows down where my subtotal box is. This solution works fine if row 75 is the top row but if I wanted to expand it it doesn't work. I would imagine there's a simpiler way to have a formula search a worksheet for a specific number (date) which is today's date / number and then return the information from a certain number of rows down. I hope this makes sense! Please help!



-msimone
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,362
Can you provide a sample of the data, along with actual/expected results?
 

msimone

New Member
Joined
Sep 30, 2006
Messages
17
I've taken a jpeg snapshot of my spreadsheet...how do I post it to you?[/url]
 

msimone

New Member
Joined
Sep 30, 2006
Messages
17
Essentially I'm wondering there is an excel formula (or comination of formulas) that can search an entire worksheet for a value and then return a related value from a specified position based on the found value?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,362

ADVERTISEMENT

Try posting a small text sample of the data, or use Colo's HTML Maker to post a snapshot of the data.
 

msimone

New Member
Joined
Sep 30, 2006
Messages
17
a1: 1 (for October 1st)
a2: -$20
a3: +$70
a4: $50 (Subtotal for Oct 1st)
a5: $1000 (current balance)

d1: 2 (for October 2nd)
d2: $100
d3: -$90
d4: $10 (Subtotal for Oct 2nd)
d5: $1010 (current balance)

This repeats in the classic calendar format: 7 cell boxes across and 4 rows downward. The problem I'm having is that if I use HLOOKUP it only it requires that the value I'm looking for be the TOP row in the formula. If I want to have the formula search not only the top row but the 4 calendar box rows underneath I need to edit my formula to such an extent that it becomes useless. Basically I want to have a summary of my current balance on a seperate worksheet depending on the current day.

Does this make sense?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,362
If I understand you correctly, A6:A10 would contain the data for October 8, A11:A15 would contain the data for October 15, and so on. In other words, the dates are contained in Rows 1, 6, 11, 16, 21, and the corresponding amounts in Rows 5, 10, 15, 20, and 25. If this is correct try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=INDEX(A1:S25,MIN(IF(MOD(ROW(A1:S25)-ROW(A1),5)=0,IF(A1:S25=U1,ROW(A1:S25)-ROW(A1)+1)))+4,MATCH(U1,INDEX(A1:S25,MIN(IF(MOD(ROW(A1:S25)-ROW(A1),5)=0,IF(A1:S25=U1,ROW(A1:S25)-ROW(A1)+1))),0),0))

...where U1 contains the current date.

Hope this helps!
 

msimone

New Member
Joined
Sep 30, 2006
Messages
17
This is amazing! It totally works. I'd love to decipher how this formula works? Would it be asking too much to get a deconstruction of how this magic works?!? If it's too difficult I understand and thank you for your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,111,910
Messages
5,541,541
Members
410,547
Latest member
htran4
Top