how to copy the formula

[jayait2000]

Hi, I have a QC report which has 30 sheets(for each days report) and a final report sheet (combines the numbers in all 30 sheets). All 30 sheets have the same data for each day. In the last sheet I have coombined all numbers for entire month. I have a formula as =('1'!C103) in Column B and column C should show =('2'!C103) and column D should show =('3'!C103).... till =('30'!C103).

However when I drag the formula from column B. It gets changed in C, D and other cells. it doesnt change the sheet(date like '2'!, '3'!) but just changes the column C103, D103, E103 etc. How will change the day as '2'!, '3'! and keep the cell number constant?

Thanks
Jaya

[NickyC]

Hi
why not just change the formulas from relative to absolute references, ie $A$1 not A1

[JBeaucaire]

If you put the sheet names in column A, in this case just the numbers 1, 2, 3, etc... then this becomes simple enough.


A2 = 1 (the first sheet name, but it could also be Sheet1, or Monday, or whatever the real sheet name is)
B2: =INDIRECT("'" & $A2 $ "'!C103")
C2: =INDIRECT("'" & $A2 $ "'!D99") (this is just to show you create the first row of formulas to ref the cells desired)

When you've finished creating the INDIRECT() formulas in row 2 based off of the sheetname in A2, now copy that row of formulas downward and they will adjust themselves to ref the sheet listed in column A.

[jayait2000]

Hi,

Sorry this doesnt work It shows an error

[JBeaucaire]

Post your workbook so we can see the error, too.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

[jayait2000]

Hi, i have attached the excel sheet for you reference. Please view the last sheet named Daily report. I have manually enterd the formule for one employee (marked in Blue). The one in green need to be filled, and I need to repeat the same for all the employees your help is greatly appriciated

[JBeaucaire]

1) On the Daily report, I've searched and cannot find your non-working INDIRECT() formulas. I also do not see on that sheet the cells that have the sheet names you would want.

Post #2 demonstrated the layout. Somewhere on the sheet is the tab name, then that tabname is used in the INDIRECT() to construct the formula.

BUT... I have to say, why bother? This appears to be a static layout of 31 days for a month's worth of data. Your formulas as laid out currently on the Daily Report are done, they should just work from now on.


2) You can simplify your formula on the day sheets.

C45: =COUNTIF(C2:C40, A45)
D45: =SUMIF(C2:C40, A45, F2:F40)
E45: =SUMIF(C2:C40, A45,G2:G40)

[jayait2000]

Hi,

I was talking about "Daily Report" cell number B25, C25........AF25 (the one in green colour). B25='1!'B46 (where 1 is the sheet name); C25='2!'B46........... etc. which I need to do for all employees. so when I drag the formule from B to AF i want the sheet name (1,2,3,4,5,6,......31) to be change and Refernce cell B46....should remail same. This is applicable to all employees.

Thanks
Jaya

[JBeaucaire]

In column B you would insert the sheet name somewhere so it could be referenced. Even if it's just the number one, it's useful as a reference, too, so that it is evident.

So, going back to my original post, follow those instructions.

B3 = 1
C3 = 2
etc.....

B15: =INDIRECT("'" & B$3 & "'!B45")



There is no substantive difference between this post and my original post.

[pmor1945]

Hi,

Here is a possible solution:

1. Row 14 needs to be unmerged and the format changed to General.

2. In Row 14 above the Blue lines enter the Day no to which the column refers - B14 value 1, C14 value 2 etc. If you don't want to see the numbers make them the same colour as the Fill.

3. In cell B15 copy and paste this formula into the formula bar for the cell:

=INDIRECT("'" & B14 &"'!B45") (very close to that indicated by our eminent Moderator!)

4. Copy the formula as far right as you need.

5. Copy and modify as required for cells B17, B19, and B20, and then copy these to the right.

HTH

Peter Moran