Spin Box with Dates and Lookup

[JPDS]

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.

 

[Ashish Mathur]

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]

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

 

[JPDS]

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

 

[JPDS]

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

 

[Ashish Mathur]

You are welcome. Glad I could help

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com