Set a value, use it later (possible VBA?)
 

I've never used VBA before and, probably, made some very long and complex
Excel functions that could have been simplified using VBA. This might be a
good place for me to start to use and learn VBA. I searched for a ng with
Excel and VBA in the title, but didn't see one so I'm posting here for you
good folks!

I have two spreadsheets. One has monthly data (billing.xls) and the other
has static tables (ratebook.xls) that are used in calculations for
billing.xls. There are four tables that I use in ratebook.xls to lookup
values for billing.xls. Each table of rates in ratebook.xls has a defined
name. I'd like to set a value when new data is imported into billing.xls
that will dynamically update my lookup functions and choose the correct
table in ratebook.xls.

Example:
Ratebook.xls - structured as two worksheets named Ground and CWT. Each
worksheet has 4 named ranges ( in the Ground worksheet they are GroundBand1,
GroundBand2, GroundBand3, GroundBand4 and in the CWT worksheet they are
GroundCWTBand1, GroundCWTBand2...etc.) The ranges are structured as shipping
rates by weight and destination; where weight is down the left column and
destinations are along the top row. I use lookup functions and index/match
combos to find the shipping charge at the intersection of a given weight to
a given destination. Each of the named ranges are structured identically,
but have different rates in the matrix.

Ratebook.xls sets a value when data is refreshed for "Band". The value has
four possible outcomes; Band1, Band2, Band3, Band4.

Based on this value, I would like my various lookup functions to change and
look at the corresponding named range. If "Band"=Band1, then change named
range in lookup functions globally to "GroundBand1", and so on.

If this can be accomplished outside of VBA, I'm still interested. I just
want to do it the smartest and easiest way.

Thanks in advance,
Rich

Take a look at the InDirect function
HTH
Gary Brown

"Rich Palarea" wrote:

I've never used VBA before and, probably, made some very long and complex
Excel functions that could have been simplified using VBA. This might be a
good place for me to start to use and learn VBA. I searched for a ng with
Excel and VBA in the title, but didn't see one so I'm posting here for you
good folks!

I have two spreadsheets. One has monthly data (billing.xls) and the other
has static tables (ratebook.xls) that are used in calculations for
billing.xls. There are four tables that I use in ratebook.xls to lookup
values for billing.xls. Each table of rates in ratebook.xls has a defined
name. I'd like to set a value when new data is imported into billing.xls
that will dynamically update my lookup functions and choose the correct
table in ratebook.xls.

Example:
Ratebook.xls - structured as two worksheets named Ground and CWT. Each
worksheet has 4 named ranges ( in the Ground worksheet they are GroundBand1,
GroundBand2, GroundBand3, GroundBand4 and in the CWT worksheet they are
GroundCWTBand1, GroundCWTBand2...etc.) The ranges are structured as shipping
rates by weight and destination; where weight is down the left column and
destinations are along the top row. I use lookup functions and index/match
combos to find the shipping charge at the intersection of a given weight to
a given destination. Each of the named ranges are structured identically,
but have different rates in the matrix.

Ratebook.xls sets a value when data is refreshed for "Band". The value has
four possible outcomes; Band1, Band2, Band3, Band4.

Based on this value, I would like my various lookup functions to change and
look at the corresponding named range. If "Band"=Band1, then change named
range in lookup functions globally to "GroundBand1", and so on.

If this can be accomplished outside of VBA, I'm still interested. I just
want to do it the smartest and easiest way.

Thanks in advance,
Rich