Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple formula parts and utilizing a table
First, I'm sorry but consider me a very basic user. I'm trying to figure out
how to do a formula. Column A Column B Column C (has multiple options via dropdown) (has multiple options via dropdown) ?? A user selects an option in A and an option in B. In another sheet (same workbook) I have a table of data on 5 columns/6 rows. Depending on their selections in A & B, I need to have column C populated with data from a particular cell in that table. Is this even possible? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple formula parts and utilizing a table
Hi Marilyn,
Sounds like a job for VLOOKUP Read up on this in Help (and any books you might have) Come back with more questions - tell us (A) what version of Excel, (b) a bit about the data in the table, (c) example of the 'rules' like "if A = "cat", and B = "dog" what is to be returned form the table. best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Marilyn" wrote in message ... First, I'm sorry but consider me a very basic user. I'm trying to figure out how to do a formula. Column A Column B Column C (has multiple options via dropdown) (has multiple options via dropdown) ?? A user selects an option in A and an option in B. In another sheet (same workbook) I have a table of data on 5 columns/6 rows. Depending on their selections in A & B, I need to have column C populated with data from a particular cell in that table. Is this even possible? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple formula parts and utilizing a table
On Tue, 26 Jan 2010 11:55:03 -0800, Marilyn
wrote: First, I'm sorry but consider me a very basic user. I'm trying to figure out how to do a formula. Column A Column B Column C (has multiple options via dropdown) (has multiple options via dropdown) ?? A user selects an option in A and an option in B. In another sheet (same workbook) I have a table of data on 5 columns/6 rows. Depending on their selections in A & B, I need to have column C populated with data from a particular cell in that table. Is this even possible? If your "Column A" choosen value ends up in cell A1 and you "Column B" choosen value ends up in cell B1, and the table of data in the other sheet, which has the name Sheet2, has the A and B options as "axises" in the table, like this empty Bopt1 Bopt2 Bopt3 Bopt4 Bopt5 Aopt1 a b c d e Aopt2 f g h i j Aopt3 and so on Aopt4 Aopt5 Aopt6 and you want the value h returned if the Column A choosen option is Aopt2 and the Column B choosen option is Bopt3, then try the following formula in the cell in Column C where you want the result to appear: =INDEX(Sheet2!B2:F7,MATCH(A1,Sheet2!A2:A7,0),MATCH (B1,Sheet2!B1:F1,0)) Hope this helps / Lars-Åke |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple formula parts and utilizing a table
Yes. I'm guessing A controls the row & B controls the column you want to look
at. Example: B C 2 x y 3 z aa My small table has row headers of 2 and 3, column headers of B and C. The blank spot in top-left corner is cell A1. Let's say my drop down in column A I choose "2" and in column B dropdown I choose "C". My formula is: =INDEX('Sheet2'!$B$2:$D$3,MATCH(A2,'Sheet2'!$A$2:$ A$3,0),MATCH(B2,'Sheet2'!$B$1:$C$1,0)) The spits out the result "y". -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Marilyn" wrote: First, I'm sorry but consider me a very basic user. I'm trying to figure out how to do a formula. Column A Column B Column C (has multiple options via dropdown) (has multiple options via dropdown) ?? A user selects an option in A and an option in B. In another sheet (same workbook) I have a table of data on 5 columns/6 rows. Depending on their selections in A & B, I need to have column C populated with data from a particular cell in that table. Is this even possible? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple formula parts and utilizing a table
I'm using Excel 2003.
Sorry, I'm not explaining this well. I'll try to give better info: Let's say Column A asks you to pick 1 of 5 gems: Agate, Turquoise, Garnet, Diamond, Pearl Let's say Column B asks you to pick 1 of 6 Countries: US, Can, UK, China, Japan, Germany The other worksheet has a table with data (its a number in each cell) representing all the possible permutations of those choices. However (and this might present a problem) this row/column headers of this table are not gems and countries. Is that a problem? Its just a stand-alone table. So, on Column C, can a formula recognize which gem you picked in Column A, which country you picked in Column B then go to the table and bring in the number from a table and put it into Column C. (Eg if I picked Agate and China, I want Column C to say 1394). Thanks! "Marilyn" wrote: First, I'm sorry but consider me a very basic user. I'm trying to figure out how to do a formula. Column A Column B Column C (has multiple options via dropdown) (has multiple options via dropdown) ?? A user selects an option in A and an option in B. In another sheet (same workbook) I have a table of data on 5 columns/6 rows. Depending on their selections in A & B, I need to have column C populated with data from a particular cell in that table. Is this even possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Utilizing a RANDBETWEEN() formula within a LOOKUP formula | Excel Discussion (Misc queries) | |||
protect parts of a pivot table | Excel Worksheet Functions | |||
Utilizing a Cell even with a Formula located inside it | Excel Worksheet Functions | |||
Divide a Cell into Multiple Parts? | Excel Discussion (Misc queries) | |||
Divide a Cell into Multiple Parts? | New Users to Excel |