Hi,
I have a workbook that is sent to me every quarter. It contains 3 worksheets that I cannot change and can only use as a data source. I have been granted permission to establish named ranges on each worksheet. That way, I can reference the named ranges more easily in my summary report on sheet 4. I have worked through all of the dynamic ranges I need and they work. So there ais a group of values that I can reference using dynamic ranges on each sheet -- call them GOLD, SILVER and STANDARD.
On my summary sheet, various formula work to state current value, expected return, future value, etc. Here's the snag--The summary sheet contains varying numbers of rows up to 650 and requires me to edit, copy and paste the formulas manually in three columns for each group.
The records are grouped by State and plan type and are not presented in a regular, recurring interval. So 1 row may be Silver, the next may be Gold, the next three may be Standard. Never a repeating sequence.
Is there a way that I can use a formula to reference the named range for each dynamic range? I currently have formulas that work, like:
And the only changes between columns of formulas are the references to the ranges for Silver, Gold and Standard. So is there a way to have a formula to switch between named ranges according to the name of the product?
TIA
I have a workbook that is sent to me every quarter. It contains 3 worksheets that I cannot change and can only use as a data source. I have been granted permission to establish named ranges on each worksheet. That way, I can reference the named ranges more easily in my summary report on sheet 4. I have worked through all of the dynamic ranges I need and they work. So there ais a group of values that I can reference using dynamic ranges on each sheet -- call them GOLD, SILVER and STANDARD.
On my summary sheet, various formula work to state current value, expected return, future value, etc. Here's the snag--The summary sheet contains varying numbers of rows up to 650 and requires me to edit, copy and paste the formulas manually in three columns for each group.
The records are grouped by State and plan type and are not presented in a regular, recurring interval. So 1 row may be Silver, the next may be Gold, the next three may be Standard. Never a repeating sequence.
Is there a way that I can use a formula to reference the named range for each dynamic range? I currently have formulas that work, like:
VBA Code:
=INDEX(Silver_Plan,MATCH(E11,Silver_St,0),MATCH(D11,Silver_Type,0)+4)*$F11And the only changes between columns of formulas are the references to the ranges for Silver, Gold and Standard. So is there a way to have a formula to switch between named ranges according to the name of the product?
TIA
