Dynamic named ranges and the Indirect() function
I have a workbook containing two worksheets, named "Sheet2" and "Summary (0)".
"Summary (0)" contains locally defined dynamic named ranges which identify a lookup table.
"Sheet2" contains a cell which identifies the name of the worksheet "Summary (0)". It simply contains the text string "Summary (0)".
"Sheet2" also contains a lookup value to be identified in the table in "Summary (0)".
I was going to use the INDIRECT() function to obtain the name of the worksheet which contains the lookup table, but I am informed that you cannot use a dynamic named range within an INDIRECT command.
The problem is illustrated here:
I am informed from elsewhere that there is supposedly a workaround for this, by the following cryptic advice:
"You would need to use Evaluate call (ie old XLM) via a Name to utilise INDIRECT - then use the Evaluate Names in your formulae."
Does anyone out there know what this means? Is there another approach that would workaround it? VBA not out of the question.
With kind regards