#1   Report Post  
Alec Kolundzic
 
Posts: n/a
Default Look Up Function

Help

On a sheet called routing, I have a number of cables, a1-a7, routed on
traywork, T1-T6. This gives me the routing for each cable. But on another
sheet called population, I need to see what each tray contains.

See following:

Routing
a1 T1 T2 T3 T4 T5 T6
a2 T1 T3 T4 T5 T6
a3 T1 T3 T4 T5 T6
a4 T1 T5 T6
a5 T1 T2 T3 T4 T5 T6
a6 T1 T2 T5 T6
a7 T1 T5 T6


Population
T1 T2 T3 T4 T5 T6
a1 a1 a1 a1 a1 a1 a1
a2 a2 a2 a2 a2 a2
a3 a3 a3 a3 a3 a3
a4 a4 a4 a4
a5 a5 a5 a5 a5 a5 a5
a6 a6 a6 a6 a6
a7 a7 a7 a7

I've tried Look Up functions with little success, any ideas.

Thanks
Alec Kolundzic
  #2   Report Post  
Tom Hayakawa
 
Posts: n/a
Default

Howdy,

Let's assume your Routing table resides in cells A1 to G7.

Also assume your Population table resides in cells A9 to G16.

Try this formula in cell B10: =IF(COUNTIF($A1:$G1,B$9)0,$A10,"")

Replicate it across your Population table (maintaining the proper cell
ranges, of course), and it should give you what you want. Of course, this
formula assumes a cable will only ever be in one tray a maximum of one time.
Also, you could make the cable runs Named Ranges and simplify the formula
somewhat.

Good luck,

Tom Hayakawa

"Alec Kolundzic" wrote:

Help

On a sheet called routing, I have a number of cables, a1-a7, routed on
traywork, T1-T6. This gives me the routing for each cable. But on another
sheet called population, I need to see what each tray contains.

See following:

Routing
a1 T1 T2 T3 T4 T5 T6
a2 T1 T3 T4 T5 T6
a3 T1 T3 T4 T5 T6
a4 T1 T5 T6
a5 T1 T2 T3 T4 T5 T6
a6 T1 T2 T5 T6
a7 T1 T5 T6


Population
T1 T2 T3 T4 T5 T6
a1 a1 a1 a1 a1 a1 a1
a2 a2 a2 a2 a2 a2
a3 a3 a3 a3 a3 a3
a4 a4 a4 a4
a5 a5 a5 a5 a5 a5 a5
a6 a6 a6 a6 a6
a7 a7 a7 a7

I've tried Look Up functions with little success, any ideas.

Thanks
Alec Kolundzic

  #3   Report Post  
Alec Kolundzic
 
Posts: n/a
Default

Thanks Tom,
This works just fine.

"Tom Hayakawa" wrote:

Howdy,

Let's assume your Routing table resides in cells A1 to G7.

Also assume your Population table resides in cells A9 to G16.

Try this formula in cell B10: =IF(COUNTIF($A1:$G1,B$9)0,$A10,"")

Replicate it across your Population table (maintaining the proper cell
ranges, of course), and it should give you what you want. Of course, this
formula assumes a cable will only ever be in one tray a maximum of one time.
Also, you could make the cable runs Named Ranges and simplify the formula
somewhat.

Good luck,

Tom Hayakawa

"Alec Kolundzic" wrote:

Help

On a sheet called routing, I have a number of cables, a1-a7, routed on
traywork, T1-T6. This gives me the routing for each cable. But on another
sheet called population, I need to see what each tray contains.

See following:

Routing
a1 T1 T2 T3 T4 T5 T6
a2 T1 T3 T4 T5 T6
a3 T1 T3 T4 T5 T6
a4 T1 T5 T6
a5 T1 T2 T3 T4 T5 T6
a6 T1 T2 T5 T6
a7 T1 T5 T6


Population
T1 T2 T3 T4 T5 T6
a1 a1 a1 a1 a1 a1 a1
a2 a2 a2 a2 a2 a2
a3 a3 a3 a3 a3 a3
a4 a4 a4 a4
a5 a5 a5 a5 a5 a5 a5
a6 a6 a6 a6 a6
a7 a7 a7 a7

I've tried Look Up functions with little success, any ideas.

Thanks
Alec Kolundzic

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
Averaging function Sarah Excel Discussion (Misc queries) 0 January 18th 05 05:09 PM
Function in XL or in VBA for XL that pulls numeric digits from a t Nate Oliver Excel Discussion (Misc queries) 0 December 14th 04 05:57 PM
Excel function help facilities RPS Excel Discussion (Misc queries) 1 December 8th 04 03:36 AM
I cant use englisch function names in a swedich version of excel PE Excel Discussion (Misc queries) 2 December 7th 04 02:00 AM
SUMIF(AND) FUNCTION Saariko Excel Worksheet Functions 9 October 28th 04 11:52 AM


All times are GMT +1. The time now is 06:38 AM.

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"