Menu
Ask a question Report

Copying formulas to multiple worksheets [Solved/Closed]

Posts
6
Registration date
Saturday November 8, 2014
Status
Member
Last seen
November 8, 2014
 -
Excel Ask a question Report
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.
See more 

10 replies

Best answer
approved by Ratnendra Ashok on Sep 4, 2019
Report
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
 130
3
Thank you
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

Say "Thank you" 3

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 6300 users have said thank you to us this month

Report
Posts
6
Registration date
Saturday November 8, 2014
Status
Member
Last seen
November 8, 2014
 1
1
Thank you
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.
Report
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
 130
1
Thank you
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
Report
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
 130
0
Thank you
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
Report
Posts
6
Registration date
Saturday November 8, 2014
Status
Member
Last seen
November 8, 2014
 1
0
Thank you
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
Report
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
 130
0
Thank you
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
Report
Posts
6
Registration date
Saturday November 8, 2014
Status
Member
Last seen
November 8, 2014
 1
0
Thank you
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
Report
Posts
6
Registration date
Saturday November 8, 2014
Status
Member
Last seen
November 8, 2014
 1
0
Thank you
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.
Report
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
 130
0
Thank you
"...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
Report
Posts
6
Registration date
Saturday November 8, 2014
Status
Member
Last seen
November 8, 2014
 1
0
Thank you
Eureka!!!

Worked like a charm.

Thank you.

Mmikey45
Ask a question