Hi I'm v v new to forums so please be kind!

I work for a Kitchen retail outfit
I've been working in excel to create a form for my salesman to use as an order form to eliminate errors on our ordering system

So far I have listed all the data (Item Code,Description,Cost Price,Sale Price,Retail Price,Offer Price) for every kitchen range that we sell, these are grouped into 5 separate price groups so I put then on their own sheet making 5 sheets named Group 1-5

I have then made a form that will use vlookup and return the Item code and a full description and all the different prices for that item (Cost,Retail,Sale,Offer)

I have inserted a dropdown box with all the ranges in it and would like this box to direct the vlookup formula to the correct Sheet for that range.

I've tried all sorts of different ways to get this and because I have more than 29 ranges I can't use the dropdown that generates a value to use as a lookup which is totally confusing me

Any help would be gratefully appreciated. I will post a sample of form so far and VLookup formula if needed

Please note I have no experience of VBA so I might need some extra help if thats the way to sort it

Kindest Regards
Andy

Howdy, and welcome to the board. Can you post a sample (not complete, but enough to show what you are attempting) and the result you expect? That will help direct to a solution. My guess is that you will be able to use an INDIRECT function to accomplish what you want.

Help!!!
How do I attach a mini version of the form to a post?

I think I've worked it out

Here is a mini version..lookups won't work as the data is in a different workbook,but the question is the same... How do I make the Range dropdown list set the lookup array for the pricing formula?

Okay, couple of questions. You say there are five Groups. Correct? Each Group will have its own worksheet? You want Cell G2 to be a drop down that lists the Groups? Then, that will refer to the worksheet Group1 or Group2 ... and on each worksheet, there will be data?

How does the Range List connect to the Item List?

Can you include a sample of the worksheet that has the data for lookup? It's still hard to see how you want this to work.

Hi again
Thanks Shades I really appreciate you persevering with me

I am attaching a full version of the sheet so far. I realise I could shorten some of the formula by naming lists but I would rather have a working version first and then tune it later

You are right with your assumption, I would like the Dropdown box to dictate which sheet the prices are returned from as the ranges are priced across 5 groups.

Regards (again)
Andy

Thanks, this is beginning to make a little sense. However, what is the difference between the values on the Data Sheet for Group 2 and the values on worksheet Group 2?

I put the ranges in to groups to try and make it easier for myself to work through the lookup formula. On the data page are all the prices for all the ranges one after the other in no particular order.

If it is more straightforward to use just the 'Data' sheet then I'm more than happy to work my formula again I just need a pointer as to how the dropdown will select the correct prices for the range that was selected.

Would it be more simple to not use the dropdown as the key to the pricing and to use a different path?

Okay, I think I have your solution.

I shortened all worksheet names to make references simpler. And with your Data Sheet set up, there is no need for the Group sheets unless you need them.

So, on the CustomerPriceList worksheet, in cell D6, put this formula in:

=INDEX(Data!$A$2:$DR$475,MATCH($C6,Data!$A$2:$A475 ,FALSE),2)

Then in E 6, put this formula in:

=INDEX(Data!$A$2:$DR$475,MATCH($C6,Data!$A$2:$A475 ,FALSE),MATCH($E$3&" "&E$5,Data!$A$2:$DR$2,FALSE))

and copy to the right.

Notice that this uses the drop down of the City.

Basically INDEX/MATCH allows you to do two way lookups (row and column). I concatenated the City name and the headers (with a space between) so that it could be used as the lookup for the proper column on Data.

BTW, you have Data laid out in a very usable form and easily read form. Well done.

Thank you for the compliment
I read up the basics for making a start and then I got in too deep.

I'm grateful for your help and if its appropriate heres a beer on me


Sheet works brilliantly

The Sales guy won't know what hit him tomorrow when I implement it!

Many thanks
Andy

Great, and glad to help. Let them drool over your work. It is well done.

And again, 75% of using data starts with good data structure. Once I figured out your structure, the rest was relatively easy.

Note also, that I did not include any error trapping in the formulas. But since you have done that with the VLOOKUP formulas, I assumed you could do it with this set of formulas as well.

You have moved beyond the "Novice Excel Dabbler"!

I'll 'Dabble' a little more and put the error trapping back.....Don't go away!!!!

I'm back again

I've been tweaking a little and now got a sheet set up for the Sales guy to use but I would like to add a little extra to the tail of the sheet which will allow him to input the quantity of accessories instead of an individual line for each item

As the formula is now far in advance of where I'm at I would like to now how to Insert a PRODUCT in the last 10 rows to calculate the correct value in the Columns after the Subtotal line

Its probably a lot easier than my small brain thinks but I've tried several times and just end up with a circular that is annoying me!

Regards
Andy

Are you wanting the number of each unique item in column C?
If so, then in cell F47, enter this formula and copy down.

=COUNTIF($C$6:$C$45,$C47)

This assumes you will select the appropriate item in cell C47