Hello everyone,

I'm asking for help to solve this problem as I'm pretty sure that I'd need
to programmatically get this data rather than do it with functions. I might
be able to figure something out with functions but it would probably be a lot
neater otherwise.

What I have is a main page with peoples names in vertical columns with dates
across the top to use to match the data with. I also have worksheets with
those peoples names on them that store the data in more detail. What I'd like
to do is get the value of one cell (the sum total) of the detail numbers. The
sum is always in the same place, 8 rows down from a given date.

One of the biggest problems is that my detail worksheet does not have
contiguous ranges. They go from; F2:L9 , C12:L19 , C22:L29 and finally
C32:K39. Those ranges encompass both the date and the sum figure I want,
which is row 8 in this range.

Would it be possible to figure out a way on the main summary page to look at
the persons name, find the worksheet based on that persons name, match the
dates and record the sum from the detail page onto the summary page?

I sure as heck can't do it! :) Any help would sure be appreciated.

Thanks
John

John: The functio below will search any range of date, find a date, and then
return the value that is 8 rows below the date. To call the function from a
worksheet

=GetDatetotal(a1,sheet2!A1:a100)

where a1 contains a cell in date format

Function GetDateTotal(Mydate As Date, Target)

Set FindRange = Target.Find(Mydate)

myrow = FindRange.Row + 8
mycol = FindRange.Column

GetDateTotal = Cells(myrow, mycol).Value


End Function

"JKHouston" wrote:

Hello everyone,

I'm asking for help to solve this problem as I'm pretty sure that I'd need
to programmatically get this data rather than do it with functions. I might
be able to figure something out with functions but it would probably be a lot
neater otherwise.

What I have is a main page with peoples names in vertical columns with dates
across the top to use to match the data with. I also have worksheets with
those peoples names on them that store the data in more detail. What I'd like
to do is get the value of one cell (the sum total) of the detail numbers. The
sum is always in the same place, 8 rows down from a given date.

One of the biggest problems is that my detail worksheet does not have
contiguous ranges. They go from; F2:L9 , C12:L19 , C22:L29 and finally
C32:K39. Those ranges encompass both the date and the sum figure I want,
which is row 8 in this range.

Would it be possible to figure out a way on the main summary page to look at
the persons name, find the worksheet based on that persons name, match the
dates and record the sum from the detail page onto the summary page?

I sure as heck can't do it! :) Any help would sure be appreciated.

Thanks
John

Since you are getting the data from another sheet, you need to modify the
function like this:

Function GetDateTotal(Mydate As Date, Target)

Set FindRange = Target.Find(Mydate)

myrow = FindRange.Row + 8
mycol = FindRange.Column

GetDateTotal = FindRange.Parent.Cells(myrow, mycol).Value


End Function

otherwise you return information from the sheet with the formula which would
be meaninless in the context it has been presented.

Also, Find won't work in A User Defined function in xl2000 and earlier.
(just some information for the OP).

--
Regards,
Tom Ogilvy


"Joel" wrote in message
...
John: The functio below will search any range of date, find a date, and
then
return the value that is 8 rows below the date. To call the function from
a
worksheet

=GetDatetotal(a1,sheet2!A1:a100)

where a1 contains a cell in date format

Function GetDateTotal(Mydate As Date, Target)

Set FindRange = Target.Find(Mydate)

myrow = FindRange.Row + 8
mycol = FindRange.Column

GetDateTotal = Cells(myrow, mycol).Value


End Function

"JKHouston" wrote:

Hello everyone,

I'm asking for help to solve this problem as I'm pretty sure that I'd
need
to programmatically get this data rather than do it with functions. I
might
be able to figure something out with functions but it would probably be a
lot
neater otherwise.

What I have is a main page with peoples names in vertical columns with
dates
across the top to use to match the data with. I also have worksheets with
those peoples names on them that store the data in more detail. What I'd
like
to do is get the value of one cell (the sum total) of the detail numbers.
The
sum is always in the same place, 8 rows down from a given date.

One of the biggest problems is that my detail worksheet does not have
contiguous ranges. They go from; F2:L9 , C12:L19 , C22:L29 and finally
C32:K39. Those ranges encompass both the date and the sum figure I want,
which is row 8 in this range.

Would it be possible to figure out a way on the main summary page to look
at
the persons name, find the worksheet based on that persons name, match
the
dates and record the sum from the detail page onto the summary page?

I sure as heck can't do it! :) Any help would sure be appreciated.

Thanks
John

I am using xl2000 so it would seem that 'find' is not a viable option for me.
I sure appreciate the advice both gave tho'.

Regards,
John


"Tom Ogilvy" wrote:

Since you are getting the data from another sheet, you need to modify the
function like this:

Function GetDateTotal(Mydate As Date, Target)

