Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing a Value in variable length columns.
Hi All, I apologize if this is not the right place, I believe this falls
under teh category if worksheet functions; I have a SS I would like to reference a cell in a column which changes in length. In this case, I would like to reference the most current date in a column. Is there a simple method for doing this, as the data will routinely be different lengths in that column. Further, there is Data on the Horizontal that I would like to reference to another cell in another sheet that would correspond to this date. Can anyone give any suggestions as to the best way to accomdate this? Thank you for any suggestions and taking the time to read. Regards, Kevin |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing a Value in variable length columns.
The date would be
=MAX($A$1:$A$1000) the value in say column D corresponding to that would be =INDEX($D$1:$D$1000,MATCH(MAX($A$1:$A$1000),$A$1:$ A$1000,0)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Kevin" wrote in message ... Hi All, I apologize if this is not the right place, I believe this falls under teh category if worksheet functions; I have a SS I would like to reference a cell in a column which changes in length. In this case, I would like to reference the most current date in a column. Is there a simple method for doing this, as the data will routinely be different lengths in that column. Further, there is Data on the Horizontal that I would like to reference to another cell in another sheet that would correspond to this date. Can anyone give any suggestions as to the best way to accomdate this? Thank you for any suggestions and taking the time to read. Regards, Kevin |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing a Value in variable length columns.
Thanks Bob, I will give that a try.
I presume that if the range to be referenced is in another sheet, it would merely include the Sheet Name, ie: =MAX('SheetName'!$A$1:$A$1000) Thanks for the help! :) Kevin "Bob Phillips" wrote: The date would be =MAX($A$1:$A$1000) the value in say column D corresponding to that would be =INDEX($D$1:$D$1000,MATCH(MAX($A$1:$A$1000),$A$1:$ A$1000,0)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Kevin" wrote in message ... Hi All, I apologize if this is not the right place, I believe this falls under teh category if worksheet functions; I have a SS I would like to reference a cell in a column which changes in length. In this case, I would like to reference the most current date in a column. Is there a simple method for doing this, as the data will routinely be different lengths in that column. Further, there is Data on the Horizontal that I would like to reference to another cell in another sheet that would correspond to this date. Can anyone give any suggestions as to the best way to accomdate this? Thank you for any suggestions and taking the time to read. Regards, Kevin |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing a Value in variable length columns.
Hi Bob,
I tried the function below, and it returns a value of "00/01/1900" A couple things I was wondering if they may be the cause: 1) The column being referenced houses the date in the following format: 01/03/2006 16:00:00 2) The first row contains header text (Although I tried adjusting the range to where the date starts, and it returns the same result) 3) There are a number of blank cells throughout the range (All celss are date formatted however) Thanks again! Kevin "Bob Phillips" wrote: The date would be =MAX($A$1:$A$1000) the value in say column D corresponding to that would be =INDEX($D$1:$D$1000,MATCH(MAX($A$1:$A$1000),$A$1:$ A$1000,0)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Kevin" wrote in message ... Hi All, I apologize if this is not the right place, I believe this falls under teh category if worksheet functions; I have a SS I would like to reference a cell in a column which changes in length. In this case, I would like to reference the most current date in a column. Is there a simple method for doing this, as the data will routinely be different lengths in that column. Further, there is Data on the Horizontal that I would like to reference to another cell in another sheet that would correspond to this date. Can anyone give any suggestions as to the best way to accomdate this? Thank you for any suggestions and taking the time to read. Regards, Kevin |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing a Value in variable length columns.
That means that the value is zero but you are using date format, if you
change the format to general it will be zero, note that blank cells are also seen as zero by excel so if the latest date corresponds to an empty cell that would explain the result although you might had applied it incorrectly -- Regards, Peo Sjoblom Portland, Oregon "Kevin" wrote in message ... Hi Bob, I tried the function below, and it returns a value of "00/01/1900" A couple things I was wondering if they may be the cause: 1) The column being referenced houses the date in the following format: 01/03/2006 16:00:00 2) The first row contains header text (Although I tried adjusting the range to where the date starts, and it returns the same result) 3) There are a number of blank cells throughout the range (All celss are date formatted however) Thanks again! Kevin "Bob Phillips" wrote: The date would be =MAX($A$1:$A$1000) the value in say column D corresponding to that would be =INDEX($D$1:$D$1000,MATCH(MAX($A$1:$A$1000),$A$1:$ A$1000,0)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Kevin" wrote in message ... Hi All, I apologize if this is not the right place, I believe this falls under teh category if worksheet functions; I have a SS I would like to reference a cell in a column which changes in length. In this case, I would like to reference the most current date in a column. Is there a simple method for doing this, as the data will routinely be different lengths in that column. Further, there is Data on the Horizontal that I would like to reference to another cell in another sheet that would correspond to this date. Can anyone give any suggestions as to the best way to accomdate this? Thank you for any suggestions and taking the time to read. Regards, Kevin |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing a Value in variable length columns.
Thanks Guys, nailed down the problem...
Seems that because the Date field also has the Time attached, it is not picking up the Date as an actual date, resulting in the Zero value. I'll tinker and see if there is a way to seperate the date and time into seperate columns and into a format that Excel recognizes as a proper date and time. Thanks for the assistance, it really helped out and I learned something new. Cheers, Kevin "Peo Sjoblom" wrote: That means that the value is zero but you are using date format, if you change the format to general it will be zero, note that blank cells are also seen as zero by excel so if the latest date corresponds to an empty cell that would explain the result although you might had applied it incorrectly -- Regards, Peo Sjoblom Portland, Oregon "Kevin" wrote in message ... Hi Bob, I tried the function below, and it returns a value of "00/01/1900" A couple things I was wondering if they may be the cause: 1) The column being referenced houses the date in the following format: 01/03/2006 16:00:00 2) The first row contains header text (Although I tried adjusting the range to where the date starts, and it returns the same result) 3) There are a number of blank cells throughout the range (All celss are date formatted however) Thanks again! Kevin "Bob Phillips" wrote: The date would be =MAX($A$1:$A$1000) the value in say column D corresponding to that would be =INDEX($D$1:$D$1000,MATCH(MAX($A$1:$A$1000),$A$1:$ A$1000,0)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Kevin" wrote in message ... Hi All, I apologize if this is not the right place, I believe this falls under teh category if worksheet functions; I have a SS I would like to reference a cell in a column which changes in length. In this case, I would like to reference the most current date in a column. Is there a simple method for doing this, as the data will routinely be different lengths in that column. Further, there is Data on the Horizontal that I would like to reference to another cell in another sheet that would correspond to this date. Can anyone give any suggestions as to the best way to accomdate this? Thank you for any suggestions and taking the time to read. Regards, Kevin |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing a Value in variable length columns.
Kevin,
That doesn't seem to be the problem to me, a date with time in would still have a MAX 0. Which formula returns the 0, the MAX or the INDEX? Can u post some data to show the problem? -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Kevin" wrote in message ... Thanks Guys, nailed down the problem... Seems that because the Date field also has the Time attached, it is not picking up the Date as an actual date, resulting in the Zero value. I'll tinker and see if there is a way to seperate the date and time into seperate columns and into a format that Excel recognizes as a proper date and time. Thanks for the assistance, it really helped out and I learned something new. Cheers, Kevin "Peo Sjoblom" wrote: That means that the value is zero but you are using date format, if you change the format to general it will be zero, note that blank cells are also seen as zero by excel so if the latest date corresponds to an empty cell that would explain the result although you might had applied it incorrectly -- Regards, Peo Sjoblom Portland, Oregon "Kevin" wrote in message ... Hi Bob, I tried the function below, and it returns a value of "00/01/1900" A couple things I was wondering if they may be the cause: 1) The column being referenced houses the date in the following format: 01/03/2006 16:00:00 2) The first row contains header text (Although I tried adjusting the range to where the date starts, and it returns the same result) 3) There are a number of blank cells throughout the range (All celss are date formatted however) Thanks again! Kevin "Bob Phillips" wrote: The date would be =MAX($A$1:$A$1000) the value in say column D corresponding to that would be =INDEX($D$1:$D$1000,MATCH(MAX($A$1:$A$1000),$A$1:$ A$1000,0)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Kevin" wrote in message ... Hi All, I apologize if this is not the right place, I believe this falls under teh category if worksheet functions; I have a SS I would like to reference a cell in a column which changes in length. In this case, I would like to reference the most current date in a column. Is there a simple method for doing this, as the data will routinely be different lengths in that column. Further, there is Data on the Horizontal that I would like to reference to another cell in another sheet that would correspond to this date. Can anyone give any suggestions as to the best way to accomdate this? Thank you for any suggestions and taking the time to read. Regards, Kevin |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing a Value in variable length columns.
Hi Bob, sorry for teh delay, voting day here ;-)
Here is a data sample (I adjusted the formula accordingly as my data is in Column B): Column B: 01/03/2006 16:00:00 01/03/2006 16:15:00 01/03/2006 16:30:00 01/03/2006 16:45:00 01/03/2006 17:00:00 01/03/2006 17:15:00 01/03/2006 17:30:00 The start time will always be in the same cell (B4) but the end time can change, as the number of records can vary. The formula returning the Zero value is the MAX one for me. Here is what I have: "=MAX($B$4:$B$1000)" Which returns: "00/01/1900" My workaround was to use the MAX formula against Column A, which is simply an auto-number couting the number of data points in Column B (Currently 1 to 68) and then use the INDEX to reference the data in Column B adjacent to the MAX in B: "=INDEX($B$4:$B$1500,MATCH(MAX($A$4:$A$1500),$A$4: $A$1500,0))" This DOES return the MAX date and time, but then I ran into another problem: I used the same set up as described above to retrieve the MIN date and time, and then wanted to Subtract MIN from MAX and get a count of the total Hours and Minutes elapsed (HH:mm) but I can't seem to get it to acknowledge more than 24 hrs, which is a real possibility for this project (Ie: Start date of 01/03/2006 16:00:00, End date of 01/06/2006 21:00:00) Hopefully I described this correctly. Currently it's all a bit of a jumble in my head. Thanks for all your help Bob, I truly appreciate it! Cheers, Kevin "Bob Phillips" wrote: Kevin, That doesn't seem to be the problem to me, a date with time in would still have a MAX 0. Which formula returns the 0, the MAX or the INDEX? Can u post some data to show the problem? -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Kevin" wrote in message ... |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing a Value in variable length columns.
Kevin,
Canadian eh? I would guess that those numbers are not true date and time but text. Try this formula =MAX(--($B$4:$B$1000)) as an array formula, so commit with Ctrl-Shift-Enter5, not just Enter. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Kevin" wrote in message ... Hi Bob, sorry for teh delay, voting day here ;-) Here is a data sample (I adjusted the formula accordingly as my data is in Column B): Column B: 01/03/2006 16:00:00 01/03/2006 16:15:00 01/03/2006 16:30:00 01/03/2006 16:45:00 01/03/2006 17:00:00 01/03/2006 17:15:00 01/03/2006 17:30:00 The start time will always be in the same cell (B4) but the end time can change, as the number of records can vary. The formula returning the Zero value is the MAX one for me. Here is what I have: "=MAX($B$4:$B$1000)" Which returns: "00/01/1900" My workaround was to use the MAX formula against Column A, which is simply an auto-number couting the number of data points in Column B (Currently 1 to 68) and then use the INDEX to reference the data in Column B adjacent to the MAX in B: "=INDEX($B$4:$B$1500,MATCH(MAX($A$4:$A$1500),$A$4: $A$1500,0))" This DOES return the MAX date and time, but then I ran into another problem: I used the same set up as described above to retrieve the MIN date and time, and then wanted to Subtract MIN from MAX and get a count of the total Hours and Minutes elapsed (HH:mm) but I can't seem to get it to acknowledge more than 24 hrs, which is a real possibility for this project (Ie: Start date of 01/03/2006 16:00:00, End date of 01/06/2006 21:00:00) Hopefully I described this correctly. Currently it's all a bit of a jumble in my head. Thanks for all your help Bob, I truly appreciate it! Cheers, Kevin "Bob Phillips" wrote: Kevin, That doesn't seem to be the problem to me, a date with time in would still have a MAX 0. Which formula returns the 0, the MAX or the INDEX? Can u post some data to show the problem? -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Kevin" wrote in message ... |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing a Value in variable length columns.
Oh forgot to mention, with the tmes over 24 hours, format the result cell as
[h]:mm -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Kevin" wrote in message ... Hi Bob, sorry for teh delay, voting day here ;-) Here is a data sample (I adjusted the formula accordingly as my data is in Column B): Column B: 01/03/2006 16:00:00 01/03/2006 16:15:00 01/03/2006 16:30:00 01/03/2006 16:45:00 01/03/2006 17:00:00 01/03/2006 17:15:00 01/03/2006 17:30:00 The start time will always be in the same cell (B4) but the end time can change, as the number of records can vary. The formula returning the Zero value is the MAX one for me. Here is what I have: "=MAX($B$4:$B$1000)" Which returns: "00/01/1900" My workaround was to use the MAX formula against Column A, which is simply an auto-number couting the number of data points in Column B (Currently 1 to 68) and then use the INDEX to reference the data in Column B adjacent to the MAX in B: "=INDEX($B$4:$B$1500,MATCH(MAX($A$4:$A$1500),$A$4: $A$1500,0))" This DOES return the MAX date and time, but then I ran into another problem: I used the same set up as described above to retrieve the MIN date and time, and then wanted to Subtract MIN from MAX and get a count of the total Hours and Minutes elapsed (HH:mm) but I can't seem to get it to acknowledge more than 24 hrs, which is a real possibility for this project (Ie: Start date of 01/03/2006 16:00:00, End date of 01/06/2006 21:00:00) Hopefully I described this correctly. Currently it's all a bit of a jumble in my head. Thanks for all your help Bob, I truly appreciate it! Cheers, Kevin "Bob Phillips" wrote: Kevin, That doesn't seem to be the problem to me, a date with time in would still have a MAX 0. Which formula returns the 0, the MAX or the INDEX? Can u post some data to show the problem? -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Kevin" wrote in message ... |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing a Value in variable length columns.
Hi Bob, thanks, that gives me some options to work with. Still having some
issues with making it all work, but I think that has more to do with the Operator than the advice or program ;) Sorry for the delay, some how lost the post. Thanks again! Kevin "Bob Phillips" wrote: Oh forgot to mention, with the tmes over 24 hours, format the result cell as [h]:mm -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Kevin" wrote in message ... Hi Bob, sorry for teh delay, voting day here ;-) Here is a data sample (I adjusted the formula accordingly as my data is in Column B): Column B: 01/03/2006 16:00:00 01/03/2006 16:15:00 01/03/2006 16:30:00 01/03/2006 16:45:00 01/03/2006 17:00:00 01/03/2006 17:15:00 01/03/2006 17:30:00 The start time will always be in the same cell (B4) but the end time can change, as the number of records can vary. The formula returning the Zero value is the MAX one for me. Here is what I have: "=MAX($B$4:$B$1000)" Which returns: "00/01/1900" My workaround was to use the MAX formula against Column A, which is simply an auto-number couting the number of data points in Column B (Currently 1 to 68) and then use the INDEX to reference the data in Column B adjacent to the MAX in B: "=INDEX($B$4:$B$1500,MATCH(MAX($A$4:$A$1500),$A$4: $A$1500,0))" This DOES return the MAX date and time, but then I ran into another problem: I used the same set up as described above to retrieve the MIN date and time, and then wanted to Subtract MIN from MAX and get a count of the total Hours and Minutes elapsed (HH:mm) but I can't seem to get it to acknowledge more than 24 hrs, which is a real possibility for this project (Ie: Start date of 01/03/2006 16:00:00, End date of 01/06/2006 21:00:00) Hopefully I described this correctly. Currently it's all a bit of a jumble in my head. Thanks for all your help Bob, I truly appreciate it! Cheers, Kevin "Bob Phillips" wrote: Kevin, That doesn't seem to be the problem to me, a date with time in would still have a MAX 0. Which formula returns the 0, the MAX or the INDEX? Can u post some data to show the problem? -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Kevin" wrote in message ... |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing a Value in variable length columns.
May be.. =LOOKUP(9.999E+307,B4:B1500)-B4+(B4LOOKUP(9.999E+307,B4:B1500)) format the cell [h]:mm HTH -- Krishnakumar ------------------------------------------------------------------------ Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138 View this thread: http://www.excelforum.com/showthread...hreadid=503864 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing Columns in a Row. PLEASE HELP!! | Excel Worksheet Functions | |||
Excel 2003: In a Macro,how to select a variable row length table | Excel Discussion (Misc queries) | |||
Graph with variable data length | Excel Discussion (Misc queries) | |||
Referencing Variable Name Worksheets | New Users to Excel | |||
Sum a column of variable length? | Excel Discussion (Misc queries) |