For each day i have created a sheet , i want to prepare consolidated sheet

[rajjindu]

I have created a sheet for each day of a month (31 sheets for may,sheetnames like 1,2,3...31). In the consolidated sheet , A1 colum contains the date like 1-may-2013, 2-may-2013 etc. now in consolidated sheet in column B i want to copy A5 cell from all the 31 sheets. pls help
thanks inadvance

[arlu1201]

Do you want to do it by formula or macro?

[newdoverman]

Where you want to have the answer you could enter a formula like this to sum all the values in A5 of all the sheets between Sheet1 and sheet31:

Please Login or Register  to view this content.

You could also select the cell where you want the answer and click on the Data tab, Consolidate, select Sum (or whatever function you want to use), click on the Reference field, then click the first sheet and select A5 then click ADD. Click on the next sheet (A5 should already be selected) and click ADD. Repeat for all the sheets then click OK. All the data in A5 should be consolidated in the cell that you selected for the answer.

VBA can also solve the problem but that isn't my area of interest.

[Jarko28]

Hi rajjindu

Try the attached sheet...change values in A5 on any sheet, go back to index and press the button...

Or, create a sheet in a copy of your workbook named index. Then press Alt+F11, and in the VBA window go to Insert and Choose module. In the module sheet paste in the following code:

Please Login or Register  to view this content.

Then press the green play button within the VBA editor window...

[rajjindu]

thanks for everyone of u who have tried , i got some other solution . thank u

[arlu1201]

rajjindu,

If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit.

If you got it from a link, you can put the link here.