Set FindRange = Target.Find(Mydate)

myrow = FindRange.Row + 8
mycol = FindRange.Column

GetDateTotal = FindRange.Parent.Cells(myrow, mycol).Value


End Function

otherwise you return information from the sheet with the formula which would
be meaninless in the context it has been presented.

Also, Find won't work in A User Defined function in xl2000 and earlier.
(just some information for the OP).

--
Regards,
Tom Ogilvy


"Joel" wrote in message
...
John: The functio below will search any range of date, find a date, and
then
return the value that is 8 rows below the date. To call the function from
a
worksheet

=GetDatetotal(a1,sheet2!A1:a100)

where a1 contains a cell in date format

Function GetDateTotal(Mydate As Date, Target)

Set FindRange = Target.Find(Mydate)

myrow = FindRange.Row + 8
mycol = FindRange.Column

GetDateTotal = Cells(myrow, mycol).Value


End Function

"JKHouston" wrote:

Hello everyone,

I'm asking for help to solve this problem as I'm pretty sure that I'd
need
to programmatically get this data rather than do it with functions. I
might
be able to figure something out with functions but it would probably be a
lot
neater otherwise.

What I have is a main page with peoples names in vertical columns with
dates
across the top to use to match the data with. I also have worksheets with
those peoples names on them that store the data in more detail. What I'd
like
to do is get the value of one cell (the sum total) of the detail numbers.
The
sum is always in the same place, 8 rows down from a given date.

One of the biggest problems is that my detail worksheet does not have
contiguous ranges. They go from; F2:L9 , C12:L19 , C22:L29 and finally
C32:K39. Those ranges encompass both the date and the sum figure I want,
which is row 8 in this range.

Would it be possible to figure out a way on the main summary page to look
at
the persons name, find the worksheet based on that persons name, match
the
dates and record the sum from the detail page onto the summary page?

I sure as heck can't do it! :) Any help would sure be appreciated.

Thanks
John

Hi Tom,

I wonder if you would have an alternate method that I could use to acheive
the same result as the "find" system that both Joel suggested and you
improved on? I am using excel 2000 so it would seem that I can't use the code
provided.

It's quite frustrating as, after looking at what you had written, that was
exactly what I was looking for!

If you would have some other suggestion as to what I could do, I'd really
appreciate it!

Thanks,
John


"Tom Ogilvy" wrote:

Since you are getting the data from another sheet, you need to modify the
function like this:

Function GetDateTotal(Mydate As Date, Target)

Set FindRange = Target.Find(Mydate)

myrow = FindRange.Row + 8
mycol = FindRange.Column

GetDateTotal = FindRange.Parent.Cells(myrow, mycol).Value


End Function

otherwise you return information from the sheet with the formula which would
be meaninless in the context it has been presented.

Also, Find won't work in A User Defined function in xl2000 and earlier.
(just some information for the OP).

--
Regards,
Tom Ogilvy


"Joel" wrote in message
...
John: The functio below will search any range of date, find a date, and
then
return the value that is 8 rows below the date. To call the function from
a
worksheet

=GetDatetotal(a1,sheet2!A1:a100)

where a1 contains a cell in date format

Function GetDateTotal(Mydate As Date, Target)

Set FindRange = Target.Find(Mydate)

myrow = FindRange.Row + 8
mycol = FindRange.Column

GetDateTotal = Cells(myrow, mycol).Value


End Function

"JKHouston" wrote:

Hello everyone,

I'm asking for help to solve this problem as I'm pretty sure that I'd
need
to programmatically get this data rather than do it with functions. I
might
be able to figure something out with functions but it would probably be a
lot
neater otherwise.

What I have is a main page with peoples names in vertical columns with
dates
across the top to use to match the data with. I also have worksheets with
those peoples names on them that store the data in more detail. What I'd
like
to do is get the value of one cell (the sum total) of the detail numbers.
The
sum is always in the same place, 8 rows down from a given date.

One of the biggest problems is that my detail worksheet does not have
contiguous ranges. They go from; F2:L9 , C12:L19 , C22:L29 and finally
C32:K39. Those ranges encompass both the date and the sum figure I want,
which is row 8 in this range.

Would it be possible to figure out a way on the main summary page to look
at
the persons name, find the worksheet based on that persons name, match
the
dates and record the sum from the detail page onto the summary page?

I sure as heck can't do it! :) Any help would sure be appreciated.

Thanks
John

Maybe you can use a UDF like:

Option Explicit
Function GetDateTotal(Mydate As Date, Target) As Variant

Dim myCol As Range
Dim res As Variant
Dim myVal As Variant

For Each myCol In Target.Columns
res = Application.Match(CLng(Mydate), myCol, 0)
If IsError(res) Then
'keep looking
Else
Exit For
End If
Next myCol

