How To Recalculate with Drop Down Menu

[novice31]

I want to make a drop down menu whereby each choice in the drop down would re-calculate my workbook.

Let me explain:-

I want to make a drop down menu in sheet 1 with 3 choices.
The 3 choices come from sheet 2 (row 1, column A, B, C).
I have data in sheet 2 in rows 2 through to 6 in columns A, B, C.
I have sheet 3 that currently uses the data from sheet 2 row 2 through to 6 for column A.

I want to be able to use the drop down menu in sheet 1 to choose either column A, B, or C in sheet 2 and then re-calculate the data in sheet 3 accordling.

How can I do that?

Really need your help. A million thanks.

I have the drop down menu made now.... but how can i re-calculate the values in sheet 3 after choosing a choice in the drop down?

[Palmetto]

I want to make a drop down menu in sheet 1 with 3 choices.
The 3 choices come from sheet 2 (row 1, column A, B, C).

Create a named range for these cells and use this named range as the Source for a data validation list in the cell on sheet1.

I want to be able to use the drop down menu in sheet 1 to choose either column A, B, or C in sheet 2 and then re-calculate the data in sheet 3 accordling.

Reference the cell on sheet1 in your formulas.

[royUK]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[novice31]

thanks for that,

but i still don't know how to change the formula in sheet 3 to allow the drop down menu (in sheet 1) to choose either column A, B or C in sheet 2 for the values to be used in sheet 3 calculations.

[Palmetto]

See attached.

Sheet1!A1 = drop down cell = Data Validation with list option
Sheet2!A1:C1 = list items for drop down
Sheet3!B1 = A1+Sheet1!A1

a named range was created for the item lits on sheet2 and given a name of "MyList", which is used as the Source for the Data Validation on sheet1.

It would be best to post a sample workbook so that we don't have to work blind as to your structure/ data, etc.

[novice31]

Palmetto,

Thanks for the help and suggestion. I have made up a sample excel sheet with what I am working with. hopefully attached.

I do not know how to change the calculations in sheet 3 depending on the drop down menu.

The excel sheet is quite straight forward, but I just don't know how to do what i need.

Thanks

[JBeaucaire]

On Sheet1:
Cell A1 has been named "Choice"

On Sheet2:
1 - each of the colored ranges has been given names that MATCH the options in the drop box (minus the spaces)
2- B2:B6 has been named Option1
3- C2:C6 has been named Option2
4- D2:D6 has been named Option3
5- A2:A6 has been named Drinks

On Sheet3:
The formula in C3 now uses an INDIRECT() reference to the cell "Choice" to slip the value into an INDEX() formula.
The SUBSTITUTE() function strips out the space so the result matches one of the named ranges on Sheet2.

After that, it's essentially a INDEX/MATCH

=$A3*INDEX(INDIRECT(SUBSTITUTE(Choice," ","")),MATCH(C$2,Drinks,0))
…becomes
=$A3*INDEX(Option3,MATCH(C$2,Drinks,0))

[Palmetto]

In future post please upload a sample workbook fromthe start. You'll get an accurate answer more quickly and no time will be expended guessing a solution.

Now that you've posted your workbook, I see what you want.

Take a look at the attached.

Several named ranges have been created and this formula returns a value from the selected option list.
In sheet-3, cells C3:G7

=$A3*OFFSET(INDIRECT(Sheet1!$A$2),1,0,1,1)

Brief explanation:

Each of the three cells on sheet-2 have been named. Click the cell and look in the name box (left of formula bar).

The above formula looks at the value in Sheet1!A2 and the INDIRECT function forces Excel to "interpret" the value as a range reference.

This named range, say Option_1 which is really sheet2!B1, becomes the "anchor point" from which to OFFSET and return the correct value from the correct option list.

Be sure to cycle through your drop down and carefully check all values in the table to ensure I've correctly understood your requirements.

[novice31]

Many thanks for your help.

I need a bit more advice please - i hope you can understand this:

Is there anyway that I can show the different data for all three choices from the drop down menu in a table? But at the same time I also still need to be able show a lot detail of one drop down in another table on the same sheet.

See sheet 1 in the attached example file - this will make more sense.

Again, thank you for all those that helped.

[JBeaucaire]

...can [we] show the different data for all three choices from the drop down menu in a table? But at the same time I also still need to be able show a lot detail of one drop down in another table on the same sheet.

See sheet 1 in the attached example file - this will make more sense.

Not unless you toss out the whole "OPTION" portion of the prices in Sheet3 and just create a separate table for all 3 options. You need the data to EXIST in Sheet3 if you want tit to appear on Sheet1.

So, the whole INDIRECT() thing can go away from the prices in the Sheet3 table, just create 3 complete tables.