Formula for different sheets

[Joey]

Hi

I would like to create a formula that relates to different sheets in m
workbook when a selection to it's name is made.

The book contains 13 sheets.
Sheet 1 - sheet where I do the lookups etc
Sheets 2 to 13 are the 12 months of the year.

Could I create a formula in Sheet 1 that would automatically refer to
Sheet with the name of the month I'm looking for if I created a dro
down list.

Eg,
Sheet 1 in A2 = drop down list with month names
A4 - a lookup or IF formula finding data from one of the other sheets
The data in each sheet is exactly the same.

Many thanks
Joe

 

[Andy B]

Hi

If you were to have Jan in cell A1, then this would return the cell A10 from
sheet Jan

=INDIRECT(A1&"!"&"A10")

Hope this helps.

 

[Jan Karel Pieterse]

Hi Joey,

I would like to create a formula that relates to different sheets in my
workbook when a selection to it's name is made.

Check out the INDIRECT worksheet function.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[BrianB]

Say you set a combobox to change cell A2 with the selection number.
Range E1:E12 contains the month list to which the combobox refers.
Cell A1 has formula :- =INDEX($E$1:$E$12,A2) to return the mont
text.
This formula will return the value in cell a1 of the sheet with th
same month name :-
=INDIRECT(CONCATENATE(A1,"!","A1")

 

[Joey]

Brilliant!!!

Thanks guy