Finding duplicates on separate sheets

[COLINBELL2780]

Hi guys

this is my 1st post, ive always loved excel and belive im ok at it for self learnt but this one is testing me so im calling on some assistance if at all possible

I cannot actually upload my own spreadsheet as its over 1000KB so ive deleted most of it a made a mini version attached

what im looking for help with is:
1.) if any product code is selected on the kit sheet (Green tab) the same code on the catalogue (Yellow tab) would highlight so we know at a glance which products are being used and which are surplus
, one other issue is that the tabs have some code to change the name automatically to marry up to the kit name choosen

2.) as we have so many products in our main spreadsheet (not Mini) you will see the data validation drop down on the (Green Tab) are very long, i wondering if there is a way to hide some of the cells in the catalogue that also reduces the amount avaiable in the drop down list making it easier & quicker to make a selection?

or
is there a way to instead of drop down to start typing "Running outlet" for example in the cell and it only shows any line with that selected text to choose from?


any help be much appriciated

[dflak]

1.) if any product code is selected on the kit sheet (Green tab) the same code on the catalogue (Yellow tab) would highlight so we know at a glance which products are being used and which are surplus
, one other issue is that the tabs have some code to change the name automatically to marry up to the kit name choosen

Do you mean selecting the cell or selecting a drop down item from the cell? If the latter, then you could set up a VBA On Change event to accomplish this task. What I would do is duplicate the selection to an out-of-the way cell and use conditional formatting to highlight cells that match that selection.

2.) as we have so many products in our main spreadsheet (not Mini) you will see the data validation drop down on the (Green Tab) are very long, i wondering if there is a way to hide some of the cells in the catalogue that also reduces the amount avaiable in the drop down list making it easier & quicker to make a selection?

I am not quite sure what the question is here. What kind of criteria would you use to narrow down the selection? One trick on minimizing the number of items is to use a pivot table with a filter and overlay the results with a named dynamic range and use the name as the drop down source.

is there a way to instead of drop down to start typing "Running outlet" for example in the cell and it only shows any line with that selected text to choose from?

This can only be done using user forms. This would restrict you to making an entry of one record at a time. It would probably be implemented by selecting a cell, launching a macro to launch the form, fill in the form, click in a button on the form and write the data back.

I can't help but notice how "hard coded" things are on this spreadsheet. For example, the lookup values on the catalogue sheet are on very specific rows. There is no leeway to add or delete data above them. I suggest you break out each lookup group into its own Excel table. Tables are very flexible and will allow you to add and delete data without affecting formulas or lookup.

I would also make the same suggestion about the House Type sheet, but then you would have to string out things Horizontally and that is probably not what you want to do. My design for this would be to have a button on each of the sub-section rows which when pressed would launch the appropriate form then open up a new row in that section and insert the new data.

Either way, it's kind of a big project. The lowest hanging fruit is my suggestion to break up the lookup data into their own tables.