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

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$16,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

Thanks Dav, I can't wait to try it...

I'll let you know how it goes.

Thanks again

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!

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" <[email protected]> wrote inmessage news:[email protected]...>> 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>

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!

Can you post a sample file?

I see you're posting from Excelforum. They allow uploads. Just zip the file.

Biff

"tvtime" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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!

I might have actually done some of the lifting on this... I might be able to use DATEVALUE...

That's what I get for thinking... it doesn't work with DATEVALUE... still would like help...

Thanks!

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

Try this:

Select an empty unused cell (one that hasn't been preformatted or ever used)
Copy that cell by going to Edit>Copy
Now select the cells that contain the dates. I guess that would be B1:EX1
Goto Edit>Paste Special>Add>OK

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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.

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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!

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

I've sent you an email.

and I've probably met my 'thank you' quota, but you certainly deserve it...

Kiley

Hi tvtime

I saw all your messages posted.

I guess you have typed the function incorrect

=3DINDEX(WTVF!$B$2:$EX$97,MATCH(E3,WTVF!$B$1:$EX$1,0),MATCH(G3,WTVF!$A$2:$A=
$9=AD7,0))


If you see you need to change it this way

=3DINDEX(WTVF!$A$2:$EX$97,MATCH(E3,WTVF!$B$1:$EX$1,0),MATCH(G3,WTVF!$A$2:$A=
$9=AD7,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...n=3Dgetinfo&u=
serid=3D36676
> View this thread: http://www.excelforum.com/showthread...hreadid=3D564=
172

Sorry it must be $A$1

=3DINDEX(WTVF!$A$1:$EX$97,MATCH(E3,WTVF!$B$1:$EX$1,0),MATCH(G3,WTVF!$A$2:$A=
$9=AD=AD7,0))

Shail


shail wrote:
> Hi tvtime
>
> I saw all your messages posted.
>
> I guess you have typed the function incorrect
>
> =3DINDEX(WTVF!$B$2:$EX$97,MATCH(E3,WTVF!$B$1:$EX$1,0),MATCH(G3,WTVF!$A$2:=
$A$9=AD7,0))
>
>
> If you see you need to change it this way
>
> =3DINDEX(WTVF!$A$2:$EX$97,MATCH(E3,WTVF!$B$1:$EX$1,0),MATCH(G3,WTVF!$A$2:=
$A$9=AD7,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?action=3Dgetinfo=
&userid=3D36676
> > View this thread: http://www.excelforum.com/showthread.php?threadid=3D5=
64172

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

You're welcome. Thanks for the feedback!

Biff

"tvtime" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>