Referencing Cell Next To Today's Date Cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Docktondad - 16 May 2007 21:25 GMT

Please bear with the question: Using Excel 2002 w/SP/3 and Windows XP w/SP2.
I have two columns: 1) A2...A137 are daily dates. 2) B2...B137 are cells
that are filled in (one cell) each day and contain my weight. In Cell J37 I
want to have the data in the cell to the right of the current day displayed
automatically as each day changes. How do I reference the data in Column B
to do this? Thanks,

Docktondad

Reply to this Message

PCLIVE - 16 May 2007 21:33 GMT

One way:

=INDIRECT("B" & MATCH(TODAY(),A:A))

> Please bear with the question: Using Excel 2002 w/SP/3 and Windows XP
> w/SP2.
[quoted text clipped - 8 lines]
>
> Docktondad

Reply to this Message

Marvin P. Winterbottom - 16 May 2007 21:34 GMT

not sure what you mean by "as each day changes" Are you entering a date in a
cell and want to display the weight from that date? If so, that is a vlookup
command.

> Please bear with the question: Using Excel 2002 w/SP/3 and Windows XP w/SP2.
> I have two columns: 1) A2...A137 are daily dates. 2) B2...B137 are cells
[quoted text clipped - 4 lines]
>
> Docktondad

Reply to this Message

T. Valko - 16 May 2007 21:46 GMT

If todays date is the last date entered and your dates are in sequential
ascending order:

=LOOKUP(TODAY(),A2:A137,B2:B137)

If there might be dates beyond todays date:

=INDEX(B2:B137,MATCH(TODAY(),A2:A137,0))

Biff

> Please bear with the question: Using Excel 2002 w/SP/3 and Windows XP
> w/SP2.
[quoted text clipped - 8 lines]
>
> Docktondad

Reply to this Message

Jeff - 16 May 2007 22:15 GMT

you should be able to do that nesting a couple of functions
Offset & Match

assuming Dates are in column A and Weight in Column B this formula will work.

=OFFSET(A2:A35,(MATCH(E2,A2:A35,0)-1),1,1)

Where A2:A35 is the date range
Where E2 is the Date you are interested in retreving weight data for.

you could clean it up a little using named ranges,
=OFFSET(Date_Range,(MATCH(Date,Date_Range,0)-1),1,1)

Where Date_Range is the date range
Where Date is the Date you are interested in retreving weight data for.

next step would be to use variable named ranges but that is a different
conversation.

hope this helps

Signature

Jeff

Reply to this Message

Docktondad - 16 May 2007 22:25 GMT

Thanks to all for the suggestions. PCLIVE had the answer I was looking for.
He read my mind perfectly.

Docktondad

Reply to this Message