Spin Box with Dates and Lookup
 

Hi,

I'm trying to design a spin box function which pulls in data from monthly
tabs, the month being determined by a spin box. I have monthly tabs Jan-10 to
Dec-10 all containing an identical table with different data in. The user
needs to be able to change the month of lookup using a spin box function but
I cant get it to work properly.

Thanks in anticipation.

Spin Box with Dates and Lookup
 

Hi,

You are not very clear about your question. Anyways, try this

Right click on the spin control box which you have drawn ad give the lower
and upper limits are 1 and 12 respectively. In the cell link box, select
any non blank cell (say H35) on the same worksheet. You will notice that as
you press the up/down arrow keys on the spin control box, the number in the
cell that you selected in the link box will change. Now in a separate box,
type the sheet names 'Jan-10, 'Feb-10 etc (say this is in $I$2:$I$11).
Please note that single quotes - have put them so that the entries do not
convert to dates.

Now in cell I35, type =INDIRECT("'"&INDEX($I$2:$I$11,H35,1)&"'!A1")

$I$2:$I$11 has the sheet names
A1 is the cell on any one of the 12 sheets from where you want to pick up
the value.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"JPDS" wrote in message
...
Hi,

I'm trying to design a spin box function which pulls in data from monthly
tabs, the month being determined by a spin box. I have monthly tabs Jan-10
to
Dec-10 all containing an identical table with different data in. The user
needs to be able to change the month of lookup using a spin box function
but
I cant get it to work properly.

Thanks in anticipation.

Spin Box with Dates and Lookup
 

Hi Ashish, thanks but its still not working. I'm getting a #REF! error in the
value pickup cell. Also how do I change the number from 1-12 to Jan-Dec?

Thanks


"Ashish Mathur" wrote:

Hi,

You are not very clear about your question. Anyways, try this

Right click on the spin control box which you have drawn ad give the lower
and upper limits are 1 and 12 respectively. In the cell link box, select
any non blank cell (say H35) on the same worksheet. You will notice that as
you press the up/down arrow keys on the spin control box, the number in the
cell that you selected in the link box will change. Now in a separate box,
type the sheet names 'Jan-10, 'Feb-10 etc (say this is in $I$2:$I$11).
Please note that single quotes - have put them so that the entries do not
convert to dates.

Now in cell I35, type =INDIRECT("'"&INDEX($I$2:$I$11,H35,1)&"'!A1")

$I$2:$I$11 has the sheet names
A1 is the cell on any one of the 12 sheets from where you want to pick up
the value.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"JPDS" wrote in message
...
Hi,

I'm trying to design a spin box function which pulls in data from monthly
tabs, the month being determined by a spin box. I have monthly tabs Jan-10
to
Dec-10 all containing an identical table with different data in. The user
needs to be able to change the month of lookup using a spin box function
but
I cant get it to work properly.

Thanks in anticipation.

Spin Box with Dates and Lookup
 

Ashish, I now have the lookup value working fine - thats brilliant! How do I
change the value of the spinbox number to correlate with the month name?

Thanks

"Ashish Mathur" wrote:

Hi,

You are not very clear about your question. Anyways, try this

Right click on the spin control box which you have drawn ad give the lower
and upper limits are 1 and 12 respectively. In the cell link box, select
any non blank cell (say H35) on the same worksheet. You will notice that as
you press the up/down arrow keys on the spin control box, the number in the
cell that you selected in the link box will change. Now in a separate box,
type the sheet names 'Jan-10, 'Feb-10 etc (say this is in $I$2:$I$11).
Please note that single quotes - have put them so that the entries do not
convert to dates.

Now in cell I35, type =INDIRECT("'"&INDEX($I$2:$I$11,H35,1)&"'!A1")

$I$2:$I$11 has the sheet names
A1 is the cell on any one of the 12 sheets from where you want to pick up
the value.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"JPDS" wrote in message
...
Hi,

I'm trying to design a spin box function which pulls in data from monthly
tabs, the month being determined by a spin box. I have monthly tabs Jan-10
to
Dec-10 all containing an identical table with different data in. The user
needs to be able to change the month of lookup using a spin box function
but
I cant get it to work properly.

Thanks in anticipation.

Spin Box with Dates and Lookup
 

Managed too work it out! In the end, thanks for your help!

"Ashish Mathur" wrote:

Hi,

You are not very clear about your question. Anyways, try this

Right click on the spin control box which you have drawn ad give the lower
and upper limits are 1 and 12 respectively. In the cell link box, select
any non blank cell (say H35) on the same worksheet. You will notice that as
you press the up/down arrow keys on the spin control box, the number in the
cell that you selected in the link box will change. Now in a separate box,
type the sheet names 'Jan-10, 'Feb-10 etc (say this is in $I$2:$I$11).
Please note that single quotes - have put them so that the entries do not
convert to dates.

Now in cell I35, type =INDIRECT("'"&INDEX($I$2:$I$11,H35,1)&"'!A1")

$I$2:$I$11 has the sheet names
A1 is the cell on any one of the 12 sheets from where you want to pick up
the value.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"JPDS" wrote in message
...
Hi,

I'm trying to design a spin box function which pulls in data from monthly
tabs, the month being determined by a spin box. I have monthly tabs Jan-10
to
Dec-10 all containing an identical table with different data in. The user
needs to be able to change the month of lookup using a spin box function
but
I cant get it to work properly.

Thanks in anticipation.

Spin Box with Dates and Lookup
 

You are welcome. Glad I could help

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"JPDS" wrote in message
...
Ashish, I now have the lookup value working fine - thats brilliant! How do
I
change the value of the spinbox number to correlate with the month name?

Thanks

"Ashish Mathur" wrote:

Hi,

You are not very clear about your question. Anyways, try this

Right click on the spin control box which you have drawn ad give the
lower
and upper limits are 1 and 12 respectively. In the cell link box, select
any non blank cell (say H35) on the same worksheet. You will notice that
as
you press the up/down arrow keys on the spin control box, the number in
the
cell that you selected in the link box will change. Now in a separate
box,
type the sheet names 'Jan-10, 'Feb-10 etc (say this is in $I$2:$I$11).
Please note that single quotes - have put them so that the entries do not
convert to dates.

Now in cell I35, type =INDIRECT("'"&INDEX($I$2:$I$11,H35,1)&"'!A1")

$I$2:$I$11 has the sheet names
A1 is the cell on any one of the 12 sheets from where you want to pick up
the value.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"JPDS" wrote in message
...
Hi,

I'm trying to design a spin box function which pulls in data from
monthly
tabs, the month being determined by a spin box. I have monthly tabs
Jan-10
to
Dec-10 all containing an identical table with different data in. The
user
needs to be able to change the month of lookup using a spin box
function
but
I cant get it to work properly.

Thanks in anticipation.