Intersection point return data

[tvtime]

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

 

[Dav]

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 ro
headings are in c7:c16 and are times

and in the second sheet the first column is time and the second is dat
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 valu
nearest above or below the formula needs to be ammended

Regards

Da

 

[tvtime]

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

I'll let you know how it goes.

Thanks agai

 

[tvtime]

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!

 

[Biff]

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 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>

 

[tvtime]

First, thanks Biff for the help, but it did not work. I've checked th
formats of all cells and tried it also as an array.

Any thoughts of other checks?

Thanks

 

[Biff]

Can you post a sample file?

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

Biff

 

[tvtime]

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]

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

 

[tvtime]

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

Thanks

 

[tvtime]

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

 

[Biff]

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]

I did try the paste special trick, and it did convert my dates/times t
numbers, but the cells in L that weren't working still didn't work. I
you have a minute to look at the file (from the link above), I'
appreciate your insight.

And I'm very grateful for your help, whether we find a resolution o
not.

Have a great day

 

[Biff]

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]

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!

 

[Biff]

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]

I've sent you an email.

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

Kile

 

[shail]

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

 

[shail]

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

 

[tvtime]

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