dynamic vlookup?
 

How would I create a formula that uses 'User input' as the criteria to
find the designated data on another sheet? Here's an example:

Say I have sales data for cars. A user can choose to see total sales
on 'Make' (ie Honda) or 'Model' (ie Accord), or 'Make/Model' (Honda
Accord). Assume for this example that other manufacturers make an
'Accord', so 'Accord' and 'Honda Accord' would return different totals.

I've already got drop-downs created for Make & Model that contain the
possible options. But how do I structure the VLOOKUP to use these
user-entered values? When I try it the 'normal' way, the VLOOKUP
doesn't recognize a blank cell (ie a Make but no Model) as blank -- it
evaluates the cell as '0'.

Any ideas? I know that VLOOKUP may not be the best way to do, but it's
what I knew how to do...

TIA, Ray

dynamic vlookup?
 

The answer depends on how your raw data is setup. Without a good detailed
description or seeing a small sample it'd be difficult to make a suggestion.

Biff

"Ray" wrote in message
oups.com...
How would I create a formula that uses 'User input' as the criteria to
find the designated data on another sheet? Here's an example:

Say I have sales data for cars. A user can choose to see total sales
on 'Make' (ie Honda) or 'Model' (ie Accord), or 'Make/Model' (Honda
Accord). Assume for this example that other manufacturers make an
'Accord', so 'Accord' and 'Honda Accord' would return different totals.

I've already got drop-downs created for Make & Model that contain the
possible options. But how do I structure the VLOOKUP to use these
user-entered values? When I try it the 'normal' way, the VLOOKUP
doesn't recognize a blank cell (ie a Make but no Model) as blank -- it
evaluates the cell as '0'.

Any ideas? I know that VLOOKUP may not be the best way to do, but it's
what I knew how to do...

TIA, Ray

dynamic vlookup?
 

Hi Ray,

I sent you a workbook that may do what you want, if not, maybe get on the
right track. Tried to explain what I was doing but got all muddled and
would probably not make good enough sense.

HTH
Regards,
Howard

"Ray" wrote in message
oups.com...
How would I create a formula that uses 'User input' as the criteria to
find the designated data on another sheet? Here's an example:

Say I have sales data for cars. A user can choose to see total sales
on 'Make' (ie Honda) or 'Model' (ie Accord), or 'Make/Model' (Honda
Accord). Assume for this example that other manufacturers make an
'Accord', so 'Accord' and 'Honda Accord' would return different totals.

I've already got drop-downs created for Make & Model that contain the
possible options. But how do I structure the VLOOKUP to use these
user-entered values? When I try it the 'normal' way, the VLOOKUP
doesn't recognize a blank cell (ie a Make but no Model) as blank -- it
evaluates the cell as '0'.

Any ideas? I know that VLOOKUP may not be the best way to do, but it's
what I knew how to do...

TIA, Ray

dynamic vlookup?
 

Thanks alot for your help, Howard ... as you said, not exactly what I
needed (likely due to my poor explanation of what I needed!), but put
me on the right track -- and now the issue is solved!

Best Rgds,
Ray


L. Howard Kittle wrote:
Hi Ray,

I sent you a workbook that may do what you want, if not, maybe get on the
right track. Tried to explain what I was doing but got all muddled and
would probably not make good enough sense.

HTH
Regards,
Howard

"Ray" wrote in message
oups.com...
How would I create a formula that uses 'User input' as the criteria to
find the designated data on another sheet? Here's an example:

Say I have sales data for cars. A user can choose to see total sales
on 'Make' (ie Honda) or 'Model' (ie Accord), or 'Make/Model' (Honda
Accord). Assume for this example that other manufacturers make an
'Accord', so 'Accord' and 'Honda Accord' would return different totals.

I've already got drop-downs created for Make & Model that contain the
possible options. But how do I structure the VLOOKUP to use these
user-entered values? When I try it the 'normal' way, the VLOOKUP
doesn't recognize a blank cell (ie a Make but no Model) as blank -- it
evaluates the cell as '0'.

Any ideas? I know that VLOOKUP may not be the best way to do, but it's
what I knew how to do...

TIA, Ray