Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with Data Validation
Hi, I know how to do Data Validation but I'm coming across a problem that I'm
really stumped with. I've looked around to see if anyone else has had this problem but can't find any solutions at the moment - especially as "INDIRECT" doesn't seem to be working for me in this instance. I've got a worksheet with formulas on that links to another worksheet to pull names of staff members in. These staff members all have an associated shift which they work on. The data looks like this... Shift Name ES Tester1 ES Tester2 LS Tester 3 LS Tester4 NS Tester5 The names are being brought in using the formulas correctly and the associated named ranges (ESTesters, LSTesters) are all working properly using OFFSET to calculate where to start and end in the list. The whole spreadsheet has a cell named "AllocationShift" on the front page, where users can state "ES", "LS" or "NS". Now if I specify a cell to have Data Validation with "INDIRECT(AllocationShift & 'Testers')" Excel says that the Source evaluates to an error. Putting the same formula in a cell and stepping through it looks like it doesn't like "INDIRECT('ESTesters')" - that's certainly the step where it gives me #REF. Now I think this means that it can't find the named range but I'm not sure. This formula used to work when i was importing the data via macros but that's since stopped because the document is shared and we were getting too many "automation problems" through different versions of Excel. I also suspect that our network may have been causing some of the problems. Anyway - this error might be caused that the named range "ESTesters" contains formulas, rather than text, but again I'm unsure. If anyone has any advice, or wants to see code then give me a shout! Thanks very much - this forum has provided me with excellent support before now, and I'm not expecting this to change any time soon :-) George |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with Data Validation
Just a little further information...
If I put "=ESTesters" into the DV it works :-( It's just I can't make the list validation work when I want to change the named range based on the AllocationShift cell :-( "George" wrote: Hi, I know how to do Data Validation but I'm coming across a problem that I'm really stumped with. I've looked around to see if anyone else has had this problem but can't find any solutions at the moment - especially as "INDIRECT" doesn't seem to be working for me in this instance. I've got a worksheet with formulas on that links to another worksheet to pull names of staff members in. These staff members all have an associated shift which they work on. The data looks like this... Shift Name ES Tester1 ES Tester2 LS Tester 3 LS Tester4 NS Tester5 The names are being brought in using the formulas correctly and the associated named ranges (ESTesters, LSTesters) are all working properly using OFFSET to calculate where to start and end in the list. The whole spreadsheet has a cell named "AllocationShift" on the front page, where users can state "ES", "LS" or "NS". Now if I specify a cell to have Data Validation with "INDIRECT(AllocationShift & 'Testers')" Excel says that the Source evaluates to an error. Putting the same formula in a cell and stepping through it looks like it doesn't like "INDIRECT('ESTesters')" - that's certainly the step where it gives me #REF. Now I think this means that it can't find the named range but I'm not sure. This formula used to work when i was importing the data via macros but that's since stopped because the document is shared and we were getting too many "automation problems" through different versions of Excel. I also suspect that our network may have been causing some of the problems. Anyway - this error might be caused that the named range "ESTesters" contains formulas, rather than text, but again I'm unsure. If anyone has any advice, or wants to see code then give me a shout! Thanks very much - this forum has provided me with excellent support before now, and I'm not expecting this to change any time soon :-) George |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with Data Validation
this error might be caused that the named range "ESTesters"
contains formulas, rather than text, Close! If ESTesters is a dynamic range calculated by using OFFSET then: =INDIRECT(ESTesters) = #REF! The problem is INDIRECT's argument *must* be a TEXT representation of a valid reference. In this case the argument evaluates to the OFFSET formula causing the error. -- Biff Microsoft Excel MVP "George" wrote in message ... Hi, I know how to do Data Validation but I'm coming across a problem that I'm really stumped with. I've looked around to see if anyone else has had this problem but can't find any solutions at the moment - especially as "INDIRECT" doesn't seem to be working for me in this instance. I've got a worksheet with formulas on that links to another worksheet to pull names of staff members in. These staff members all have an associated shift which they work on. The data looks like this... Shift Name ES Tester1 ES Tester2 LS Tester 3 LS Tester4 NS Tester5 The names are being brought in using the formulas correctly and the associated named ranges (ESTesters, LSTesters) are all working properly using OFFSET to calculate where to start and end in the list. The whole spreadsheet has a cell named "AllocationShift" on the front page, where users can state "ES", "LS" or "NS". Now if I specify a cell to have Data Validation with "INDIRECT(AllocationShift & 'Testers')" Excel says that the Source evaluates to an error. Putting the same formula in a cell and stepping through it looks like it doesn't like "INDIRECT('ESTesters')" - that's certainly the step where it gives me #REF. Now I think this means that it can't find the named range but I'm not sure. This formula used to work when i was importing the data via macros but that's since stopped because the document is shared and we were getting too many "automation problems" through different versions of Excel. I also suspect that our network may have been causing some of the problems. Anyway - this error might be caused that the named range "ESTesters" contains formulas, rather than text, but again I'm unsure. If anyone has any advice, or wants to see code then give me a shout! Thanks very much - this forum has provided me with excellent support before now, and I'm not expecting this to change any time soon :-) George |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation problem | Excel Discussion (Misc queries) | |||
Problem with Data Validation | Excel Discussion (Misc queries) | |||
Data Validation problem | Excel Discussion (Misc queries) | |||
Data validation problem | Excel Discussion (Misc queries) | |||
Data validation problem | Excel Worksheet Functions |