INDIRECT function in Dynamic Data Validation does not work

Hi.

1. For an INDIVIDUAL vendor, is the PRODUCT ID always unique?

2. an there only EVER be one "BBQSauce"? If not then...

3. Can there only EVER be one "Sauce, Bbq Sweet & Sour Plastic Jug Shelf Stable"? If not then...

4. Is the Product Number the only REALLY unique description for the overall product?

5. Can I rearrange the Vendor lists?

6. Please explain what you wanted the formula in BA6 to do? It's not working and I don't follow what you were wanting it for.

7. Please confirm that you are still using Excel 2007. (Excel 2010 or later would be nicer!!).

Thank you Glenn for the quick prompt. Kindly read my answers to each of your questions.


1. For an INDIVIDUAL vendor, is the PRODUCT ID always unique?
ProductID can be duplicate, ex, Vendor1 can have ChicBrdd with ProductNbr 1111111 and Vendor2 can have same item with same ProductNbr or just a different ProductNbr.
Reason I have used 111 or 222 or 333 prefixes for the ProductNbr was to test if the search function is working properly, actual numbers can be same or different.

2. an there only EVER be one "BBQSauce"? If not then...
Each Vendor can be selling the same BBQSauce or a variation of packing in which case the ProductID will be the same but the ProductNbr will be different

3. Can there only EVER be one "Sauce, Bbq Sweet & Sour Plastic Jug Shelf Stable"? If not then...
ProductDescription can be same, Brand and/or ProductNbr will be different

4. Is the Product Number the only REALLY unique description for the overall product?
Yes, I don't use it in the drop down list because numbers are difficult to remember where as ProductID is descriptive and easy to remember

5. Can I rearrange the Vendor lists?
To make data entry easy for end user following should be in front and together: Date-Vendor-ProductID, everything else is either a lookup or formula.

6. Please explain what you wanted the formula in BA6 to do? It's not working and I don't follow what you were wanting it for.
Whatever I enter in DataEntry ProductID (D6) by virtue of CELL function in BD2, I find all the items that match my partial or full searched item, and counts the number in BB6:BJ999 range the matches found, and lists it in column BL which populates the drop down list in column D. The "BB6=$C$6" part, used only with USVendor was for testing, I have removed it from other vendor lists but seems I forgot to remove it from USVendor, it was used to see if that will pickup only items for a particular vendor if I had combined all lists together, it did not

7. Please confirm that you are still using Excel 2007. (Excel 2010 or later would be nicer!!).
My laptop is 365 my desktop 2013.

Again thank you for taking your valuable time to help me with this problem.
RJK

You didn't really answer the Q. Can ONE vendor have two or more products with the same ProductID?

I have assumed the answer is "no". If it is "yes", let me know and i can tweak it.

I have combined ALL the suppliers into one Table, on a separate sheet, which makes life much simpler. If you add on new suppliers/items, all you have to do is DATA/SORT/OK/OK to get all the information that needs to be together, together. I set up one Named range (ProductID), uisng this:

Formula:

Please Login or Register  to view this content.

and used ProductID as the data validation list in ProductID column.

there is nothing in the price column, as I couldn't see it in the raw data.

Thank you very much for taking the time to help me out with my problem.

Your solution is what I started with, the drop down lists "all" the items of the vendor, which could be over 100 items, hence, difficult to find a particular item, especially on a laptop. That is why I chose to use column BA together with Cell function in BD2 and filtered in column BK to get "only" matches in column BL to show in the drop down list of column D.

Column E, is for the cost, it is only entered in Data Entry to track if costs are trending up or down, it is not fixed or constant, hence not in VendorList description.

Again I appreciate your help, and would keep your INDEX formula for future use, and how would you tweak it when a vendor can have multiple items with same ProductID but different ProductNbr, because that is the norm.

OK. Let's try again. I still don't understand this bit:

That is why I chose to use column BA together with Cell function in BD2 and filtered in column BK to get "only" matches in column BL to show in the drop down list of column D.

Do you mean that you would go to the desired vendor, type in "chi" and then select from a reduced drop-down selection?

I have made a bit of a guess here !!

Select a vendor in c6.
Enter a search term (word, letter, etc) from the product ID in I3.
The dropdown in d6 shows only those products relating to the selected vendor, containing the search term.

What it is doing is looking for the FIRST blank row in dataentry column D and matching the corresponding for the supplier from column C with chosen keyword. So, if you go back to a previous value and delete the entry in column C it will show you the updated list for that supplier/search term combination.

See what you think.

Not sure, due to my English.

1. Why you use many of formula =Cell("Contents")?
For me, it's very strange , look like want to refer it self (and cause a circular reference)
(In this file I'm already erased them all)

2. I'm not sure that Excel allow to use Offset via Indirected name or not. But if you want to use INDIRECT.
You may create a 'RANGE' instead of Offset refer.
As a sample in sheet REFER cell E3

Please Login or Register  to view this content.

And Name it as USvendor_Validation_List (after delete the old one).

3. Then change data validation in sheet DATAEntry of cell D6 to

Please Login or Register  to view this content.

Regards.

I am still not clear about what you want. But idoes this describe it???

You enter partial text. You then want to see vendor/Product ID combinations (based on a search for the partuial text in productID), select one and populate the vendor and productID columns?

In my version, (in line with what I had thought you wanted...) you first select the vendor from the DD in column C and THEN the list of matching products appear in the column D DD box.

Here's another idea (again, using my restructured set-up. Yours is too clunky!!). This DOES NOT require any sorting of the raw data table.

1. Select partial phrase in B5.

2. Click on green shaded box in column A.

3. Make your selection.

4. C, D, H & I populate.

5. Selection at column A becomes invisible.

6. Green shading moves to the next blank cell in column A.

7. Start again from Step 1 .

As I was say, you may need to use INDIRECT(INDIRECT( cell that contains formula that generate range ) )

for example
BE2 : named as USVendor_validation_list

Please Login or Register  to view this content.

Please try this file.



Regards.

Can you tell me how my solution DOES NOT work?

Maybe try this

Data Validation List at D6

=OFFSET($BK$6,,MATCH(C6&"List",$BB$4:$CO$4,),INDEX($BK$4:$CX$4,MATCH(C6&"List",$BB$4:$CO$4,)))