Is this possible? I dont know where to start. 1

[vbcad]

I have a spreadsheet for some workouts. What i am trying to do is make the previous selection for each list be unavailable in the following weeks sheet. for example in Week1/Day 1 I can pick "Front Dumbbell Lateral Raise" from the list in the first section. Then on Week 2/Day 1 I would like "Front Dumbbell Lateral Raise" to be unavailable for selection as to not repeat the exercise and only be able to select one of the other two exercises from my data validation list.

 

[SkipVought]

hi,

How about posting some sample data that illustrates the issue (a table with headings), also explaining exactly what results you expect to see.

Shooting from the hip, I'd lean toward MS Query, but there are probably other methods that could work.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[vbcad]

Where can i attach the file? I see the upload button is missing

 

[SkipVought]

Copy 'n' paste

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[vbcad]

test for url

 

[SkipVought]

Copy 'n' paste, PLEASE!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[zelgar]

There's probably a more efficient way, but here's one way of doing it:
make a list of all of the different combinations (e.g., for a list with items from 1 - 10), you'd get the following
ALL DATA: 1 2 3 4 5 6 7 8 9 10
1 Selected: 2 3 4 5 6 7 8 9 10
2 Selected: 1 3 4 5 6 7 8 9 10
3 Selected: 1 2 4 5 6 7 8 9 10
4 Selected: 1 2 3 5 6 7 8 9 10
5 Selected: 1 2 3 4 6 7 8 9 10
6 Selected: 1 2 3 4 5 7 8 9 10
7 Selected: 1 2 3 4 5 6 8 9 10
8 Selected: 1 2 3 4 5 6 7 9 10
9 Selected: 1 2 3 4 5 6 7 8 10
10 Selected: 1 2 3 4 5 6 7 8 9

each list is given a name for only the data, Don't include the header) (e.g., list1, list2, list3 ...)
Data Validation is used with a LIST. For the 1st choice, a list with all of the choices
For the dependent list, use Data Validation with a LIST using the following formula
=CHOOSE(INDEX(A1:A10,A24),list1,list2,list3,list4,list5,list6,list7,list8,list9,list10)

 

[byundt]

I assume you are doing this in more than one cell and more than one worksheet. If so, setting up named ranges for the data validation dropdown lists will get complicated.

As an alternative, how about using Conditional Formatting to highlight the choice if it was the same as the previous week? You can do so with a formula for cell H1 like:
=AND(H1<>"",PreviousChoice(H1)=H1)
You can apply this Conditional Formatting to other cells, and the references will automatically adjust. You can even install the Conditional Formatting on a template worksheet that you copy and insert for each successive week.

PreviousChoice is a user-defined function in a regular module sheet such as Module1.

Function PreviousChoice(cel As Range)
'Returns the value of cel from the preceding worksheet
Dim i As Long
i = cel.Worksheet.Index
If i > 1 Then
    PreviousChoice = Worksheets(i - 1).Range(cel.Address).Value
End If
End Function

If the Conditional Formatting does not appear to be working, you may put the code in a worksheet code pane rather than a regular module sheet. Instead of right-clicking the sheet tab and choosing View Code..., you should ALT + F11 to open the VBA Editor, then Insert...Module to create a blank regular module sheet. Having done so, paste the code there and test your conditional formatting once again.

Brad