Multiple sheets

[tessa]

I have a 7 sheet spreadsheet.
Sheet 1 is where the formula will be going and it contains a dropdown menu with a list of product names and a dropdown menu with a list of product types. These names and types correspond to different name and types on sheets 2-6
On sheets 2-6 there is information (eg. name, percentage, code) that needs to go on sheet 1.
Sheet 7 is the information for the dropdown lists.
I need to work out how to achieve the following:
If, Product Name = ABC and Product Type = XYZ, the relevant information with regards to the Product Name and Type from sheets 2-6 automatically populates to Sheet 1.
Hope this makes sense.
Cheers

[Ken Puls]

Hi Tessa, and welcome to the forum.

Do you happen to have a sample of your data that you can attach and upload? It would be easier for us to help if we were working with data that resembled what you use, rather than try to mock something up that may not be correct.

You can attach a workbook to the thread by clicking the "Go Advanced" button, then look under the text window for the option to upload a file.

[Simon Lloyd]

This would probably be a nightmare worksheet if it was to house hundreds of volatile formula to maintain, a cleaner neater solution may be to use VBA, however as Ken says we do need a sample, providing a workbook will not only get you your answer quicker but will better illustrate your problem, usually when we can see your data (it can be dummy data but must be of the same type) and your structure it is far easier for us to give you a tailored, workable answer to your query

[tessa]

Here is the spreadsheet that I am trying to figure out. If there is any other way that this could be done please let me know.

Cheers
Tess

[NBVC]

You would need to name each table in each of those sheets (only the data portions). The names would need to be a combination of exactly what is in B2 and C3 (but without any spaces)...e.g:OnePathOneAnswerInvestmentPortfolioConservative

Also, it would be recommended to not use Merged cells. Instead just make single columns and widen as necessary to give the correct appearance.

Then you could simply use formula:

=IF(ISERR(INDEX(INDIRECT(SUBSTITUTE($B$2&$C$3," ","")),ROWS($A$1:$A1),COLUMNS($A$1:A$1))),"",INDEX(INDIRECT(SUBSTITUTE($B$2&$C$3," ","")),ROWS($A$1:$A1),COLUMNS($A$1:A$1)))

copied down and across the 3 columns.

If you have XL2007 or later:

=IFERROR(INDEX(INDIRECT(SUBSTITUTE($B$2&$C$3," ","")),ROWS($A$1:$A1),COLUMNS($A$1:A$1)),"")

[Simon Lloyd]

NBVC, welcome and nice solution, as you point out it would benefit from a layout change.

[NBVC]

Thanks Simon. Looks like a great place....

I see you are already a Super Mod... Well, well

[Simon Lloyd]

You know me, i get where bleach can't i now admin 5 sites and mod at 1!

[Ken Puls]

Hi NBVC, and welcome!