Copying formulas to multiple worksheetsSolved/Closed

Question

Hi,
I have a workbook that contains 32 worksheets.  31 identical sheets that are basically days of the month.  Last one is a summary sheet.
I have to add a formula in each of the daily sheets that will sum a range of cells.  I want to create the formula in the first page and then copy it to the rest. The result needs to have each page referencing the range from only its own cells not the other worksheets.

Mazzaropi · Accepted Answer

mmikey45, Good morning.

Suppose your sheets have the name: Plan1 Plan2 Plan3...Plan31
You want sum an A10 cell about all sheets.

Try to do:
At SUMMARY, any cell: =SUM(Plan1:Plan31!A10)

Is it what you want?
I hope it helps.
 --
Belo Horizonte, Brasil.
Marcílio Lobão

Mazzaropi · Answer

mmikey45, Good morning. 

Try to do:
a)Click onthe first TAB name with a rigth bottom.
b) Select all sheets

Type any formula on this first sheet.
NOW, look at the others sheet at same cells if they have or not the same formula you typed on first tab.

Is it what you want?
I hope it helps. 
 --
Belo Horizonte, Brasil.
Marcílio Lobão

mmikey45 · Answer

Worked perfect thanks.
Let me ask the second part to my problem.
I need to create a formula on the summary work sheet that references the result on each one of the daily worksheets.  I need it to reference each of the 31 worksheets individually.

mmikey45 · Answer

No, I want to start on Summary tab Cell AA10 and enter info from Cell B90 on Worksheet 1, on Cell AA11 on Summary enter info from Cell B90 on worksheet 2. Ending at cell AA41 with info from B90 on worksheet 31

Mazzaropi · Answer

Try to do: 

SUMMARY TAB
AA10 --> =INDIRECT("Plan" & ROW(A1) & "!$B$90")
Copy it down.

Is it what you want?
I hope it helps. 
 --
Belo Horizonte, Brasil.
Marcílio Lobão

mmikey45 · Answer

I have done as you suggest.  The cells are returning a result of #REF!
Not returning the correct result which should be a number.  Cells are formatted for a number with no decimal places.

Thanks for your help,
really appreciate.
Mmikey

Mazzaropi · Answer

mmikey45, 

As I don't know how is your file, I did a small example about my formula:
http://speedy.sh/b8gaE/08-11-2014-en-Kioskea-Indexed-Summary-OK.xlsx 
 
Please, tell us if it worked for you.
 --
Belo Horizonte, Brasil.
Marcílio Lobão

mmikey45 · Answer

I see how it works.  I think the issue for mine is the worksheets are named 1, 2, 3, 4, 5, ....., when I changed your worksheet from Plan1 to just 1 it stopped working.  When I changed your formula from =INDIRECT("Plan" & ROW(A1) & "!$A$1")  to =INDIRECT("1" & ROW(A1) & "!$A$1") it did not work.
Not sure but I must be missing something.

Mazzaropi · Answer

"...Not sure but I must be missing something...."
That's it.

Try to use it:

Before: =INDIRECT("1" & ROW(A1) & "!$A$1")
Now...: =INDIRECT(ROW(A1) & "!$A$1")

I believe this will work. 
 --
Belo Horizonte, Brasil.
Marcílio Lobão

mmikey45 · Answer

Eureka!!!

Worked like a charm.

Thank you.

Mmikey45

Copying formulas to multiple worksheets

10 responses

Similar discussions

Subscribe To Our Newsletter!