Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Data on a Worksheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Data on a Worksheet
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Data on a Worksheet
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Data on a Worksheet
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Data on a Worksheet
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Data on a Worksheet
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Data on a Worksheet
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Data on a Worksheet
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Data on a Worksheet
Did you put this in a General module?
Don't put it under ThisWorkbook or behind a worksheet. jkhouston wrote: 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 -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Data on a Worksheet
No, I put it under "thisworkbook" I'll try it under a general module as you
suggested. Thanks! "Dave Peterson" wrote: Did you put this in a General module? Don't put it under ThisWorkbook or behind a worksheet. jkhouston wrote: 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 -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Data on a Worksheet
that's it, thanks a lot!
I wonder if I could pick your brains a little more? :) just a quick example of the summary page format. 02/09 02/19 03/11 dave 5 15 6 john 18 30 4 jim 50 9 18 Would it be possible to select the worksheet from the players name shown? The routine you wrote works in a columnar range A:L so we would need to have the range like; dave!A:L each worksheet is named after the players. In fact I think you added an error handler for, and improved, the code I used to rename the worksheets by using cell A1 on the sheets. Also, would it be possible to find the min and max values per date and display the results in this format? Low Score High Score 02/09 dave "5" jim "50" 02/19 jim "9" john "30" and so on. I understand that this is a lot to ask for so if you don't have the time, I'll understand. I really appreciate what you've done so far, thanks again! Best regards, John "Dave Peterson" wrote: Did you put this in a General module? Don't put it under ThisWorkbook or behind a worksheet. jkhouston wrote: 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 -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Data on a Worksheet
I'm not sure I understand.
If you have the summary sheet, you could get a table like: Low Score High Score 02/09 dave "5" jim "50" 02/19 jim "9" john "30" with a little manipulation and formulas. I put your sample table in A1:D4. Headers in B1:D1 and A2:A4. Select the date headers edit|copy Select a range A11 (say) away from that table edit|paste special|transpose Now all the dates will be in A11:A13 Then I added headers in B10:e10 (Min, Min Name, Max, Max Name) And these formulas: B11: =MIN(INDEX($B$2:$D$4,0,MATCH($A11,$B$1:$D$1,0))) C11: =INDEX($A$2:$A$4,MATCH(B11,INDEX($B$2:$D$4,0,MATCH ($A11,$B$1:$D$1,0)),0)) D11: =MAX(INDEX($B$2:$D$4,0,MATCH($A11,$B$1:$D$1,0))) E11: =INDEX($A$2:$A$4,MATCH(D13,INDEX($B$2:$D$4,0,MATCH ($A13,$B$1:$D$1,0)),0)) And copy all 4 formulas down. Adjust the addresses to what you need (include the sheet names if the formulas are on a different sheet) and I think that this will work. ====== Notice that B2:D4 is the range with the actual data. and the matches just pick out the correct column (matching the date in column A to the dates in the table header row). jkhouston wrote: that's it, thanks a lot! I wonder if I could pick your brains a little more? :) just a quick example of the summary page format. 02/09 02/19 03/11 dave 5 15 6 john 18 30 4 jim 50 9 18 Would it be possible to select the worksheet from the players name shown? The routine you wrote works in a columnar range A:L so we would need to have the range like; dave!A:L each worksheet is named after the players. In fact I think you added an error handler for, and improved, the code I used to rename the worksheets by using cell A1 on the sheets. Also, would it be possible to find the min and max values per date and display the results in this format? Low Score High Score 02/09 dave "5" jim "50" 02/19 jim "9" john "30" and so on. I understand that this is a lot to ask for so if you don't have the time, I'll understand. I really appreciate what you've done so far, thanks again! Best regards, John "Dave Peterson" wrote: Did you put this in a General module? Don't put it under ThisWorkbook or behind a worksheet. jkhouston wrote: 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 -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Data on a Worksheet
Hi Dave,
That's what I was looking for, thanks very much. I'm sorry that I was confusing in my question, I was actually asking two seperate things. Is there a way to select a worksheet other than implicitly stating it in the formula? Going back to the "getdatetotal(date, target)" routine, is there a way to identify the target by selecting a cell rather than writing in 'jim brady'!A:L ? the way it is now the formula would read, getdatetotal(b2,'jim brady'!A:L), and what I'd like it to be is something like, getdatetotal(b2,a4!A:L). I know the syntax is wrong, I'm just trying to convey the intent. This way, I can change the name in cell A4 and have the formula automatically go to the correct worksheet instead of having to change all the references to a specific worksheet. Is that possible? Thanks again for all your input, you're saving me hours and hours of work! Best regards, John "Dave Peterson" wrote: I'm not sure I understand. If you have the summary sheet, you could get a table like: Low Score High Score 02/09 dave "5" jim "50" 02/19 jim "9" john "30" with a little manipulation and formulas. I put your sample table in A1:D4. Headers in B1:D1 and A2:A4. Select the date headers edit|copy Select a range A11 (say) away from that table edit|paste special|transpose Now all the dates will be in A11:A13 Then I added headers in B10:e10 (Min, Min Name, Max, Max Name) And these formulas: B11: =MIN(INDEX($B$2:$D$4,0,MATCH($A11,$B$1:$D$1,0))) C11: =INDEX($A$2:$A$4,MATCH(B11,INDEX($B$2:$D$4,0,MATCH ($A11,$B$1:$D$1,0)),0)) D11: =MAX(INDEX($B$2:$D$4,0,MATCH($A11,$B$1:$D$1,0))) E11: =INDEX($A$2:$A$4,MATCH(D13,INDEX($B$2:$D$4,0,MATCH ($A13,$B$1:$D$1,0)),0)) And copy all 4 formulas down. Adjust the addresses to what you need (include the sheet names if the formulas are on a different sheet) and I think that this will work. ====== Notice that B2:D4 is the range with the actual data. and the matches just pick out the correct column (matching the date in column A to the dates in the table header row). jkhouston wrote: that's it, thanks a lot! I wonder if I could pick your brains a little more? :) just a quick example of the summary page format. 02/09 02/19 03/11 dave 5 15 6 john 18 30 4 jim 50 9 18 Would it be possible to select the worksheet from the players name shown? The routine you wrote works in a columnar range A:L so we would need to have the range like; dave!A:L each worksheet is named after the players. In fact I think you added an error handler for, and improved, the code I used to rename the worksheets by using cell A1 on the sheets. Also, would it be possible to find the min and max values per date and display the results in this format? Low Score High Score 02/09 dave "5" jim "50" 02/19 jim "9" john "30" and so on. I understand that this is a lot to ask for so if you don't have the time, I'll understand. I really appreciate what you've done so far, thanks again! Best regards, John "Dave Peterson" wrote: Did you put this in a General module? Don't put it under ThisWorkbook or behind a worksheet. jkhouston wrote: 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 -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Data on a Worksheet
maybe you mean something like:
=getdatetotal(date,indirect("'" & $a2 "'!A:L") or if you're going to point at the date in the header: =getdatetotal(b$1,indirect("'" & $a2 "'!A:L") Another option... Create your summary table so that it looks like: Name Date Qty (3 columns) Then you could use data|Pivottable to find those max/mins per date -- and do lots of other nice summary tables. jkhouston wrote: Hi Dave, That's what I was looking for, thanks very much. I'm sorry that I was confusing in my question, I was actually asking two seperate things. Is there a way to select a worksheet other than implicitly stating it in the formula? Going back to the "getdatetotal(date, target)" routine, is there a way to identify the target by selecting a cell rather than writing in 'jim brady'!A:L ? the way it is now the formula would read, getdatetotal(b2,'jim brady'!A:L), and what I'd like it to be is something like, getdatetotal(b2,a4!A:L). I know the syntax is wrong, I'm just trying to convey the intent. This way, I can change the name in cell A4 and have the formula automatically go to the correct worksheet instead of having to change all the references to a specific worksheet. Is that possible? Thanks again for all your input, you're saving me hours and hours of work! Best regards, John "Dave Peterson" wrote: I'm not sure I understand. If you have the summary sheet, you could get a table like: Low Score High Score 02/09 dave "5" jim "50" 02/19 jim "9" john "30" with a little manipulation and formulas. I put your sample table in A1:D4. Headers in B1:D1 and A2:A4. Select the date headers edit|copy Select a range A11 (say) away from that table edit|paste special|transpose Now all the dates will be in A11:A13 Then I added headers in B10:e10 (Min, Min Name, Max, Max Name) And these formulas: B11: =MIN(INDEX($B$2:$D$4,0,MATCH($A11,$B$1:$D$1,0))) C11: =INDEX($A$2:$A$4,MATCH(B11,INDEX($B$2:$D$4,0,MATCH ($A11,$B$1:$D$1,0)),0)) D11: =MAX(INDEX($B$2:$D$4,0,MATCH($A11,$B$1:$D$1,0))) E11: =INDEX($A$2:$A$4,MATCH(D13,INDEX($B$2:$D$4,0,MATCH ($A13,$B$1:$D$1,0)),0)) And copy all 4 formulas down. Adjust the addresses to what you need (include the sheet names if the formulas are on a different sheet) and I think that this will work. ====== Notice that B2:D4 is the range with the actual data. and the matches just pick out the correct column (matching the date in column A to the dates in the table header row). jkhouston wrote: that's it, thanks a lot! I wonder if I could pick your brains a little more? :) just a quick example of the summary page format. 02/09 02/19 03/11 dave 5 15 6 john 18 30 4 jim 50 9 18 Would it be possible to select the worksheet from the players name shown? The routine you wrote works in a columnar range A:L so we would need to have the range like; dave!A:L each worksheet is named after the players. In fact I think you added an error handler for, and improved, the code I used to rename the worksheets by using cell A1 on the sheets. Also, would it be possible to find the min and max values per date and display the results in this format? Low Score High Score 02/09 dave "5" jim "50" 02/19 jim "9" john "30" and so on. I understand that this is a lot to ask for so if you don't have the time, I'll understand. I really appreciate what you've done so far, thanks again! Best regards, John "Dave Peterson" wrote: Did you put this in a General module? Don't put it under ThisWorkbook or behind a worksheet. jkhouston wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Data on a Worksheet
Yeah! Exactly!
That's awesome! Thanks again Dave. :) Sure wish I could figure this out like you've been able to. ;) I really appreciate all the help, you've answered all my questions. Thanks again! Best regards, John Houston "Dave Peterson" wrote: maybe you mean something like: =getdatetotal(date,indirect("'" & $a2 "'!A:L") or if you're going to point at the date in the header: =getdatetotal(b$1,indirect("'" & $a2 "'!A:L") Another option... Create your summary table so that it looks like: Name Date Qty (3 columns) Then you could use data|Pivottable to find those max/mins per date -- and do lots of other nice summary tables. jkhouston wrote: Hi Dave, That's what I was looking for, thanks very much. I'm sorry that I was confusing in my question, I was actually asking two seperate things. Is there a way to select a worksheet other than implicitly stating it in the formula? Going back to the "getdatetotal(date, target)" routine, is there a way to identify the target by selecting a cell rather than writing in 'jim brady'!A:L ? the way it is now the formula would read, getdatetotal(b2,'jim brady'!A:L), and what I'd like it to be is something like, getdatetotal(b2,a4!A:L). I know the syntax is wrong, I'm just trying to convey the intent. This way, I can change the name in cell A4 and have the formula automatically go to the correct worksheet instead of having to change all the references to a specific worksheet. Is that possible? Thanks again for all your input, you're saving me hours and hours of work! Best regards, John "Dave Peterson" wrote: I'm not sure I understand. If you have the summary sheet, you could get a table like: Low Score High Score 02/09 dave "5" jim "50" 02/19 jim "9" john "30" with a little manipulation and formulas. I put your sample table in A1:D4. Headers in B1:D1 and A2:A4. Select the date headers edit|copy Select a range A11 (say) away from that table edit|paste special|transpose Now all the dates will be in A11:A13 Then I added headers in B10:e10 (Min, Min Name, Max, Max Name) And these formulas: B11: =MIN(INDEX($B$2:$D$4,0,MATCH($A11,$B$1:$D$1,0))) C11: =INDEX($A$2:$A$4,MATCH(B11,INDEX($B$2:$D$4,0,MATCH ($A11,$B$1:$D$1,0)),0)) D11: =MAX(INDEX($B$2:$D$4,0,MATCH($A11,$B$1:$D$1,0))) E11: =INDEX($A$2:$A$4,MATCH(D13,INDEX($B$2:$D$4,0,MATCH ($A13,$B$1:$D$1,0)),0)) And copy all 4 formulas down. Adjust the addresses to what you need (include the sheet names if the formulas are on a different sheet) and I think that this will work. ====== Notice that B2:D4 is the range with the actual data. and the matches just pick out the correct column (matching the date in column A to the dates in the table header row). jkhouston wrote: that's it, thanks a lot! I wonder if I could pick your brains a little more? :) just a quick example of the summary page format. 02/09 02/19 03/11 dave 5 15 6 john 18 30 4 jim 50 9 18 Would it be possible to select the worksheet from the players name shown? The routine you wrote works in a columnar range A:L so we would need to have the range like; dave!A:L each worksheet is named after the players. In fact I think you added an error handler for, and improved, the code I used to rename the worksheets by using cell A1 on the sheets. Also, would it be possible to find the min and max values per date and display the results in this format? Low Score High Score 02/09 dave "5" jim "50" 02/19 jim "9" john "30" and so on. I understand that this is a lot to ask for so if you don't have the time, I'll understand. I really appreciate what you've done so far, thanks again! Best regards, John "Dave Peterson" wrote: Did you put this in a General module? Don't put it under ThisWorkbook or behind a worksheet. jkhouston wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Data on a Worksheet
Glad you got it working.
jkhouston wrote: Yeah! Exactly! That's awesome! Thanks again Dave. :) Sure wish I could figure this out like you've been able to. ;) I really appreciate all the help, you've answered all my questions. Thanks again! Best regards, John Houston <<snipped |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
Import entire row of data to other worksheet based on one lookup v | Excel Discussion (Misc queries) | |||
Lookup on worksheet | Excel Discussion (Misc queries) | |||
lookup stock symbol on worksheet and return summary data | Excel Worksheet Functions | |||
Using Index and Match functions to lookup data in another worksheet | Excel Programming |