formula for drop down menu?

[plpl78]

IN cell G1 i have a number that changes every two weeks currently that number is eleven.

In A19 i have a drop down menu starting at one and going to twenty six.

Is there a way i can set the drop down menu to start at the current number based off the number in G1?

Example of the data in G1 is five then when i click on the drop down in A19 it would start at five instead of 0

Thank you for all your help

 

[jtakw]

Then do Exactly as I illustrated in Post #7 above.

Put =Sheet1!G1 in C2
Put =C2+1 in C3
Copy C3 formula all the way down to C27

Now whenever G1 of Sheet1 changes, your drop down in A19 Will Start with That number.

 

[jtakw]

May be you mean you Always want the "revolving" numbers 1 to 26, if that's that case use the following updated formulas, still follow the instructions in my post #11 above:

Book1
	C
1
2	11
3	12
4	13
5	14
6	15
7	16
8	17
9	18
10	19
11	20
12	21
13	22
14	23
15	24
16	25
17	26
18	1
19	2
20	3
21	4
22	5
23	6
24	7
25	8
26	9
27	10
Sheet2

Cell Formulas
Range	Formula
C2	=IF(Sheet1!G1<=26,Sheet1!G1,1)
C3	=IF(C2+1>26,1,C2+1)

But wondering, what's supposed to happen when Week 27 and higher comes around?

 

[plpl78]

at the end of week 26 it will revert back to week 1 i just need to update a different section on sheet 2 for the update to happen put that part i understand lol

 

[jtakw]

So is my formula in Post #12 working for you?

 

[plpl78]

Yes and no. When i did as you said it got the drop down working like i wanted but it broke another drop down menu. When u go into A19 and select the current week say 11 it was set up originally as eleven. because i changed it to 11 it broke the INDIRECT i had set up

The way it works now is you select the week so in this case eleven it then does the indirect, it finds the named ranged 11 on sheet 2 and displays two weeks worth of dates in the 2nd drop down (which is now the broken one)

I tried to rename the range from eleven to 11 but it wouldn't let me