lmerrick528
New Member
Is there any automated way to replace the original named range definitions (refers to values) from file?
I have a workbook that has over 200 named ranges, I have exported all of the named ranges and “rebuilt” the definitions to take advantage of the Index formula to have more dynamic ranges.
Sample Data Below:
Original Export - used the following to export the data: Formulas: Name Manager: Use in Formula: Paste Names -- these are in columns A & B on a worksheet
Asset
='WBS Selections'!$C$23
Asset_Mgmt
='EES WBS'!$C$110:$C$123
Asset_Mgmt_and_Other_Global_Support
='Faucet WBS'!$C$112:$C$117
Balance_of_Plant
='Faucet WBS'!$C$3:$C$11
Balance_of_Plant_Installation_Sink
='Sink WBS'!$C$81:$C$99
Balance_of_Plant_Material_Sink
='Sink WBS'!$C$63:$C$79
Business_Rates_Property_Taxes_etc
='Faucet WBS'!$C$119:$C$121
Business_Rates_Property_Taxes_Sink_OM
='Sink WBS'!$C$331:$C$333
Capitalized_Overhead
='Faucet WBS'!$C$13:$C$14
Desired data to replace all of the named ranges definitions -- again in two columns, can be put anywhere in excel worksheet
Asset
='WBS Selections'!$C$23
Asset_Mgmt
='EES WBS'!$C$110:INDEX('EES WBS'!$C$110:$C$125,COUNTSA('EES WBS'!$C$110:$C$125)
Asset_Mgmt_and_Other_Global_Support
='Faucet WBS'!$C$112:INDEX('Faucet WBS'!$C$112:$C$119,COUNTSA('Faucet WBS'!$C$112:$C$119)
Balance_of_Plant
='Faucet WBS'!$C$3:INDEX('Faucet WBS'!$C$3:$C$13,COUNTSA('Faucet WBS'!$C$3:$C$13)
Balance_of_Plant_Installation_Sink
='Sink WBS'!$C$81:INDEX('Sink WBS'!$C$81:$C$101,COUNTSA('Sink WBS'!$C$81:$C$101)
Balance_of_Plant_Material_Sink
='Sink WBS'!$C$63:INDEX('Sink WBS'!$C$63:$C$81,COUNTSA('Sink WBS'!$C$63:$C$81)
Business_Rates_Property_Taxes_etc
='Faucet WBS'!$C$119:INDEX('Faucet WBS'!$C$119:$C$123,COUNTSA('Faucet WBS'!$C$119:$C$123)
Business_Rates_Property_Taxes_Sink_OM
='Sink WBS'!$C$331:INDEX('Sink WBS'!$C$331:$C$335,COUNTSA('Sink WBS'!$C$331:$C$335)
Capitalized_Overhead
='Faucet WBS'!$C$13:INDEX('Faucet WBS'!$C$13:$C$16,COUNTSA('Faucet WBS'!$C$13:$C$16)
Desired data to replace all of the named ranges definitions -- again in two columns, can be put anywhere in excel worksheet
Desired data to replace all of the named ranges definitions -- again in two columns, can be put anywhere in excel worksheet
I have a workbook that has over 200 named ranges, I have exported all of the named ranges and “rebuilt” the definitions to take advantage of the Index formula to have more dynamic ranges.
Sample Data Below:
Original Export - used the following to export the data: Formulas: Name Manager: Use in Formula: Paste Names -- these are in columns A & B on a worksheet
Asset
='WBS Selections'!$C$23
Asset_Mgmt
='EES WBS'!$C$110:$C$123
Asset_Mgmt_and_Other_Global_Support
='Faucet WBS'!$C$112:$C$117
Balance_of_Plant
='Faucet WBS'!$C$3:$C$11
Balance_of_Plant_Installation_Sink
='Sink WBS'!$C$81:$C$99
Balance_of_Plant_Material_Sink
='Sink WBS'!$C$63:$C$79
Business_Rates_Property_Taxes_etc
='Faucet WBS'!$C$119:$C$121
Business_Rates_Property_Taxes_Sink_OM
='Sink WBS'!$C$331:$C$333
Capitalized_Overhead
='Faucet WBS'!$C$13:$C$14
Desired data to replace all of the named ranges definitions -- again in two columns, can be put anywhere in excel worksheet
Asset
='WBS Selections'!$C$23
Asset_Mgmt
='EES WBS'!$C$110:INDEX('EES WBS'!$C$110:$C$125,COUNTSA('EES WBS'!$C$110:$C$125)
Asset_Mgmt_and_Other_Global_Support
='Faucet WBS'!$C$112:INDEX('Faucet WBS'!$C$112:$C$119,COUNTSA('Faucet WBS'!$C$112:$C$119)
Balance_of_Plant
='Faucet WBS'!$C$3:INDEX('Faucet WBS'!$C$3:$C$13,COUNTSA('Faucet WBS'!$C$3:$C$13)
Balance_of_Plant_Installation_Sink
='Sink WBS'!$C$81:INDEX('Sink WBS'!$C$81:$C$101,COUNTSA('Sink WBS'!$C$81:$C$101)
Balance_of_Plant_Material_Sink
='Sink WBS'!$C$63:INDEX('Sink WBS'!$C$63:$C$81,COUNTSA('Sink WBS'!$C$63:$C$81)
Business_Rates_Property_Taxes_etc
='Faucet WBS'!$C$119:INDEX('Faucet WBS'!$C$119:$C$123,COUNTSA('Faucet WBS'!$C$119:$C$123)
Business_Rates_Property_Taxes_Sink_OM
='Sink WBS'!$C$331:INDEX('Sink WBS'!$C$331:$C$335,COUNTSA('Sink WBS'!$C$331:$C$335)
Capitalized_Overhead
='Faucet WBS'!$C$13:INDEX('Faucet WBS'!$C$13:$C$16,COUNTSA('Faucet WBS'!$C$13:$C$16)
Desired data to replace all of the named ranges definitions -- again in two columns, can be put anywhere in excel worksheet
Desired data to replace all of the named ranges definitions -- again in two columns, can be put anywhere in excel worksheet
