Copying Formulas to Multiple Worksheets [Solved/Closed]

mmikey45 6 Posts Saturday November 8, 2014Registration date November 8, 2014 Last seen - Nov 8, 2014 at 09:32 AM - Latest reply: mmikey45 6 Posts Saturday November 8, 2014Registration date November 8, 2014 Last seen
- Nov 8, 2014 at 12:00 PM
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
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Nov 8, 2014 at 10:35 AM
2
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

Thank you, Mazzaropi 2

Something to say? Add comment

CCM has helped 1773 users this month

Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Nov 8, 2014 at 09:55 AM
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
mmikey45 6 Posts Saturday November 8, 2014Registration date November 8, 2014 Last seen - Nov 8, 2014 at 10:13 AM
0
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.
mmikey45 6 Posts Saturday November 8, 2014Registration date November 8, 2014 Last seen - Nov 8, 2014 at 10:40 AM
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
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Nov 8, 2014 at 10:49 AM
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
mmikey45 6 Posts Saturday November 8, 2014Registration date November 8, 2014 Last seen - Nov 8, 2014 at 11:04 AM
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
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Nov 8, 2014 at 11:15 AM
0
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
mmikey45 6 Posts Saturday November 8, 2014Registration date November 8, 2014 Last seen - Nov 8, 2014 at 11:50 AM
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.
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Nov 8, 2014 at 11:57 AM
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
mmikey45 6 Posts Saturday November 8, 2014Registration date November 8, 2014 Last seen - Nov 8, 2014 at 12:00 PM
0
Thank you
Eureka!!!

Worked like a charm.

Thank you.

Mmikey45