Menu

Copying formulas to multiple worksheets [Solved/Closed]

Report
Posts
6
Registration date
Saturday November 8, 2014
Status
Member
Last seen
November 8, 2014
 -
 subodh -
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

Answer 1 / 10
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
 134
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
Thank you
3
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 5448 users have said thank you to us this month

Report

This is helpful.
Answer 2 / 10
Posts
6
Registration date
Saturday November 8, 2014
Status
Member
Last seen
November 8, 2014
 1
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.
Answer 3 / 10
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
 134
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
Answer 4 / 10
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
 134
"...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
Answer 5 / 10
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
 134
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
Answer 6 / 10
Posts
6
Registration date
Saturday November 8, 2014
Status
Member
Last seen
November 8, 2014
 1
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
Answer 7 / 10
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
 134
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
Answer 8 / 10
Posts
6
Registration date
Saturday November 8, 2014
Status
Member
Last seen
November 8, 2014
 1
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
Answer 9 / 10
Posts
6
Registration date
Saturday November 8, 2014
Status
Member
Last seen
November 8, 2014
 1
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.
Answer 10 / 10
Posts
6
Registration date
Saturday November 8, 2014
Status
Member
Last seen
November 8, 2014
 1
Eureka!!!

Worked like a charm.

Thank you.

Mmikey45
Ask a question