Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
A formula enquiry; (screenshots included)
I've made two example sheets to show my problem. SHEET A http://img71.imageshack.us/img71/9950/example010xx.jpg SHEET B http://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: http://www.excelforum.com/member.php...o&userid=34665 View this thread: http://www.excelforum.com/showthread...hreadid=544338 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
A formula enquiry; (screenshots included)
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: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=544338 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
A formula enquiry; (screenshots included)
Change 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) "PhilK" wrote in message ... I've made two example sheets to show my problem. SHEET A http://img71.imageshack.us/img71/9950/example010xx.jpg SHEET B http://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: http://www.excelforum.com/member.php...o&userid=34665 View this thread: http://www.excelforum.com/showthread...hreadid=544338 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
A formula enquiry; (screenshots included)
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: http://www.excelforum.com/member.php...o&userid=34665 View this thread: http://www.excelforum.com/showthread...hreadid=544338 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
assign formula to another cell | Excel Worksheet Functions | |||
How would I write a max formula that included color formats? | Excel Discussion (Misc queries) | |||
Match then lookup | Excel Worksheet Functions | |||
adding row to forumla | Excel Discussion (Misc queries) | |||
Creating a check mark box | Setting up and Configuration of Excel |