Spin Box with Dates and Lookup

  • Thread starter Thread starter JPDS
  • Start date Start date
J

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.
 
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
 
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, 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
 
Back
Top