Sheet adress(name) in cell as referance

[912Geir]

Hi

Have a problem that I can not find a solution for.

Do some of you kow how to make a drop down list with names of the sheets in my workbook?
I will in the next collum use the adress in an formula to look up values in that sheet.

[dflak]

Put this code in a module

Please Login or Register  to view this content.

[912Geir]

Thanks for input.
I have never used coding in my Excel work before.
Is it not possible to do this without it?
I have made an simple example of what I am trying do do.
Front sheet will be like an order sheet where it is possible to choose different products in different sizes and get correct price.
When I choose product drop down i left colum it will give me an reference that I can use later with right colums. I plan to use formula Index and Match
to find the correct price for right product with matching size.
Maybe I am thinking wrong or it can be don in an more easy way. Happy for all input.
I know I could just put all product in the front page and make the world more easy. But I like to have an nice structure with one product in its own sheet.
Later I will also make different options to to apply for the products.

[Pete_UK]

You can use this formula in E3:

=IFERROR(INDEX(INDIRECT("'"&B3&"'!c:i"),MATCH(C3,INDIRECT("'"&B3&"'!B:B")),MATCH(D3,INDIRECT("'"&B3&"'!C3:I3"))),"")

You may need to change the commas ( , ) in the formula to semicolons ( ; ) according to your regional settings, and you may need to translate the function names into your language.

Copy down as required.

Hope this helps.

Pete

[912Geir]

Thanks for formula suggestion.
I have never used this combination so I have to do a studie to understand its nature.
I have paste it into cell E3/E4/E5, and changed it to Norwegian but it will not give me any price data.
If I understand the formula correct, it checks name of product/sheet i colum A and check with/length in Colums B&C to match them in the correct sheet.

Regards
Geir

[Pete_UK]

I have put the formula in your file - it should be translated to Norwegian automatically when you open it.

Hope this helps.

Pete

[912Geir]

Works brilliant!
Thank for all help. Now I can start to develop my little order system.

Best Regards
Geir

[Pete_UK]

Glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[912Geir]

Been puzzeling more with my little product calculator today. I have tried to copy Pete's solution (even if I do not understand all parameters yet..) to another collum and find an amount number 2.
It seems to be something wrong with the lookup, but I can not find it.. :-(

[Pete_UK]

When you use MATCH without specifying the third (optional) parameter, like I did, then it is assumed to be TRUE or 1, which means that it will look for the match which is less than or equal to the item being sought. This means that the data table has to be sorted. The top part of your table in the sheet Classic_FA40 which contain numbers in column B is sorted, but the lower part with names in column B is not. In the attached file I have sorted rows 10 to 15 in that sheet, and the formula in G3 of the Frontpage sheet now gives the correct result.

Hope this helps.

Pete

[912Geir]

Thanks again Pete!

It is facinating how "easy" problems in Excel can be solved sometimes.. I will sort the lists from now :-)

Best Regards
Geir

[Pete_UK]

You're welcome - glad to help.

Pete