Basic search function

[dubseven]

Hi All -

I have a spreadsheet with 2 sheets.

Sheet 1 contains various columns, one for part numbers, one for prices, one fordescription, etc. and two columns of cells that are called "stage 1" (call this column A) and "stage 2" (Column B). The cells in last two either have a yes or a no in them as a value.

For Sheet 2, I want it to search through Sheet 1, and have it return data from rows that are marked "yes" as "Stage 1".

What is the best way to do this?

Thank you!

 

[dubseven]

Re: Need help with a basic search function

dubseven,

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the macro code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Tried this - got error 1004 - method intersect of object _Global failed.

 

[Fluff]

Re: Need help with a basic search function

I've uploaded you test file here
which works for me. Does it work for you

EDIT
Link removed

 

[dubseven]

Re: Need help with a basic search function

I've uploaded you test file here https://1drv.ms/x/s!AtYrlcV2NWPFgQxXgvBb35kVwtnu
which works for me. Does it work for you

Looks like a totally different file?

 

[Fluff]

Re: Need help with a basic search function

Oops wrong link, try this one
https://1drv.ms/x/s!AtYrlcV2NWPFgQ31kaPOBekC4oMH

 

[dubseven]

Re: Need help with a basic search function

There are 7 stage 1 items, it only pulled 2?

 

[Fluff]

Re: Need help with a basic search function

It will only copy the data across, when you type Yes into col H

 

[dubseven]

Re: Need help with a basic search function

Is there any way to do it with a regular formula so it auto updates? Will be making a lot of changes to the sheet

 

[Fluff]

Re: Need help with a basic search function

I'm afraid I've no idea. I don't do much with formulae, maybe somebody else will be able to help.