I've got a workbook that I've created via VBA.

One sheet is populated with data that I want to look up and has
various ranges on it to use in .Validation on the other sheet.

The other sheet has .Validation in column 1 that looks up, for
instance, "FoodType".

Once the user has selected "Fruit", column 2's .Validation kicks
in and in that column the user sees a drop down list of "Bananas,
Oranges, Apples... and so-forth".

Once the user has selected a FoodType and a specific food, I want
to go to that table of lookup data that I created on the other
worksheet, use FoodType|SpecificFood as a unique key, and
retrieve a bunch of other cell values in the located row and use
them to populate corresponding columns in the sheet where the
user specified FoodType|SpecificFood.

Can somebody point me in the right direction
strategy/syntax-wise?

For starters, I'm guessing that the code will be located in
Worksheet_Change and I'll be casing out on Target.Column.

But what do I do once that the "SpecificFood" column has
changed? Take FoodType and SpecificFood over to the
lookup sheet... but what then?
--
PeteCresswell

why not make a column on the left of the lookup table that has a
concatenation of Food type and specified food. Then just concatentate the
users choices and use a normal vlookup formula.

--
Regards,
Tom Ogilvy


"(PeteCresswell)" wrote:

I've got a workbook that I've created via VBA.

One sheet is populated with data that I want to look up and has
various ranges on it to use in .Validation on the other sheet.

The other sheet has .Validation in column 1 that looks up, for
instance, "FoodType".

Once the user has selected "Fruit", column 2's .Validation kicks
in and in that column the user sees a drop down list of "Bananas,
Oranges, Apples... and so-forth".

Once the user has selected a FoodType and a specific food, I want
to go to that table of lookup data that I created on the other
worksheet, use FoodType|SpecificFood as a unique key, and
retrieve a bunch of other cell values in the located row and use
them to populate corresponding columns in the sheet where the
user specified FoodType|SpecificFood.

Can somebody point me in the right direction
strategy/syntax-wise?

For starters, I'm guessing that the code will be located in
Worksheet_Change and I'll be casing out on Target.Column.

But what do I do once that the "SpecificFood" column has
changed? Take FoodType and SpecificFood over to the
lookup sheet... but what then?
--
PeteCresswell

Per Tom Ogilvy:
and use a normal vlookup formula.

Thanks. That was what I was trolling for: some Excel-specific
syntax to avoid beating down the table looking for a match.
--
PeteCresswell

Per (PeteCresswell):
some Excel-specific
syntax to avoid beating down the table looking for a match.
--

I've got vLookup working.... sort of....

Did the concatenate column values thing in the table and am then
concatting the two cells' .Values on-the-fly into an invisible
cell and basing the vLookup on that cell containing the
on-the-fly concatenated names.

Right now it's not working with the range parm = False... i.e. it
will do a fuzzy match but fails on an exact match.

Before I spend any more man hours trying to get around that, I
would pose the following question:
----------------------------------------------------------------
Given that we will be doing maybe 20-30 vLookups (one for each
cell that has tb populated) each time the user changes one of the
key cells, will that start to slow things down unduly when we get
a thousand or so rows in the table tb searched? Right now we
only have a couple hundred.

I'm thinking that maybe I should fall back to my kneejerk
solution, which was to case out on Target.Row/Target.Column and,
if it's the second key cell, just concat my key values in VBA,
set a .Range to the lookup table, and beat down the first column
of the .Range looking for a match. Then, once a match was
found, iterate across the table's row populating columns in the
sheet the user sees.

Seems to have the advantages of being up-front (i.e. you look at
the VBA code, and you see the whole picture..), being
more-or-less of a sure thing, and only requiring a single
(instead of 20-30) trip through the table looking for a
match..... and I'm guessing that with a little effort, I could
make the table search binary.
----------------------------------------------------------------

Any thoughts on this?
--
PeteCresswell

I would suspect you need to pursue what you know best.

--
Regards,
Tom Ogilvy


"(PeteCresswell)" wrote:

Per (PeteCresswell):
some Excel-specific
syntax to avoid beating down the table looking for a match.
--

I've got vLookup working.... sort of....

Did the concatenate column values thing in the table and am then
concatting the two cells' .Values on-the-fly into an invisible
cell and basing the vLookup on that cell containing the
on-the-fly concatenated names.

Right now it's not working with the range parm = False... i.e. it
will do a fuzzy match but fails on an exact match.

Before I spend any more man hours trying to get around that, I
would pose the following question:
----------------------------------------------------------------
Given that we will be doing maybe 20-30 vLookups (one for each
cell that has tb populated) each time the user changes one of the
key cells, will that start to slow things down unduly when we get
a thousand or so rows in the table tb searched? Right now we
only have a couple hundred.

I'm thinking that maybe I should fall back to my kneejerk
solution, which was to case out on Target.Row/Target.Column and,
if it's the second key cell, just concat my key values in VBA,
set a .Range to the lookup table, and beat down the first column
of the .Range looking for a match. Then, once a match was
found, iterate across the table's row populating columns in the
sheet the user sees.

Seems to have the advantages of being up-front (i.e. you look at
the VBA code, and you see the whole picture..), being
more-or-less of a sure thing, and only requiring a single
(instead of 20-30) trip through the table looking for a
match..... and I'm guessing that with a little effort, I could
make the table search binary.
----------------------------------------------------------------

Any thoughts on this?
--
PeteCresswell

Per Tom Ogilvy:
I would suspect you need to pursue what you know best.

You mean that I should bag work on Monday and go windsurfing?

Excellent suggestion!....
--
PeteCresswell