validation list

[Guest]

I have created a spreadsheet that has worksheets which need to filled in for
every day of the month. There is a spreadsheet for each month of the year.
On each worksheet for each day there is a validation drop down list which
takes its information from a list on the bottom of each page. There are two
columns beside the drop down list that use drop down for a vlookup. The
problem i have is that the information will change from time to time and the
list for the drop down and vlookup will be updated. When the lists need to
be updated I have to go to each individual sheet from when it needs to be
updated, unlock the sheet, insert the new items into the list or update the
current lists information, resort the list and then reprotect the worksheet.
Is there any easier way of doing this?

 

[Guest]

I gather the DV list and the vlookup table are the same for each worksheet.
With that assumption, you should be able to make the change on just one
worksheet and reference that sheet on all the others.
For the vlookup, this is easy... instead of
=vlookup(a1,A$200:B$230,2,false), use
=vlookup(a1,Sheet1!A$200:B$230,2,false). That explicitly uses the
table_array from sheet1.
For the data validation, you have to define a named range . If, for
instance, the valid entries are Sheet1!A$200:A$230, select that range, then
Insert > Name > Define. Enter a name you'll use to reference the range (ex:
DV_List), then click OK.
Then in the cells you want validated, Data > Validation, choose List then in
the text box enter =DV_List (or whatever name you've chosen).
Now you should be able to make the changes on just the one sheet and have
them apply to the others as well. Be aware that this would change any
lookups for prior months as well, unless you've first locked down the old
values (copy & paste values).

 

[MartinW]

Hi enyaw,

If I understand you correctly all you have to do is lodge your
validation lists and vlookup data in a seperate sheet (instead of in each
sheet)
and have all your worksheets reference that sheet.

HTH
Martin

 

[Guest]

What I am looking for is to stop the changes applying to the previous sheets
and allow the rest of the sheets to use the new data. That is why the DV list
and vlookup table are on each sheet. So when I update a list on the 10th of
the month I need the list to update on the days for the rest of the month but
not update for the previous days so as not to change any prior values.