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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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