ASAP HELP!! Data from multiple sheets

[Taffalaffa]

I need help. I am esentially gathering information from 65 different time cards and compiling the different data into a recap sheet. How can I create a forumla that will automatically take the next sheets data? On each individual sheet the data is in the same boxes (hours are always in J28, Dollars in J29, Labor Equipment in J30). I am trying to use:
=+'1'!$J$28 but I am not able to copy this forumla and have it automatically make it =+'2'!$J$29. Please help me! I have 65 time cards I need to get info from and four fields per time sheet and I don;t want to enter each formula manually. Thanks!

 

[p45cal]

If the sheets really are named "1","2" "3" etc. then:

Excel Workbook
	D
6	11
7	12
8	13
9	#REF!
10	#REF!
Sheet22

Copy D6 formula into a cell and copy down for consecutively named sheets.
Values in J28 of my sheets 1, 2 and 3 were 11,12 and 13 respectively.
I didn't have sheets 4,5,6 etc, so they show #REF!

If you're wanting values from consecutively named sheets to go across rather than down the sheet, post back (or you could try replacing ROW with COLUMN in my formula and copy to the right).

 

[Taffalaffa]

I am confused by your answer. I am just getting #REF. What is supposed to go int the "" portion of your formula? (And thank you SOOO much for the help. I am totally under the gun here!)

 

[cgcamal]

Hi Taffalaffa,

Try to upload a sample file or snapshot to understand better where do you need the output and where they come from.

Regards.

 

[p45cal]

I am confused by your answer. I am just getting #REF. What is supposed to go int the "" portion of your formula? (And thank you SOOO much for the help. I am totally under the gun here!)

There isn't a "" portion, there's a "'" portion. (A single quote mark inbetween double quote marks).
Just copy and paste from this thread.

 

[Taffalaffa]

I can't put in a pic because it is asking for a URL.. I can't seem to just uplaod it from my computer ... But it looks like this..
<TABLE style="WIDTH: 292pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=388><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" span=2 width=114><TBODY><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 17.1pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=22 width=64>Sheet</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=96>Date</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 86pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=114> Labor </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 86pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=114> Equipment </TD></TR><TR style="HEIGHT: 26.25pt; mso-height-source: userset" height=35><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 26.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=35>1a</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69></TD></TR><TR style="HEIGHT: 26.25pt; mso-height-source: userset" height=35><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 26.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=35>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>06/13/11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70> $ 3,549.64 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70> $ 450.00 </TD></TR><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 25.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=34>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>06/14/11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70> $ 3,549.64 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70> $ 450.00 </TD></TR><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 25.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=34>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>06/15/11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70> $ 3,549.64 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70> $ 450.00 </TD></TR></TBODY></TABLE>

And at the bottom I have 65 sheets. So on my recap sheet 11B has the sheet number, 11 C has the date from sheet 1 and that formula is:
=+'1'!$G3 , 11 D has the formula =+'1'!$J29 and 11E has the forumla =+'1'!$J49. What I need is then for the next line down where it says sheet 2 for all those formulas to carry down but for sheet 2. For example: =+'2'!$G3 , 12 D has the formula =+'2'!$J29 and 12E has the forumla =+'2'!$J49... and so on and so forth for all the sheets. The data I need from each sheet that rolls onto the recap is always in the same cell on ecah individual sheet... (On sheet 1 I will need the data from J49, sheet 2 J49....) Hopefully this explains it better.

 

[cgcamal]

You can upload a sample file through a free file sharing service or hotmail skydrive or something and one
that put the link to download it.

Sorry, I still don't get it, maybe p45cal does without a sample file.

Regards.

 

[p45cal]

I think I understood to start with, using the same idea:

Excel Workbook
	B	C	D	E
10	Sheet name	Date	Labour	Equipment
11	1	01 October 2011	Lab1	Equip1
12	2	02 October 2011	Lab2	Equip2
13	3	03 October 2011	Lab3	Equip3
14	4	#REF!	#REF!	#REF!
15	5	#REF!	#REF!	#REF!
Sheet22

Copy/paste the 3 formulae above to their respective cells and copy down.
The values you see on the screenshot are on their respective sheets - this is not a cheat. As before, I don't have sheets 3, 4 etc.

Another, slightly more flexible approach; if you're using real sheet names in column B we can do the following:

Excel Workbook
	B	C	D	E
10	Sheet name	Date	Labour	Equipment
11	1	01 October 2011	Lab1	Equip1
12	2	02 October 2011	Lab2	Equip2
13	3	03 October 2011	Lab3	Equip3
14	4	#REF!	#REF!	#REF!
15	5	#REF!	#REF!	#REF!
Sheet22

Same procedure as above with the formulae.

Now if you change the name of a sheet in column B, the changes in columns C,D and E should become apparent.

By the way, you can lose all $ symbols in the formulae if it makes it any clearer for you.
All date cells should be formatted as dates else you'll just see numbers like 40819 etc.

 

[Taffalaffa]

Thanks for the info. I had to run to a meeting yesterday and have one first thing today but will check on this formula in a few hours to see if it works. If not, I will try the upload. Really thanks you SO much for the assistance.

 

[p45cal]

Thanks for the info. I had to run to a meeting yesterday and have one first thing today but will check on this formula in a few hours to see if it works. If not, I will try the upload. Really thanks you SO much for the assistance.

Did it work?