Find under date formula?

D

David

XL2K,

Is there a formula I can copy down ColB on sheet2 that will result in the
values in a column under the current date of sheet1?
 
B

Barb Reinhardt

Can you clarify your question? What do you mean by "under the current
date"? Do you mean less than the current date and if so, by how much?

What is your goal? There may be an easier way.
 
D

David

Barb Reinhardt wrote
Can you clarify your question? What do you mean by "under the current
date"? Do you mean less than the current date and if so, by how much?
Click to expand...

There are 4 columns with 4 dates as headers on Sheet1 with entries in the
cells beneath them. Not all those cells are populated on any given day. I
want the values underneath/below the current date to appear in ColB of
Sheet2.
 
G

Gary''s Student

We can use HLOOKUP(). Assuming that the dates are in the first row of the
columns in sheet1 going from A to O. In sheet 2, down the first column,
enter:

=HLOOKUP(TODAY(),Sheet1!A1:O7,1,TRUE)
=HLOOKUP(TODAY(),Sheet1!A1:O7,2,TRUE)
=HLOOKUP(TODAY(),Sheet1!A1:O7,3,TRUE)
....

This will find the correct column on sheet1 and list its contents.

Have a good day!
 
D

David

=?Utf-8?B?R2FyeScncyBTdHVkZW50?= wrote
We can use HLOOKUP(). Assuming that the dates are in the first row of
the columns in sheet1 going from A to O. In sheet 2, down the first
column, enter:

=HLOOKUP(TODAY(),Sheet1!A1:O7,1,TRUE)
=HLOOKUP(TODAY(),Sheet1!A1:O7,2,TRUE)
=HLOOKUP(TODAY(),Sheet1!A1:O7,3,TRUE)
Click to expand...

Nope. Close, but no cigar. Chalk it up to once more being a stupid poster
who doesn't know how to explain his wants clearly.

Let's get real specific:
Sheet1:
C4:F4
9/17/2005 9/18/2005 9/19/2005 9/20/2005
(For testing. They will change, of course.)

C5:C129 under 9/17/2005 will contain either a number or will be empty
Same for D5:D129 under 9/18/2005
Same for E5:E129 under 9/19/2005
Same for F5:F129 under 9/20/2005

Sheet2:
Need formula *only* in ColB starting in B2 and extending downward to B126
Results of formulas will be values in rows 5:129 from Sheet1 for current
day only. Unfortunately your offering, even with ranges adjusted, shows
values no matter what date on Sheet1 they're underneath. Would also love it
if there were no #NA's for blank cells in Sheet1's relevant range.

That's as clear as I can be without pasting entire workbook.
 
D

David

David wrote
=?Utf-8?B?R2FyeScncyBTdHVkZW50?= wrote


Nope. Close, but no cigar. Chalk it up to once more being a stupid
poster who doesn't know how to explain his wants clearly.

Let's get real specific:
Sheet1:
C4:F4
9/17/2005 9/18/2005 9/19/2005 9/20/2005
(For testing. They will change, of course.)

C5:C129 under 9/17/2005 will contain either a number or will be empty
Same for D5:D129 under 9/18/2005
Same for E5:E129 under 9/19/2005
Same for F5:F129 under 9/20/2005

Sheet2:
Need formula *only* in ColB starting in B2 and extending downward to
B126 Results of formulas will be values in rows 5:129 from Sheet1 for
current day only. Unfortunately your offering, even with ranges
adjusted, shows values no matter what date on Sheet1 they're
underneath. Would also love it if there were no #NA's for blank cells
in Sheet1's relevant range.

That's as clear as I can be without pasting entire workbook.
Click to expand...

I was able to accomplish what I wanted with this VBA one-liner:
Sub CopyToday()
Sheets(2).Range("B2:B126").Value = Sheets(1).Rows(4).Find(Date).Offset(1,
0).Resize(125, 1).Value
End Sub

Of course I'd have to add some error trapping to avoid bombing if today's
date is not found.

I would still like a Formula solution, though.
 
S

Search33

David said:
David wrote


I was able to accomplish what I wanted with this VBA one-liner:
Sub CopyToday()
Sheets(2).Range("B2:B126").Value = Sheets(1).Rows(4).Find(Date).Offset(1,
0).Resize(125, 1).Value
End Sub

Of course I'd have to add some error trapping to avoid bombing if today's
date is not found.

I would still like a Formula solution, though.
Click to expand...

Try this
in
B2...=IF(ISERROR(HLOOKUP(TODAY(),$C$4:$F$129,2,FALSE)),"",HLOOKUP(TODAY(),$C$4:$F$129,2,FALSE))
in
B3...=IF(ISERROR(HLOOKUP(TODAY(),$C$4:$F$129,3,FALSE)),"",HLOOKUP(TODAY(),$C$4:$F$129,3,FALSE))

and so on...
 
Top