miricle lookup formula???
 

Ok this has got me beat and maybe I am asking too much (of myself anyway!)

What I want to do is this:

I have 6 products lets name them product 1 through to 6. At the moment
these products are on separate sheets in a workbook. The sheets are named
product 1 etc... Each of these products have 4 sets of data related to them
these can be called data 1 through to 4. This data is in each sheet from
A1:D105. Row 1 has the data labels data A data B etc.

What we do manually at the moment is go to the data for a given product,
look down one of the data sets for a known value say data D and then retrieve
the 3 remaining values in adjacent columns. So what I would like is for a
front page to have input boxes for the product and the known value and a
formula or formulas to retrieve the missing ones. Now I have managed to get
it working for one product with the help of the following article.

How to look up a value in a list and return multiple corresponding values by
Ashish Mathur http://office.microsoft.com/en-us/as...260381033.aspx

You will notice this is for multiple results because to put a spin on things
the known value can be repeated in the data and we need the corresponding
values for all the known values in the data set.
What I tried doing was creating a drop down box containing the products as
the first input, naming the data range for each product and having that
result entered into the above formula via the INDIRECT() function.

As you can imagine this is getting out of hand!!! And it didnt work!

Can you have one formula but tell it to look in or move to different areas
like sheets?
Would it be easier if the products were compiled on one sheet?

The formula only has to find one of the corresponding values say data A
because 2 simple lookup formulas would then find the other 2 values based on
the first result.

If this is beyond excel what would be able to achieve this? An Access
database?

Thanks

Mark

miricle lookup formula???
 

Hi!

This should be fairly easy based on your explanation. Extracting data is
sort of my "specialty"

Can you send me a copy of your file? If so, I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"heymoa" wrote in message
...
Ok this has got me beat and maybe I am asking too much (of myself anyway!)

What I want to do is this:

I have 6 products let's name them product 1 through to 6. At the moment
these products are on separate sheets in a workbook. The sheets are named
product 1 etc... Each of these products have 4 sets of data related to
them
these can be called data 1 through to 4. This data is in each sheet from
A1:D105. Row 1 has the data labels data A data B etc.

What we do manually at the moment is go to the data for a given product,
look down one of the data sets for a known value say data D and then
retrieve
the 3 remaining values in adjacent columns. So what I would like is for a
front page to have input boxes for the product and the known value and a
formula or formula's to retrieve the missing ones. Now I have managed to
get
it working for one product with the help of the following article.

How to look up a value in a list and return multiple corresponding values
by
Ashish Mathur
http://office.microsoft.com/en-us/as...260381033.aspx

You will notice this is for multiple results because to put a spin on
things
the known value can be repeated in the data and we need the corresponding
values for all the known values in the data set.
What I tried doing was creating a drop down box containing the products as
the first input, naming the data range for each product and having that
result entered into the above formula via the INDIRECT() function.

As you can imagine this is getting out of hand!!! And it didn't work!

Can you have one formula but tell it to look in or move to different areas
like sheets?
Would it be easier if the products were compiled on one sheet?

The formula only has to find one of the corresponding values say data A
because 2 simple lookup formulas would then find the other 2 values based
on
the first result.

If this is beyond excel what would be able to achieve this? An Access
database?

Thanks

Mark