Indexing formula needed for two pages

Hi guys, I hope everyone is doing well. I'm looking for a little help on this project. Let me explain:

I have a workbook with 2 sheets.

Sheet 1 = A breakdown of different quality levels with a list of components. Those levels are "Minimum", "Recommended", "Premier", and "Elite".

Sheet 2 = A list of different computer component choices. In this example, Sheet 2 is a list of different computer cases to choose from.

What I want to be able to do is choose an item in Sheet 2 and have it automatically entered in the corresponding cell in Sheet 1. Here is an example:

In Sheet 2 i have a list of different computer cases. In column D I want to enter a letter or letters that corresponds with the name of the option (Minimum, Recommended, Premier, and Elite), those letters would be M,R,P,E. If for example i choose cell D3 which is the Corsair 550d and place the letter P in that cell i want cell F3 in Sheet 1 to display the combination of cells B3+C3 from Sheet 2 so it reads "Corsair 550d in F3. I also want the price data of cell E3 from Sheet 2 to automatically be entered into G3 in Sheet 1. I would also need the option to enter multiple letters in any cell in column d of Sheet 2. Let's say in Sheet 2 cell D3 I enter the letters M,R,P,E I'd like the text from B3 and C3 of sheet 2 to be entered into Sheet 1 cells B3, F3, J3, N3.

I hope that makes sense and I hope it's possible. Thanks in advanced to anyone who takes the time to assist me, i really appreciate it!!

Hi
Use in D3 (helper valid for copy to H, L and P) and represent line number in Sheet2!

Formula:

Please Login or Register  to view this content.

Use in B3 and C3

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

See the file Excel help (1).xlsx

thank you! I'm going to check it over in a few and report back.

the only problem i see is that there is always an option selected by default in Sheet 1!. Even if i haven't imputed a letter in Sheet 2! there always remains an item in Sheet 1! cells B3, F3, J3, and N3

ARRAY formulas are used
For B, F, J & N

Please Login or Register  to view this content.

For C, G, K & O

Please Login or Register  to view this content.

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets.

Originally Posted by kvsrinivasamurthy

ARRAY formulas are used
For B, F, J & N

Please Login or Register  to view this content.

For C, G, K & O

Please Login or Register  to view this content.

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets.

now what happens when i want to have more than 11 entries in sheet 2? Say i want to have entries down to row 32. What then?

Change 13 to 32 n the formula.
Since it is a ARRAY formula any editing should be confirmed with
Ctrl+Shift+Enter keys
not with simply Enter key

Change the formula as

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Originally Posted by JeteMc

Don't want to confuse things, however I want to correct for shortcomings in post #7. The ranges of all three formulas would need to be changed such that the formulas for cells B3, C3 and D3 respectively would read:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Let me know if you have any questions.

I've tried to duplicate this formula for other sheets in my workbook and I'm having the same problem. I'm getting #N/A as an outcome in some cells. Can you tell me where I'm going wrong? The workbook is attached.

Originally Posted by kvsrinivasamurthy

Change the formula as

Please Login or Register  to view this content.

Please Login or Register  to view this content.

what happens when i want to add more entries? Say up to 25 entries instead of 11?

i can keep the other formulas for the previous sheet for "CASES"? Correct?

that formula doesn't work if I'm entering more than 1 letter in the Quality column, such as EPRM

Upload the file with such 25 entries showing the expected result.

Originally Posted by kvsrinivasamurthy

Upload the file with such 25 entries showing the expected result.

here it is

Originally Posted by JeteMc

I am going to suggest greatly simplifying the formula in E7 to read:

Please Login or Register  to view this content.

As evidenced in the attached file that will find the elite option for storage (main). The change will not find the other three options as they are all assigned to the same cell. I tested by leaving only one option at a time in Storage!C3 and the formula works as I would expect.
Here is a copy of the latest version of your file with the modified formula applied: Attachment 449221
Let me know if you have any questions.

that formula doesn't work if I'm entering more than 1 letter in the Quality column, such as EPRM

I've attached a workbook with my progress thus far. This project will have many different sheets separated by component, so I'm in essence taking the formulas and inserting them as i go. The problem i come into is when i may have more entries to choose from than a previous sheet of components such as:

Storage. There will be a main storage and an extra storage all on the same "storage" sheet. I may need to have 20 or 25 main storage entries the same for extra storage entries. When i go to add new lines or alter the formula i get error messages in the corresponding cells on the "options" sheet as shown in the workbook.

It will be easy to get formulas if these things are done.
Format of Tables in all the sheets should be alike. For Eg Like in Sheet "CASES"
The names of sheets should be exactly same as in A column of Sheet "OPTIONS".

It is not clear where are those 25 entries.

@JeteMC
so must i increase *ROW(F$1:F$24) to *ROW(F$1:F$25) every time i add a new row of entries? And decrease by 1 every time i remove a row?

I guess i am so confused because i don't know the basics of what the formula is saying

I think i'm beginning to understand how to manipulate this formula. In my overall project I've been able to handle the first 13 lines of the "options" sheet with your help, so thank you. Now when i reach the "power supply" sheet it begins to get a little more complicated. Let me explain.

In the "options" sheet cell B14 i want to continue with the formula you've presented such as seen in B13, but i need a little more description. For example I'd like it to read:

Gold Corsair RM750x Modular 650 WATT

Like the other cells, it has to index the "power supply" sheet. When i insert the letter "Y" in any of the cells Q3:S3 it has to stipulate one of three phrases (Modular, Semi modular, Non Modular). If you look at "Power supply" Cell M14 you'll see the formula i wrote to indicate this.

For the wattage if you refer to "power supply" cell N12 you'll see the formula i wrote for the wattage.

I'm struggling to find a way to combine it all, do you have any suggestions?

that formula works except for one problem. If i don't have a quality selected (E,P,R,M) the word "watt" still displays in B14. I would need B14 to remain completely empty until i choose a Quality such as E,P,R,M.

i was under the weather the last week, sorry for the delay. Things have worked very well so thank you very much. In the "Quality" column where i put the letters E,P,R,M, is there a way to have a Font color specific for each letter?

E = Purple
P = Blue
R = Green
M = Orange

For example if i enter the letter E in any cell in the Quality column of the page "CASES" the letter will be displayed in purple. The same for P,R,M and their respective colors.

Thank you, I'm improving! Is that the only way i can have each letter be a different color? One letter per cell? There's no way to combine them in one cell?

okay thank you for all your help!