identify list used in a cell

[Don]

I have a spreadsheet used by mult people and we have three sections. I have
created lists for each section and did a Data / validate / list to give them
the correct options. Turns out that some have copied from one section to
another and now the lists are mixed in each section.

Is there a formula to identify the list used in a cell? or judging on the
list used, can I change the color of the cell?

I can identify the drop down's selected that are in the wrong section but
they share some names that would make it impossible to identify unless I have
a tool to identify which list it is using.

thanks

 

[Gary Brown]

I suggest you put the validation lists on a 'VERY HIDDEN' worksheet so the
clients can't get to/find the lists and change them.

To do that...
Assumption: The name of the original worksheet with your validation
lists is 'SHEET1'.
(1) Add a worksheet.
Assumption: Name of new worksheet is 'SHEET4'.
(2) Put your validation lists on the new worksheet.
(3) Re-create your validations on SHEET1 using the following examples...

' Data Validation using a List from another worksheet or workbook

' Using a List from
' On the Data Validation form,
' on the Settings tab,
' In the Validation Criteria area,
' In the 'Allow' dropdown,
' Select 'LIST'
' When the List is in ANOTHER WORKSHEET in the same workbook:
' The syntax in the 'SOURCE' RefEdit box should be
' something like...
' =INDIRECT("'Sheet4'!A1:A5")
'
' When the List is in a WORKSHEET in ANOTHER WORKBOOK:
' The syntax in the 'SOURCE' RefEdit box should be
' something like...
' =INDIRECT("'C:\Temp\[Test.xls]Sheet4'!A1:A5")

(4) Hide SHEET4.
(a) Get into the Visual Basic Editor
- Tools > Macro > Visual Basic Editor
(b) View > Properties Window
(c) In the 'Project' view (should be on the left side of the VBA area)
highlight SHEET4
(d) In the 'Properties' view (should be on the lower left side of the
VBA area) go to the very bottom of the list. You should see 'Visible' in the
box on the left and '-1 - xlSheetVisible' in the box to it's right.
(e) The box with '-1 - xlSheetVisible' is a drop-down. Select '2 -
xlSheetVeryHidden'.
(f) Get out of the Visual Basic Editor
- File > Close and Return to Microsoft Excel

Now, unless they REALLY know what they're doing, your clients won't be able
to see the worksheet. YOU, however, now know that you can easily make it
visible again to add/change/delete lists by simply going back into the VBA
Editor and changing the 'Visible' property of SHEET4 to '-1 - xlSheetVisible'.
Your lists should now stay intact!

Hope this helps,
Sincerely,
Gary Brown
--------------------------------------------------

 

[Don]

actualy, I have the list sheet protected but could hide also. The people are
taking a cell in one section and coping it to another section thus taking its
Data/validation list with it. I want it to be obvious to identify when this
has happened and have it fixed.

I suggest you put the validation lists on a 'VERY HIDDEN' worksheet so the
clients can't get to/find the lists and change them.

To do that...
Assumption: The name of the original worksheet with your validation
lists is 'SHEET1'.
(1) Add a worksheet.
Assumption: Name of new worksheet is 'SHEET4'.
(2) Put your validation lists on the new worksheet.
(3) Re-create your validations on SHEET1 using the following examples...

' Data Validation using a List from another worksheet or workbook

' Using a List from
' On the Data Validation form,
' on the Settings tab,
' In the Validation Criteria area,
' In the 'Allow' dropdown,
' Select 'LIST'
' When the List is in ANOTHER WORKSHEET in the same workbook:
' The syntax in the 'SOURCE' RefEdit box should be
' something like...
' =INDIRECT("'Sheet4'!A1:A5")
'
' When the List is in a WORKSHEET in ANOTHER WORKBOOK:
' The syntax in the 'SOURCE' RefEdit box should be
' something like...
' =INDIRECT("'C:\Temp\[Test.xls]Sheet4'!A1:A5")

(4) Hide SHEET4.
(a) Get into the Visual Basic Editor
- Tools > Macro > Visual Basic Editor
(b) View > Properties Window
(c) In the 'Project' view (should be on the left side of the VBA area)
highlight SHEET4
(d) In the 'Properties' view (should be on the lower left side of the
VBA area) go to the very bottom of the list. You should see 'Visible' in the
box on the left and '-1 - xlSheetVisible' in the box to it's right.
(e) The box with '-1 - xlSheetVisible' is a drop-down. Select '2 -
xlSheetVeryHidden'.
(f) Get out of the Visual Basic Editor
- File > Close and Return to Microsoft Excel

Now, unless they REALLY know what they're doing, your clients won't be able
to see the worksheet. YOU, however, now know that you can easily make it
visible again to add/change/delete lists by simply going back into the VBA
Editor and changing the 'Visible' property of SHEET4 to '-1 - xlSheetVisible'.
Your lists should now stay intact!

Hope this helps,
Sincerely,
Gary Brown
--------------------------------------------------

I have a spreadsheet used by mult people and we have three sections. I have
created lists for each section and did a Data / validate / list to give them
the correct options. Turns out that some have copied from one section to
another and now the lists are mixed in each section.

Is there a formula to identify the list used in a cell? or judging on the
list used, can I change the color of the cell?

I can identify the drop down's selected that are in the wrong section but
they share some names that would make it impossible to identify unless I have
a tool to identify which list it is using.

thanks