Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|