Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection point return data
First, thanks to Peo for helping me truncate time AND lookup all in one formula. Second, I'd like help with an 'intersection' formula, if possible. For example, I have 100 rows and 100 columns, where row 1 is dates, and column A is times, and within are numbers. On a second sheet I have a column (A) with random dates and another (B) with times. What I need is a third column (C)on the second sheet that looks at the date and time, finds the intersection on the first sheet, and returns the number found there. Thanks in advance for any advice. Kiley -- tvtime ------------------------------------------------------------------------ tvtime's Profile: http://www.excelforum.com/member.php...o&userid=36676 View this thread: http://www.excelforum.com/showthread...hreadid=564172 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection point return data
You need to look at the match function and the index or offset function If the column heading are in sheet1 d6:h6 and are dates and the row headings are in c7:c16 and are times and in the second sheet the first column is time and the second is date and you are in cell c2 on the scond sheet try =INDEX(Sheet1!$D$7:$H$16,MATCH(A2,Sheet1!$C$7:$C$1 6,0),MATCH(B2,Sheet1!$D$6:$H$6,0)) or =offset(sheet1!$c$6,MATCH(A2,Sheet1!$C$7:$C$16,0), MATCH(B2,Sheet1!$D$6:$H$6,0)) In both instances I have assumed an exact match, if it the value nearest above or below the formula needs to be ammended Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=564172 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection point return data
Thanks Dav, I can't wait to try it... I'll let you know how it goes. Thanks again -- tvtime ------------------------------------------------------------------------ tvtime's Profile: http://www.excelforum.com/member.php...o&userid=36676 View this thread: http://www.excelforum.com/showthread...hreadid=564172 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection point return data
I've tried both and can't get it to work. Here's my code: =INDEX(WTVF!$B$2:$EX$97,MATCH(E3,WTVF!$B$1:$EX$1,0 ),MATCH(G3,WTVF!$A$2:$A$97,0)) where the sheet name is 'WTVF' and the data lives in cells B2:EX97, dates are in the top row B1:EX1, and times are in A2:A97. On the second sheet, my date is in E3, and my time is in G3, with the formula in L3. I've tried this also as an array, but to no avail. Any further assistance is appreciated. Thanks! -- tvtime ------------------------------------------------------------------------ tvtime's Profile: http://www.excelforum.com/member.php...o&userid=36676 View this thread: http://www.excelforum.com/showthread...hreadid=564172 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection point return data
Just flip the matches around:
=INDEX(WTVF!$B$2:$EX$97,MATCH(G3,WTVF!$A$2:$A$97,0 ),MATCH(E3,WTVF!$B$1:$EX$1,0))The first match is for the row number and the second match is for the columnnumber.Biff"tvtime" wrote inmessage ... I've tried both and can't get it to work. Here's my code:=INDEX(WTVF!$B$2:$EX$97,MATCH(E3,WTVF!$B$1:$ EX$1,0),MATCH(G3,WTVF!$A$2:$A$97,0)) where the sheet name is 'WTVF' and the data lives in cells B2:EX97, dates are in the top row B1:EX1, and times are in A2:A97. On the second sheet, my date is in E3, and my time is in G3, with the formula in L3. I've tried this also as an array, but to no avail. Any further assistance is appreciated. Thanks! -- tvtime ------------------------------------------------------------------------ tvtime's Profile:http://www.excelforum.com/member.php?action=getinfo&userid=36676 View this thread: http://www.excelforum.com/showthread.php?threadid=564172 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection point return data
First, thanks Biff for the help, but it did not work. I've checked the formats of all cells and tried it also as an array. Any thoughts of other checks? Thanks! -- tvtime ------------------------------------------------------------------------ tvtime's Profile: http://www.excelforum.com/member.php...o&userid=36676 View this thread: http://www.excelforum.com/showthread...hreadid=564172 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection point return data
Can you post a sample file?
I see you're posting from Excelforum. They allow uploads. Just zip the file. Biff "tvtime" wrote in message ... First, thanks Biff for the help, but it did not work. I've checked the formats of all cells and tried it also as an array. Any thoughts of other checks? Thanks! -- tvtime ------------------------------------------------------------------------ tvtime's Profile: http://www.excelforum.com/member.php...o&userid=36676 View this thread: http://www.excelforum.com/showthread...hreadid=564172 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection point return data
Thanks for the offer, and I will if needed, but I think I'm really close now. The formula you sent is working now. For some reason, the E3 (and the rest of the column) date field on the second sheet wasn't really being seen as a date. Even though I formatted as a date, and it looked like a date.... for some reason, if I simply put the cursor in front of the date inside the cell and hit backspace, then enter, it is seen as a date and the INDEX formula works. So now I have a new challenge... automating that task of making it REALLY a date as recognized by Excel....macro or formula... I'd take either one. Thanks again for all your time and help! -- tvtime ------------------------------------------------------------------------ tvtime's Profile: http://www.excelforum.com/member.php...o&userid=36676 View this thread: http://www.excelforum.com/showthread...hreadid=564172 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection point return data
I might have actually done some of the lifting on this... I might be able to use DATEVALUE... -- tvtime ------------------------------------------------------------------------ tvtime's Profile: http://www.excelforum.com/member.php...o&userid=36676 View this thread: http://www.excelforum.com/showthread...hreadid=564172 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection point return data
That's what I get for thinking... it doesn't work with DATEVALUE... still would like help... Thanks! -- tvtime ------------------------------------------------------------------------ tvtime's Profile: http://www.excelforum.com/member.php...o&userid=36676 View this thread: http://www.excelforum.com/showthread...hreadid=564172 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection point return data
My dilemma at this point is that for some unknown (to me) reason, some values in L (second sheet), are working, and some are not. I've uploaded to my site because the file is over the 100K limit allowed. It's a zip file, link is below. http://www.ekiley.com/Dailys2006may-sep.zip The goal is to get the L column (UD Placement sheet) to fill in from the WTVF sheet. I've reached the limit of my brain power... and would appreciate your (or anyone else out there) eyes. Thanks, Kiley -- tvtime ------------------------------------------------------------------------ tvtime's Profile: http://www.excelforum.com/member.php...o&userid=36676 View this thread: http://www.excelforum.com/showthread...hreadid=564172 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection point return data
Try this:
Select an empty unused cell (one that hasn't been preformatted or ever used) Copy that cell by going to EditCopy Now select the cells that contain the dates. I guess that would be B1:EX1 Goto EditPaste SpecialAddOK That will usually convert TEXT dates into numeric DATE dates. Dates and times are really just numbers formatted to look like a date and/or time. Biff "tvtime" wrote in message ... That's what I get for thinking... it doesn't work with DATEVALUE... still would like help... Thanks! -- tvtime ------------------------------------------------------------------------ tvtime's Profile: http://www.excelforum.com/member.php...o&userid=36676 View this thread: http://www.excelforum.com/showthread...hreadid=564172 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection point return data
I did try the paste special trick, and it did convert my dates/times to numbers, but the cells in L that weren't working still didn't work. If you have a minute to look at the file (from the link above), I'd appreciate your insight. And I'm very grateful for your help, whether we find a resolution or not. Have a great day. -- tvtime ------------------------------------------------------------------------ tvtime's Profile: http://www.excelforum.com/member.php...o&userid=36676 View this thread: http://www.excelforum.com/showthread...hreadid=564172 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection point return data
Ok, I've looked at your file.........
The problem is you have "rounding issues", aka "the fudge factor". For example: 3:30 < 3:30 so you get #N/A Although you may be thinking to yourself, 3:30 DOES IN FACT = 3:30. Well, not necessarily! This may take a while to straighten out! Biff "tvtime" wrote in message ... I did try the paste special trick, and it did convert my dates/times to numbers, but the cells in L that weren't working still didn't work. If you have a minute to look at the file (from the link above), I'd appreciate your insight. And I'm very grateful for your help, whether we find a resolution or not. Have a great day. -- tvtime ------------------------------------------------------------------------ tvtime's Profile: http://www.excelforum.com/member.php...o&userid=36676 View this thread: http://www.excelforum.com/showthread...hreadid=564172 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection point return data
Well, I can't say I understand completely, but I appreciate anything you're able to come up with. And I wasn't aware the words 'fudge factor' were in the excel dictionary... :) Thank you very much! -- tvtime ------------------------------------------------------------------------ tvtime's Profile: http://www.excelforum.com/member.php...o&userid=36676 View this thread: http://www.excelforum.com/showthread...hreadid=564172 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection point return data
Ok.....
Here's what I did: You don't need the array lookup formula to get the "Converted" time in column G of sheet placement: =INDEX($B$2:$B$1441,MATCH(TEXT(F3,"hh:mm"),TEXT($A $2:$A$1441,"hh:mm"),0)) I replaced that formula with this one: =FLOOR(D3,"0:15:0") Since you don't need the lookup formula that also means you don't need the lookup table so I deleted columns A and B of sheet placement. Column F, where your times are, is now column D (which is why: =FLOOR(D3,"0:15:0") refers to cell D3) On the WTVF sheet I "manipulated" the times in column A so that they *ALL* would match the results of the "Converted" time column on sheet placement. That's what your problem was. I don't know how you generated the series of times in your original lookup table but if you fill a series in certain ways it can lead to "rounding issues" that cause things like: 3:30 does not match 3:30. The same thing applies to the time series on sheet WTVF. I also reset the used range on sheet placement since I deleted the 1440 x 2 lookup table. The file size is now 155kb, versus the original 462kb. Send me an email and I'll return your file. I don't have a place to upload. My address is: xl can help at comcast period net Remove "can" and change the obvious Biff "tvtime" wrote in message ... Well, I can't say I understand completely, but I appreciate anything you're able to come up with. And I wasn't aware the words 'fudge factor' were in the excel dictionary... :) Thank you very much! -- tvtime ------------------------------------------------------------------------ tvtime's Profile: http://www.excelforum.com/member.php...o&userid=36676 View this thread: http://www.excelforum.com/showthread...hreadid=564172 |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection point return data
I've sent you an email. and I've probably met my 'thank you' quota, but you certainly deserve it... Kiley -- tvtime ------------------------------------------------------------------------ tvtime's Profile: http://www.excelforum.com/member.php...o&userid=36676 View this thread: http://www.excelforum.com/showthread...hreadid=564172 |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection point return data
Hi tvtime
I saw all your messages posted. I guess you have typed the function incorrect =INDEX(WTVF!$B$2:$EX$97,MATCH(E3,WTVF!$B$1:$EX$1,0 ),MATCH(G3,WTVF!$A$2:$A$9*7,0)) If you see you need to change it this way =INDEX(WTVF!$A$2:$EX$97,MATCH(E3,WTVF!$B$1:$EX$1,0 ),MATCH(G3,WTVF!$A$2:$A$9*7,0)) Try this one. I wish this works for you. Thanks Shail tvtime wrote: First, thanks to Peo for helping me truncate time AND lookup all in one formula. Second, I'd like help with an 'intersection' formula, if possible. For example, I have 100 rows and 100 columns, where row 1 is dates, and column A is times, and within are numbers. On a second sheet I have a column (A) with random dates and another (B) with times. What I need is a third column (C)on the second sheet that looks at the date and time, finds the intersection on the first sheet, and returns the number found there. Thanks in advance for any advice. Kiley -- tvtime ------------------------------------------------------------------------ tvtime's Profile: http://www.excelforum.com/member.php...o&userid=36676 View this thread: http://www.excelforum.com/showthread...hreadid=564172 |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection point return data
Sorry it must be $A$1
=INDEX(WTVF!$A$1:$EX$97,MATCH(E3,WTVF!$B$1:$EX$1,0 ),MATCH(G3,WTVF!$A$2:$A$9**7,0)) Shail shail wrote: Hi tvtime I saw all your messages posted. I guess you have typed the function incorrect =INDEX(WTVF!$B$2:$EX$97,MATCH(E3,WTVF!$B$1:$EX$1,0 ),MATCH(G3,WTVF!$A$2:$A$9*7,0)) If you see you need to change it this way =INDEX(WTVF!$A$2:$EX$97,MATCH(E3,WTVF!$B$1:$EX$1,0 ),MATCH(G3,WTVF!$A$2:$A$9*7,0)) Try this one. I wish this works for you. Thanks Shail tvtime wrote: First, thanks to Peo for helping me truncate time AND lookup all in one formula. Second, I'd like help with an 'intersection' formula, if possible. For example, I have 100 rows and 100 columns, where row 1 is dates, and column A is times, and within are numbers. On a second sheet I have a column (A) with random dates and another (B) with times. What I need is a third column (C)on the second sheet that looks at the date and time, finds the intersection on the first sheet, and returns the number found there. Thanks in advance for any advice. Kiley -- tvtime ------------------------------------------------------------------------ tvtime's Profile: http://www.excelforum.com/member.php...o&userid=36676 View this thread: http://www.excelforum.com/showthread...hreadid=564172 |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection point return data
Bif, got the file, and it looks great! Thanks so much for your work on this... I'm going to be incorporating the code into a larger macro to automate everything, and I'll let you know how the finished product turns out.. and if I hit any more walls. At any rate, you've been very gracious with your time. Thank you, Kiley -- tvtime ------------------------------------------------------------------------ tvtime's Profile: http://www.excelforum.com/member.php...o&userid=36676 View this thread: http://www.excelforum.com/showthread...hreadid=564172 |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection point return data
You're welcome. Thanks for the feedback!
Biff "tvtime" wrote in message ... Bif, got the file, and it looks great! Thanks so much for your work on this... I'm going to be incorporating the code into a larger macro to automate everything, and I'll let you know how the finished product turns out.. and if I hit any more walls. At any rate, you've been very gracious with your time. Thank you, Kiley -- tvtime ------------------------------------------------------------------------ tvtime's Profile: http://www.excelforum.com/member.php...o&userid=36676 View this thread: http://www.excelforum.com/showthread...hreadid=564172 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
Vlookup to Return a Range of Data | Excel Discussion (Misc queries) | |||
I need more general XY point to point plotting than XY scatter in | Charts and Charting in Excel | |||
Return result only if data complies. | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) |