Find under date formula?

[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?

--
David

[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.
"David" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
> --
> David

[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?

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.

--
David

[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!
--
Gary''s Student


"David" wrote:

> 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?
>
> 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.
>
> --
> David
>

[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)

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.

--
David

[David]

David wrote

> =?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)
>
> 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.
>

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.

--
David

[Search33]

"David" wrote:

> David wrote
>
> > =?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)
> >
> > 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.
> >
>
> 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.
>
> --
> David
>

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...