Searching a column for a value and returning corresponding values on multiple sheets

[idemdito]

Hi,

Here is an example to demonstrate what I would like to do:

Say I have 2 columns on the first sheet -

Sheet 1:

column 1----column 2
---a------------1----
---b------------2----
---c------------3----
---d------------4----
---a------------5----
---c------------6----
---a------------7----
---b------------8----

The customers are marked with a,b,c etc. The products are given a number.
I would like to make sheets for every customer (a,b etc) and search for every matching product for that particular customer. So I am going to work on multiple sheets, I would have used VLOOKUP but this formula does not give multiple results, and I cannot use an array formaula because it has to work on multiple sheets
New customers and products will be added on the way, so it has to have a dynamic structure.


Example:

Sheet 2 .
A
----1----
----5----
----7----


Sheet 3
B
----2----
----8----

I hope someone can advice me on this matter or knows a formula that might do the job?

Thanks!

[beaunydal]

and I cannot use an array formaula because it has to work on multiple sheets

It can be done with an array formula because it is the same formula in each customer sheet and they all refer to the same Sheet1...

Please Login or Register  to view this content.

Where Customer is the cells on Sheet1 with the customer names [Refers to:=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1,1)] and Product is the cells on Sheet1 with the Product codes [Refers to:=OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$A:$A)-1,1)]
These are dynamic named ranges. As more Customers and Products are added to Sheet1 the ranges adapt to the include them.

Beau Nydal

[idemdito]

@ beaunydal


Thanks...... this is the solution. Appreciate it...

Forgot about working with Name Ranges, already tried an array formula but I did not get it to work. Now it works like a charm...