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
>
>
>