If IsError(res) Then
myVal = "Date Not found"
Else
myVal = myCol.Cells(1, 1).Offset(res + 7, 0).Value
End If

GetDateTotal = myVal

End Function

And still call it with:
=getdatetotal(a1,sheet2!a:e)

jkhouston wrote:

Hi Tom,

I wonder if you would have an alternate method that I could use to acheive
the same result as the "find" system that both Joel suggested and you
improved on? I am using excel 2000 so it would seem that I can't use the code
provided.

It's quite frustrating as, after looking at what you had written, that was
exactly what I was looking for!

If you would have some other suggestion as to what I could do, I'd really
appreciate it!

Thanks,
John

"Tom Ogilvy" wrote:

Since you are getting the data from another sheet, you need to modify the
function like this:

Function GetDateTotal(Mydate As Date, Target)

Set FindRange = Target.Find(Mydate)

myrow = FindRange.Row + 8
mycol = FindRange.Column

GetDateTotal = FindRange.Parent.Cells(myrow, mycol).Value


End Function

otherwise you return information from the sheet with the formula which would
be meaninless in the context it has been presented.

Also, Find won't work in A User Defined function in xl2000 and earlier.
(just some information for the OP).

--
Regards,
Tom Ogilvy


"Joel" wrote in message
...
John: The functio below will search any range of date, find a date, and
then
return the value that is 8 rows below the date. To call the function from
a
worksheet

=GetDatetotal(a1,sheet2!A1:a100)

where a1 contains a cell in date format

Function GetDateTotal(Mydate As Date, Target)

Set FindRange = Target.Find(Mydate)

myrow = FindRange.Row + 8
mycol = FindRange.Column

GetDateTotal = Cells(myrow, mycol).Value


End Function

"JKHouston" wrote:

Hello everyone,

I'm asking for help to solve this problem as I'm pretty sure that I'd
need
to programmatically get this data rather than do it with functions. I
might
be able to figure something out with functions but it would probably be a
lot
neater otherwise.

What I have is a main page with peoples names in vertical columns with
dates
across the top to use to match the data with. I also have worksheets with
those peoples names on them that store the data in more detail. What I'd
like
to do is get the value of one cell (the sum total) of the detail numbers.
The
sum is always in the same place, 8 rows down from a given date.

One of the biggest problems is that my detail worksheet does not have
contiguous ranges. They go from; F2:L9 , C12:L19 , C22:L29 and finally
C32:K39. Those ranges encompass both the date and the sum figure I want,
which is row 8 in this range.

Would it be possible to figure out a way on the main summary page to look
at
the persons name, find the worksheet based on that persons name, match
the
dates and record the sum from the detail page onto the summary page?

I sure as heck can't do it! :) Any help would sure be appreciated.

Thanks
John




--

Dave Peterson

Hi Dave,

I haven't had the time to try this yet but I wanted to say thanks for your
effort! I'll report back once I've had the opportunity to try it out.

Thanks again,
John

"Dave Peterson" wrote:

Maybe you can use a UDF like:

Option Explicit
Function GetDateTotal(Mydate As Date, Target) As Variant

Dim myCol As Range
Dim res As Variant
Dim myVal As Variant

For Each myCol In Target.Columns
res = Application.Match(CLng(Mydate), myCol, 0)
If IsError(res) Then
'keep looking
Else
Exit For
End If
Next myCol

If IsError(res) Then
myVal = "Date Not found"
Else
myVal = myCol.Cells(1, 1).Offset(res + 7, 0).Value
End If

GetDateTotal = myVal

End Function

And still call it with:
=getdatetotal(a1,sheet2!a:e)

jkhouston wrote:

Hi Tom,

I wonder if you would have an alternate method that I could use to acheive
the same result as the "find" system that both Joel suggested and you
improved on? I am using excel 2000 so it would seem that I can't use the code
provided.

It's quite frustrating as, after looking at what you had written, that was
exactly what I was looking for!

If you would have some other suggestion as to what I could do, I'd really
appreciate it!

Thanks,
John

"Tom Ogilvy" wrote:

Since you are getting the data from another sheet, you need to modify the
function like this:

Function GetDateTotal(Mydate As Date, Target)

Set FindRange = Target.Find(Mydate)

myrow = FindRange.Row + 8
mycol = FindRange.Column

GetDateTotal = FindRange.Parent.Cells(myrow, mycol).Value


End Function

otherwise you return information from the sheet with the formula which would
be meaninless in the context it has been presented.

Also, Find won't work in A User Defined function in xl2000 and earlier.
(just some information for the OP).

--
Regards,
Tom Ogilvy


"Joel" wrote in message
...
John: The functio below will search any range of date, find a date, and
then
return the value that is 8 rows below the date. To call the function from
a
worksheet

