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]

Hi,

Since A19 has a drop down, that means you have DV set to "In-cell dropdown" referencing a Range in the formula box.
In my sample below H1:H26 is the range reference for the DV "In-cell dropdown", set H1=G1, then copy H2 formula down to H26:

Book1
	G	H
1	5	5
2		6
3		7
4		8
5		9
6		10
7		11
8		12
9		13
10		14
11		15
12		16
13		17
14		18
15		19
16		20
17		21
18		22
19		23
20		24
21		25
22		26
23		27
24		28
25		29
26		30
Sheet64

Cell Formulas
Range	Formula
H1	=G1
H2	=H1+1

 

[plpl78]

Not sure i explained this right. When i go into A19 and i hit the drop down menu the options i have go from 0 to 26. In G1 it says its week 11 so i would select 11 in the drop down menu

What i want is for it to be set up so the drop down menu option would start what ever week is listed in G1

So if i hit the drop down menu it would start at 11 and not 0. On 6-10-2018 it will be week 12 so G1 will show 12 then i would want the drop down menu to start at 12. Thanks for the help by the way

 

[jtakw]

Not sure i explained this right. When i go into A19 and i hit the drop down menu the options i have go from 0 to 26. In G1 it says its week 11 so i would select 11 in the drop down menu

What i want is for it to be set up so the drop down menu option would start what ever week is listed in G1

So if i hit the drop down menu it would start at 11 and not 0. On 6-10-2018 it will be week 12 so G1 will show 12 then i would want the drop down menu to start at 12. Thanks for the help by the way

That's EXACTLY what my suggestion in Post #2 will do.

 

[Tetra201]

Assuming that range H1:H26 is populated with numbers from 1 to 26, try using the following formula in the Source field for DV:

=OFFSET($H$1:$H$26,$G$1-1,0,27-$G$1,1)

 

[plpl78]

i think the issue is how i have it set up the data being used in the drop down menu comes from sheet 2 cells C2 to c27

 

[jtakw]

That's just a matter of referencing G1 from Sheet2 C2:

Book1
	G
1	11
Sheet1

Book1
	C
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	27
19	28
20	29
21	30
22	31
23	32
24	33
25	34
26	35
27	36
Sheet2

Cell Formulas
Range	Formula
C2	=Sheet1!G1
C3	=C2+1

 

[plpl78]

Ok on sheet 2 in cell C12 i did =Sheet1!G1 and that gave me 11 which is what is should be then in C13 i did =C13+1 and that gave me 12 which is also right

But when i go to sheet one and hit the drop down arrow in a19 the dropdown still shows me the first number in the list which is 0

I must be doing something wrong lol

 

[jtakw]

Wait a minute, does your DV source start at C2 or C12 of Sheet2?

 

[plpl78]

on Sheet 2 is starts a C1 which is blank then goes to C27 which is twenty six then i named the range so i could use it in the drop downs i have on sheet one in column a