software

I've made two example sheets to show my problem.

SHEET A
img71.imageshack.us/img71/9950/example010xx.jpg

SHEET B
img210.imageshack.us/img210/5...ample024ce.jpg

When i choose a number from 1-3 and type it in the red cell, i'd like
the data from the correct table to be linked too the table in Sheet B.
I can't figure out how to make Excel register the number in the red
cell and then compare it to the number on the tables.

Normally i'd use a simple VLookup, but obviousally that only looks at 1
table, i'd like to be able to have as many tables as i can. Any ideas on
how to get the above problem to work?--
PhilK
------------------------------------------------------------------------
PhilK's Profile: www.excelforum.com/member.php...oamp;userid=34665
View this thread: www.excelforum.com/showthread...hreadid=544338
Are there only 5 entries in each table (as in the example) you require
or could it be of a variable length, this will make a big difference to
the formula If it is fixed at 5

Something like
=OFFSET(Sheet2!C1,MATCH($A$1,Sheet2!$C$1:$C$20,0)-1,0)
If your data is held in sheet2, the formula can be copied down and
accross for all the values column d, e,f and down 5 rows

Obviously the range c1:c20 would need extending to include other
tables

Regards

Dav--
Dav
------------------------------------------------------------------------
Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
View this thread: www.excelforum.com/showthread...hreadid=544338Change your layout to copy C1 down to C5 et. then on the second sheet in A1
put

=Sheet1!$A$1

In B1 put 1, b2 put 2 etc.

In C1

=INDEX(Sheet1!E$1:E$100,MATCH(1,(Sheet1!$C$1:$C$10 0=$A$1)*(Sheet1!$D$1:$D$10
0=$B1),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter, and copy down and over to column D

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;PhilKquot; gt; wrote in
message ...
gt;
gt; I've made two example sheets to show my problem.
gt;
gt; SHEET A
gt; img71.imageshack.us/img71/9950/example010xx.jpg
gt;
gt; SHEET B
gt; img210.imageshack.us/img210/5...ample024ce.jpg
gt;
gt; When i choose a number from 1-3 and type it in the red cell, i'd like
gt; the data from the correct table to be linked too the table in Sheet B.
gt; I can't figure out how to make Excel register the number in the red
gt; cell and then compare it to the number on the tables.
gt;
gt; Normally i'd use a simple VLookup, but obviousally that only looks at 1
gt; table, i'd like to be able to have as many tables as i can. Any ideas on
gt; how to get the above problem to work?
gt;
gt;
gt; --
gt; PhilK
gt; ------------------------------------------------------------------------
gt; PhilK's Profile:
www.excelforum.com/member.php...oamp;userid=34665
gt; View this thread: www.excelforum.com/showthread...hreadid=544338
gt;

Morning,

Dav: Your forumla works perfectly cheers. Took me a few minutes to get
it working but works just fine. Thanks alot.

Bob: I tried your formula first, just as a challenge to try and
understand it. I didn't actually get it working, but i'm going to keep
going and see if i can get it working. Always good to know different
techniques, so thank you for posting.--
PhilK
------------------------------------------------------------------------
PhilK's Profile: www.excelforum.com/member.php...oamp;userid=34665
View this thread: www.excelforum.com/showthread...hreadid=544338