Find and go to product code in a list of products

[ALANBERNARD]

Hi guys,

I have a list of product specs, over 300, and need to be able to be able to select one from this list by product code that appears on the spec and go to it on the list, I know I can do this with the Find &Replace function but need to be able to do it using a command button any suggestions?

[:) Sixthsense :)]

Hi ALANBERNARD,

Welcome to the forum

Use Vlookup.... If possbile attach a sample workbook with expected output so that we can assist you further

To know how to attach a file, please refer my signature area

[ALANBERNARD]

Hi Sixthsense,
Many thanks for the reply but VLOOKUP doesn't get me to the required product spec. I have tried attaching a file but no joy. the little circle that goes round and round will still be going round next week. I need to be able to enter a product code in a cell (on the same sheet as all the product specs) and then, by clicking the command button, go straight to this product spec in the list of product specs. I will write a little macro to attach to the command button. It needs to operate the same way as find & replace function I hope I am being clear.
AlanBernardPRODUCT SPEC.xlsm

[:) Sixthsense :)]

Yes it's better to go for code solution

Select Design Mode in Developer Tab and do right click on command button and select View Code and paste the below code. Return to excel and Deselect Design mode and enter product code in L11 cell click the command button.

Please Login or Register  to view this content.

[Marcol]

A fairly simple way with formula.

1/. Extract a list of "Product Codes" from Column H, See the Names Manager for the Dynamic Names used.

2/. Use this list for Data Validation in K11.

3/. Use Conditional formatting to highlight the header row of the required table.

This will work for any amount of tables of any size provided that the header rows in column G begin with "product", case doesn't matter.
To get it to scroll to the fable you will need to use VBa.

[Marcol]

Try this workbook if you need to auto-scroll to the target table.
It uses the Worksheet_Change event rather than a Button, Buttons in my opinion should be avoided if possible.

Please Login or Register  to view this content.

Select from the drop-down in K2

The sheet is built the same way as that in Post #5