D
David Vollmer
I have a main workbook that contains 55 sheets. The main sheet called
"Scorecard" has among other things 2 cells: one that contains a city and one
that contains a state. If a city and corresponding state are a match then a
cell in the Scorecard sheet will receive a 1; if no match, then a 0.
The second sheet, called "Lookups" contains the following cells:
City B1 The formula for this is =Scorecard!D13
State B2 The formula for this is =Scorecard!E13
Result B3 The formula for this does the lookup and returns either "Not
Found" or "HiDta Area" and that formula is:
=IF(ISERROR(VLOOKUP($B$1,INDIRECT($B$2&"!A2:C1500"),3,FALSE)),"Not
Found","HiDTA Area")
C3 will contain a 0 if Not Found or a 1 if HiDta Area and that formula is:
=IF(B3="Not Found",0,1)
The Scorecard sheet will pull that 0 or 1 to a cell (L13) using the formula
=Lookups!$C$3)
All of this works fine until I have completed the Scorecard and have saved
it by saving only the Scorecard sheet with a unique name based upon the
customer's name and number. When I open that sheet later the resulting 1 or 0
in cell L13 may be changed. Since this workbook does not have the additional
sheets, the lookup fails.
If the original workbook is loaded, the result in L13 will be whatever is
found in cell $C$3 on the original workbook.
My question - at last - is how, when I load a previously saved sheet from
the "master" workbook, can I get the lookup to work. I know I can go to the
master workbook and reenter the city and state but that method is not user
friendly and there are 8 people using this process.
Your help is most appreciated!
Thank you,
David
"Scorecard" has among other things 2 cells: one that contains a city and one
that contains a state. If a city and corresponding state are a match then a
cell in the Scorecard sheet will receive a 1; if no match, then a 0.
The second sheet, called "Lookups" contains the following cells:
City B1 The formula for this is =Scorecard!D13
State B2 The formula for this is =Scorecard!E13
Result B3 The formula for this does the lookup and returns either "Not
Found" or "HiDta Area" and that formula is:
=IF(ISERROR(VLOOKUP($B$1,INDIRECT($B$2&"!A2:C1500"),3,FALSE)),"Not
Found","HiDTA Area")
C3 will contain a 0 if Not Found or a 1 if HiDta Area and that formula is:
=IF(B3="Not Found",0,1)
The Scorecard sheet will pull that 0 or 1 to a cell (L13) using the formula
=Lookups!$C$3)
All of this works fine until I have completed the Scorecard and have saved
it by saving only the Scorecard sheet with a unique name based upon the
customer's name and number. When I open that sheet later the resulting 1 or 0
in cell L13 may be changed. Since this workbook does not have the additional
sheets, the lookup fails.
If the original workbook is loaded, the result in L13 will be whatever is
found in cell $C$3 on the original workbook.
My question - at last - is how, when I load a previously saved sheet from
the "master" workbook, can I get the lookup to work. I know I can go to the
master workbook and reenter the city and state but that method is not user
friendly and there are 8 people using this process.
Your help is most appreciated!
Thank you,
David