Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dav Dav is offline
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 195
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 195
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Inputting data to one worksheet for it effect another daedalus1 Excel Discussion (Misc queries) 1 June 25th 06 04:39 PM
Vlookup to Return a Range of Data Rob Excel Discussion (Misc queries) 13 June 1st 06 04:02 AM
I need more general XY point to point plotting than XY scatter in spazminator Charts and Charting in Excel 12 December 19th 05 06:00 PM
Return result only if data complies. John Moore Excel Discussion (Misc queries) 3 November 8th 05 03:28 PM
Sort pages? David Excel Discussion (Misc queries) 15 May 13th 05 11:33 PM


All times are GMT +1. The time now is 04:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"