=GetDatetotal(a1,sheet2!A1:a100)

where a1 contains a cell in date format

Function GetDateTotal(Mydate As Date, Target)

Set FindRange = Target.Find(Mydate)

myrow = FindRange.Row + 8
mycol = FindRange.Column

GetDateTotal = Cells(myrow, mycol).Value


End Function

"JKHouston" wrote:

Hello everyone,

I'm asking for help to solve this problem as I'm pretty sure that I'd
need
to programmatically get this data rather than do it with functions. I
might
be able to figure something out with functions but it would probably be a
lot
neater otherwise.

What I have is a main page with peoples names in vertical columns with
dates
across the top to use to match the data with. I also have worksheets with
those peoples names on them that store the data in more detail. What I'd
like
to do is get the value of one cell (the sum total) of the detail numbers.
The
sum is always in the same place, 8 rows down from a given date.

One of the biggest problems is that my detail worksheet does not have
contiguous ranges. They go from; F2:L9 , C12:L19 , C22:L29 and finally
C32:K39. Those ranges encompass both the date and the sum figure I want,
which is row 8 in this range.

Would it be possible to figure out a way on the main summary page to look
at
the persons name, find the worksheet based on that persons name, match
the
dates and record the sum from the detail page onto the summary page?

I sure as heck can't do it! :) Any help would sure be appreciated.

Thanks
John




--

Dave Peterson

Hi Dave,

I tried your routine and it came back with the #name? error. I tried to look
thru it and see where it was failing but no luck.

Thanks


"Dave Peterson" wrote:

Maybe you can use a UDF like:

Option Explicit
Function GetDateTotal(Mydate As Date, Target) As Variant

Dim myCol As Range
Dim res As Variant
Dim myVal As Variant

For Each myCol In Target.Columns
res = Application.Match(CLng(Mydate), myCol, 0)
If IsError(res) Then
'keep looking
Else
Exit For
End If
Next myCol

If IsError(res) Then
myVal = "Date Not found"
Else
myVal = myCol.Cells(1, 1).Offset(res + 7, 0).Value
End If

GetDateTotal = myVal

End Function

And still call it with:
=getdatetotal(a1,sheet2!a:e)

jkhouston wrote:

Hi Tom,

I wonder if you would have an alternate method that I could use to acheive
the same result as the "find" system that both Joel suggested and you
improved on? I am using excel 2000 so it would seem that I can't use the code
provided.

It's quite frustrating as, after looking at what you had written, that was
exactly what I was looking for!

If you would have some other suggestion as to what I could do, I'd really
appreciate it!

Thanks,
John

"Tom Ogilvy" wrote:

Since you are getting the data from another sheet, you need to modify the
function like this:

Function GetDateTotal(Mydate As Date, Target)

Set FindRange = Target.Find(Mydate)

myrow = FindRange.Row + 8
mycol = FindRange.Column

GetDateTotal = FindRange.Parent.Cells(myrow, mycol).Value


End Function

otherwise you return information from the sheet with the formula which would
be meaninless in the context it has been presented.

Also, Find won't work in A User Defined function in xl2000 and earlier.
(just some information for the OP).

--
Regards,
Tom Ogilvy


"Joel" wrote in message
...
John: The functio below will search any range of date, find a date, and
then
return the value that is 8 rows below the date. To call the function from
a
worksheet

=GetDatetotal(a1,sheet2!A1:a100)

where a1 contains a cell in date format

Function GetDateTotal(Mydate As Date, Target)

Set FindRange = Target.Find(Mydate)

myrow = FindRange.Row + 8
mycol = FindRange.Column

GetDateTotal = Cells(myrow, mycol).Value


End Function

"JKHouston" wrote:

Hello everyone,

I'm asking for help to solve this problem as I'm pretty sure that I'd
need
to programmatically get this data rather than do it with functions. I
might
be able to figure something out with functions but it would probably be a
lot
neater otherwise.

What I have is a main page with peoples names in vertical columns with
dates
across the top to use to match the data with. I also have worksheets with
those peoples names on them that store the data in more detail. What I'd
like
to do is get the value of one cell (the sum total) of the detail numbers.
The
sum is always in the same place, 8 rows down from a given date.

One of the biggest problems is that my detail worksheet does not have
contiguous ranges. They go from; F2:L9 , C12:L19 , C22:L29 and finally
C32:K39. Those ranges encompass both the date and the sum figure I want,
which is row 8 in this range.

Would it be possible to figure out a way on the main summary page to look
at
the persons name, find the worksheet based on that persons name, match
the
dates and record the sum from the detail page onto the summary page?

I sure as heck can't do it! :) Any help would sure be appreciated.

Thanks
John




--

Dave Peterson