Data Validation - Range Problem
Git a problem with Data Validation.
In column A rows 1-10 hold values, then for row 11 I've set the DV to "look up" the range 1-10 for values to use - so far so good. When I copy A1 and paste downwards for the next 100 rows, the DV "adds" 1 on to the range ie for row 12 the range is specified as 1-11, for 13 1-12 and 14 1-13 etc. I've tried a straighforward paste, as well as a copy and format paste but it doesn't change. Other columns I've done this with are fine - I just can't see what the problem is with this column - I could rewrite it all out but I'd prefer to know whats going wrong... Thanks |
Data Validation - Range Problem
Hi Dave,
If you want to get always the range lets say A1:A11 when copying it you have to use $A$1:$A$11 If this was helpful please say yes "Dave Eade" wrote: Git a problem with Data Validation. In column A rows 1-10 hold values, then for row 11 I've set the DV to "look up" the range 1-10 for values to use - so far so good. When I copy A1 and paste downwards for the next 100 rows, the DV "adds" 1 on to the range ie for row 12 the range is specified as 1-11, for 13 1-12 and 14 1-13 etc. I've tried a straighforward paste, as well as a copy and format paste but it doesn't change. Other columns I've done this with are fine - I just can't see what the problem is with this column - I could rewrite it all out but I'd prefer to know whats going wrong... Thanks |
Data Validation - Range Problem
No it doesn'e work.
If I have $A$1:$A$11 in A12 and copy to A20, when I open the Data Validation Window it has changed the range to $A$1:$A$19 "Eduardo" wrote: Hi Dave, If you want to get always the range lets say A1:A11 when copying it you have to use $A$1:$A$11 If this was helpful please say yes "Dave Eade" wrote: Git a problem with Data Validation. In column A rows 1-10 hold values, then for row 11 I've set the DV to "look up" the range 1-10 for values to use - so far so good. When I copy A1 and paste downwards for the next 100 rows, the DV "adds" 1 on to the range ie for row 12 the range is specified as 1-11, for 13 1-12 and 14 1-13 etc. I've tried a straighforward paste, as well as a copy and format paste but it doesn't change. Other columns I've done this with are fine - I just can't see what the problem is with this column - I could rewrite it all out but I'd prefer to know whats going wrong... Thanks |
Data Validation - Range Problem
Hi Dave,
It works for me please be sure that you choose custom and then enter the formula as well check that you have the $ before and after the A "Dave Eade" wrote: No it doesn'e work. If I have $A$1:$A$11 in A12 and copy to A20, when I open the Data Validation Window it has changed the range to $A$1:$A$19 "Eduardo" wrote: Hi Dave, If you want to get always the range lets say A1:A11 when copying it you have to use $A$1:$A$11 If this was helpful please say yes "Dave Eade" wrote: Git a problem with Data Validation. In column A rows 1-10 hold values, then for row 11 I've set the DV to "look up" the range 1-10 for values to use - so far so good. When I copy A1 and paste downwards for the next 100 rows, the DV "adds" 1 on to the range ie for row 12 the range is specified as 1-11, for 13 1-12 and 14 1-13 etc. I've tried a straighforward paste, as well as a copy and format paste but it doesn't change. Other columns I've done this with are fine - I just can't see what the problem is with this column - I could rewrite it all out but I'd prefer to know whats going wrong... Thanks |
Data Validation - Range Problem
No, and it's only in this one column - when I use data validation in other
columns it's fine. It's as if there is a formula embedded in the data validation rules that always adds 1 onto the last cell reference when pasted. "Eduardo" wrote: Hi Dave, It works for me please be sure that you choose custom and then enter the formula as well check that you have the $ before and after the A "Dave Eade" wrote: No it doesn'e work. If I have $A$1:$A$11 in A12 and copy to A20, when I open the Data Validation Window it has changed the range to $A$1:$A$19 "Eduardo" wrote: Hi Dave, If you want to get always the range lets say A1:A11 when copying it you have to use $A$1:$A$11 If this was helpful please say yes "Dave Eade" wrote: Git a problem with Data Validation. In column A rows 1-10 hold values, then for row 11 I've set the DV to "look up" the range 1-10 for values to use - so far so good. When I copy A1 and paste downwards for the next 100 rows, the DV "adds" 1 on to the range ie for row 12 the range is specified as 1-11, for 13 1-12 and 14 1-13 etc. I've tried a straighforward paste, as well as a copy and format paste but it doesn't change. Other columns I've done this with are fine - I just can't see what the problem is with this column - I could rewrite it all out but I'd prefer to know whats going wrong... Thanks |
All times are GMT +1. The time now is 02:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com