Help with monthly reporting

[SewermanAZ]

I am looking for a way to streamline my monthly reports. I am using excel 2007 and Windows Vista.

Currently I enter data daily into a workbook that has 12 spreadsheets in it (1 for each month). This data is just basic equipment run-times and some daily water flow. Each month I have about 4 reports that this data gets plugged into. I do know how to tell a cell on my reports to pull data from my monthly spreadsheets, My question is this: Is there a way that I can change 1 or 2 reference points on my reporting spreadsheets so that the report will know to switch all of my cells that are currently being pulled from lets say January 2011 to instead pull from the February 2011 spreadsheet.

 

[Akashwani]

Hi,

Welcome to MrExcel.

I'm not an expert so I don't have a solution for you, just an idea.

If your sheets have the same format you could try using INDIRECT.
You could set up a Data Validation drop down list and place it into a particular cell (A2 in my sample data). You can then refer to that cell for your monthly reporting......

Excel Workbook
	A	B	C
1	Data1	Data2	Data3
2	Car	Bus	Bike
3	Red	Green	Blue
4	Bill	Bob	Tom
5	DP 4	DP 4	DP 4
January

Excel Workbook
	A	B	C
1	Data1	Data2	Data3
2	Red	Green	Blue
3	1234567	1234568	1234569
4	Bill	Bob	Tom
5	test	TEST2	TEST3
February

Excel Workbook
	A	B	C	D	E	F	G
1	Month	*	Data1	Data2	Data3	*	List
2	January	*	Car	Bus	Bike	*	January
3	*	*	Red	Green	Blue	*	February
4	*	*	Bill	Bob	Tom	*	*
5	*	*	DP 4	DP 4	DP 4	*	*
Report

Excel Workbook
	A	B	C	D	E	F	G
1	Month	*	Data1	Data2	Data3	*	List
2	February	*	Red	Green	Blue	*	January
3	*	*	1234567	1234568	1234569	*	February
4	*	*	Bill	Bob	Tom	*	*
5	*	*	test	TEST2	TEST3	*	*
Report

I don't know how you can structure the formulas so that you can apply it to one cell and copy it across and down, sorry.

I hope this helps.

Ak

 

[SewermanAZ]

Ok, I understand what you have created there and I will try and apply it to one of my reports.

 

[SewermanAZ]

I tried this process and all I get in my destination cell on my report is #Ref. I used the same indirect formula that you had and I built the Data validation drop down list, but maybe I built the list wrong. which cell did you reference on the January and February spreadsheets when you were creating the drop down list?

 

[Akashwani]

Hi,

The list of sheet names for the drop down list can be anywhere, I put the List in cells G2:G3 on sheet Report, then created the Data Validation in A2 from that List.
You must ensure that your List of sheet names matches the sheet names exactly. I would suggest not using January11 as a sheet name, as I had problems when entering that into a cell, it gave me a true date.

I hope that helps.

Ak