Look up data from multiple worksheets

Any takers?

Hi JimDandy

Here is one approach that almost achieves what you require.

If you really need "No" entries it can be done. However, could there be multiple (looked for) adapters?

Regards

Alastair

It is an approach, and I certainly do appreciate the suggestion however I really need to keep the Source pace separate from the Results page. The intention is that I can replace the Source page with new data, change the Adapter list, and the Results page stays intact, changing only as the other two change. This result can be done by sorting the Source page and selecting items with the filter and doesn't really need a formula.

Ok - not a problem.

Could there ever be more than one entry in the results for any workstation? Do you need the results page to show "No" or can it just be omitted?

Regards
Alastair

Sorry, I forgot to answer that question... no, I don't suspect there being any more than one entry found, and if there was, any answer would be good, and the results can be empty.

Thank you

Hi JimDandy

I have written a macro that does what you ask.

To run it press Ctrl+shift+Q

I have shown the effect of adding a couple of extra adapters onto the Adapters tab

Hope this is of use

Regards
Alastair

This worked well in the example but my full source is 267000 rows, and the Results worksheet has 3200 individual device names. I modified the macro to allow for the size of both worksheets, but I still get an error at the line "wkstn(i, 1) = ActiveCell.Offset(0, -3)" and it seems to stop at about row 7827.

OK so I mis guessed the size of your problem I'm sorry. Alright!

I have now tested the updated macro on 300,000 lines and it works fine (OK so it gave my little laptop indigestion, but the macro worked)

If it seizes up your computer, let me know and I can re-do it so it uses less resources.

Hope this works ok

Alastair

Outstanding! Many thanks...this performed exactly as I needed.