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
- Accountant Pays Clients Tax 573 11
- Are bankruptcy restrictions order becoming more common? 181 4
- When will Private Principal Residence Relief cut will start? 299 6
- Charge out rates in London? 165 2
- Accounting jobs in the UK 58 1
- Negative retained earnings & Dividends 113 3
- Snotty HMRC letter - continued 516 15
- pre letting expenses 148 2
- Expat working back in UK 94 3
- Are contractor travel expenses 82 1
- Who owns SAGE Data? 402 8
- Audit Exemption new rules 273 6
- Property versus Trade in a Ltd Company 98 3
- change of paye software 509 13
- Which software for practice books? 467 19
- Several employments - large tax bill remaining 242 4
- CIS to 5/10/13 and HMRC Computer issue 529 2
- share capital calculation 273 14
- Dispensation pointless ? 218 3
- Renting property to yourself 460 12
- Desperately seeking Susan, Sam or anyone with any other name 736
- Experience with Islamic investor following Sharia-compliant rules? 475
- Darwinpay Partnership Assessment 289
- How to format a stock book 252
- S464A Charge to tax: conferring benefit & BIK 206
- Foreign income on Self Assesment 202
- Tax efficient holding company structure 190
- Investment property company accounts template 185
- Outlook 2007 with business contact manager not loading on new laptop 178
- auto enrolment - calculation of first pension deduction 163