Ne Question Addition to previous thread

[RachelMads02]

hello guys, i have an older thread link below with 2 questions.

http://www.excelforum.com/excel-form...o-pages-3.html

I have a workbook based on computer components. There is a sheet titled "options" which breaks down 4 different options with lists of components. Then there are individual sheets titled by their component. When going through each component sheet i can place the letters "E,P,R,M" in any cell (no letter more than once) and it will take that corresponding item and place it in the option sheet based on the letter choice. E= Elite, P= Premier, R = Recommend, and M = Minimum. It works off the formula in columns G,N,U,AB.

Question 1
Now i would like to create a new sheet titled "Upgrades". What i want to achieve is basically the same theory as in the options sheet. This time i want to place the letter U in a separate column in each component sheet and have that specific component appear on the "upgrades sheet".

Example: In the sheet titled "Cases" I want column E to represent upgrades. There are 25 different options in the cases sheet. I'd like to pick up to say, 3-5 as upgrades. I place the letter "U" next to any of the items in column E and that name + price + MU + customer price + rebate appear on the "UPGRADE" sheet. I may have as many as 5 different upgrade choices all with the letter "U". Is that possible?

Question 2
After the "UPGRADES" sheet is established i want to create a third sheet. Call it "sheet 3" for now. Here i will have a page in which a customer can pick and choose upgrades from a drop down list, but some of those upgrades will be dependent on previous choices.

Example: Let's say i give a customer the option to upgrade their CPU of their computer with 3 choices; choice 1, choice 2, choice 3. I also provide the customer the option to increase the RAM of their computer with choice A, and choice B. If and only If the customer chooses to upgrade their CPU will they be able to upgrade the RAM and to further complicate it (sorry!!) only Choice 2 and Choice 3 of the CPU will allow the customer to upgrade the RAM to choice A or Choice B.

CPU Choice 1 - No RAM choices
CPU Choice 2 - RAM Choice A
CPU Choice 3 - RAM Choice A, Ram Choice B

I hope that isn't too confusing. JETE has experience with my threads, hope to hear from you!! If anyone else has a suggestion or answer i'd love to hear it, please refer to the previous thread post #34 for the workbook. Thanks to anyone who helps!!

[JeteMc]

It would be helpful to see a mock up (manual) of what you are proposing for the 'Upgrades' sheet and 'Sheet3' added to the workbook that is attached to post #34 of the linked thread.

[RachelMads02]

okay here it is.

i edited sheets titled "cases", "Cpu", and "RAM". I inserted a column in each titled "upgrades". Cases = column E, CPU = column G, RAM = column F. Each of those pages will have a list of 25 choices.

If you look in the "CPU" sheet you will see that cell F3 has the letters EPRM. When any combination of those letters are inserted the corresponding cells will display in the "OPTIONS" sheet line 4.

Now when i come across an item that i feel should be considered as an upgrade i want to place the letter U in the upgrade column of the corresponding option. Example:

in sheet "CPU" there are items in lines 7, 11, 14. I want those items to be considered upgrades so i place the letter U in cells G7, G11, & G14. From there look at the sheet titled "UPGRADES" (to the left of the "OPTIONS" sheet). There you will see three sections. one for cases, cpu, and ram. We will only be discussing cases and ram at the moment.

When i place the letter U in cells G7, G11, and G14 of the sheet titled CPU i'd like the corresponding cells to display in the cpu section of the "UPGRADES" sheet ( i inserted the cell identification) in the UPGRADES sheet).

After that is solved I'll ask the 2nd part of the question in regards to drop down lists.

[Pete_UK]

In the attached file I put this formula in H3 of the CPU sheet:

=IF(G3="U",MAX(H$2:H2)+1,"-")

then copied it down. It gives a unique sequential number to those records marked with U in column G.

Then in cell G3 of the Upgrades sheet I used this formula:

=IFERROR(INDEX(CPU!B:B,MATCH(ROWS($1:1),CPU!$H:$H,0)),"")

Ordinarily, this is copied across to pick up the values from successive columns, as the B:B (shown in red) will change to the next successive column. In your case, however, the columns are not in sequence, so you can see that I have manually changed the B:B as appropriate and changed the formatting in copying this formula across to column M. Those formulae can then be copied down (to row 10 in this case).

Perhaps you can adapt this technique to the other sheets.

Hope this helps.

Pete

[RachelMads02]

okay that seems to work, thank you!

Now onto the 2nd question. In SHEET 3 there are two items. CPU, and RAM.

I want a drop down list in cell B2 to contain the information from cells I3:J6 in UPGRADES. Example:

AAA 6 Core 4.0GHZ
BBB 4 Core 4.0GHZ
CCC 8 Core 3.3GHZ

In cell B3 of SHEET 3 i will have a drop down list of both RAM options in "UPGRADES" sheet. But here's the catch. Only certain CPU upgrades will allow RAM upgrades. Basically the RAM drop down list is dependent on the choice from the CPU drop down list.

CPU choice AAA = allows either RAM options in cell B3 to be chosen.
CPU choice CCC = allows either RAM options in cell B3 to be chosen.
CPU choice BBB = does NOT allow either RAM options in cell B3 to be chosen. - remain blank

Does that make sense?

[JeteMc]

This solution uses the procedure found in this article to apply Named Ranges to helper tables on the Upgrades sheet and create a second dropbox that will either show the upgrade choices if the chosen CPU is AAA or CCC or will show no choices if the CPU is BBB.
Let me know if you have any questions.

[RachelMads02]

i'm kind of confused on the article you presented. i read that specific article before i made the post. more specifically i don't understand the formula how the formula imputed in the drop down lists give the result i'm looking for. i'm trying to understand so i can duplicate it with other lists going forward. any advice?

[JeteMc]

You might try downloading the example "dependent-drop-down-lists.xls" from the right side bar of the article. You also may want to look at the 'Name Manager' (Formulas Tab) on the file attached to post #6. The formula =INDIRECT(LEFT(B2,3)), in the data validation for Sheet3!B3 finds the three letters that are on the left side of B2 and selects the correct Named Range from the 'Upgrades' sheet. Since you wanted the AAA and CCC choices to both show the two upgrades they both point to the same 'Named Range', as seen in the 'Name Manager'. I hope that that makes some sense.
Let me know if you have any questions.

[RachelMads02]

Okay I'll take a look through those. Thank you!! One last question though.

In this workbook i will have Vendor ID#s for each sheet, such as Column N in cases. I'd like to attach a hyperlink to each Vendor ID#. Now when i choose a component in each sheet with the letter EPRM in the quality column I'd like the hyperlink in column N to associate with the component in the Option's sheet without making another column in the "Options" sheet.

Example:

CASES sheet > Cell N3 > will have an ID# with a hyperlink attached to it (unless there's a better solution). When i place the letter E in cell C3 i will get a description of the choice in "OPTIONS" sheet cell B3. I'd like the hyperlink attached to cell N3 in "CASES" to attach to cell B3 in "OPTIONS". Is that possible?

[JeteMc]

Sounds doable, however that is not something I am familiar with and while I might be able to come up with something that works, I imagine there are folks here who know how to do this so that it works more efficiently than something I might cobble together. I would advise you to start a new thread in the 'Excel General' forum attaching the most up to date version of the workbook and a copy of the description that you made in post #9. The title of the thread might be "Linking a hyperlink to a lookup", so that, down the road, people with a similar question will be able to find the solution quickly.

[RachelMads02]

sounds good, thank you Jete!!