If Formula

[ritesh28880]

Hi,

I have 12 sheets which are named as Jan, Feb, ..... I want to use If condition for a drop down box which consists months from Jan - Dec. Using the vale from drop box I want to fetch data from that particular months sheets.

 

[spanspace]

Excel Workbook
	A	B
1	Jan	Select Month
2
3
4	Data 1	Data 2
5	1	2
6	2	3
LookUp Sheet

Excel 2007

Cell Formulas
Range	Formula
A5	=IF($A$1="Jan",Jan!A2,IF($A$1="Feb",Feb!A2,IF($A$1="Mar",Mar!A2,IF($A$1="Apr",Apr!A2,IF($A$1="May",May!A2,IF($A$1="Jun",Jun!A2,IF($A$1="Jul",Jul!A2,IF($A$1="Aug",Aug!A2,IF($A$1="Sep",Sep!A2,IF($A$1="Oct",Oct!A2,IF($A$1="Nov",Nov!A2,IF($A$1="Dec",Dec!A2,""))))))))))))
A6	=IF($A$1="Jan",Jan!A3,IF($A$1="Feb",Feb!A3,IF($A$1="Mar",Mar!A3,IF($A$1="Apr",Apr!A3,IF($A$1="May",May!A3,IF($A$1="Jun",Jun!A3,IF($A$1="Jul",Jul!A3,IF($A$1="Aug",Aug!A3,IF($A$1="Sep",Sep!A3,IF($A$1="Oct",Oct!A3,IF($A$1="Nov",Nov!A3,IF($A$1="Dec",Dec!A3,""))))))))))))
B5	=IF($A$1="Jan",Jan!B2,IF($A$1="Feb",Feb!B2,IF($A$1="Mar",Mar!B2,IF($A$1="Apr",Apr!B2,IF($A$1="May",May!B2,IF($A$1="Jun",Jun!B2,IF($A$1="Jul",Jul!B2,IF($A$1="Aug",Aug!B2,IF($A$1="Sep",Sep!B2,IF($A$1="Oct",Oct!B2,IF($A$1="Nov",Nov!B2,IF($A$1="Dec",Dec!B2,""))))))))))))
B6	=IF($A$1="Jan",Jan!B3,IF($A$1="Feb",Feb!B3,IF($A$1="Mar",Mar!B3,IF($A$1="Apr",Apr!B3,IF($A$1="May",May!B3,IF($A$1="Jun",Jun!B3,IF($A$1="Jul",Jul!B3,IF($A$1="Aug",Aug!B3,IF($A$1="Sep",Sep!B3,IF($A$1="Oct",Oct!B3,IF($A$1="Nov",Nov!B3,IF($A$1="Dec",Dec!B3,""))))))))))))

A1 is a Data Validation Drop down that points to a list in a column I hid.

 

[Magriza]

Alternatively you could use a combination of INDIRECT and ADDRESS as ADDRESS accepts an argument for the sheet name. As INDIRECT is a volatile formula you might want to use it sparingly so it depends on how much data you're looking at pulling from the other sheets.