Assigning Formula

Brutium

Board Regular
Joined
Mar 27, 2009
Messages
188
Hello Everyone,

I have a rather peculiar situation. I surely hope someone will help me....

Using a macro, I created a number of sheets using a template. What I now need to do is figure out is how to assign a formula to different cells (the data comes from the various sheets that I created). I guess I need to replicate the same formula, but I can't figure out how to do it.

Any help would be greatly appreciated.

Thank you
Brutium
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Would this be the same formula or "replica"? If the same as in refering to the sheet in which the formula resides on like:


In Cell C1 (of each sheet)
=A1+B1 (of each sheet)

Where on each sheet you would like to add the cells A1 and B1, then show the sum in C1.

If so, then you can try to "drill through the sheets".

Select the first sheet, then hold shift key and select last.
Or Hold CTRL key, then select each sheet you would like.

After select sheets, in Cell C1 type or paste the formula and press enter. All selected sheets now have the same in Cell C1.

This could also work for refering to a Master sheet of common sheet like:

=MASTER!A1+MASTER!B1

Is something like this what you would like?
 
Upvote 0
Thank you Repairman615 for your reply.:)
:(
No, this is not really what I am trying to accomplish.
As I was trying to explain, there are a number of sheets that are created by my macro. All the sheets are based on a template which contains a column that calculates the average of students' marks in a number of tests.
The sheets that are created, all contain the averages of various tools that a teacher will use to evaluate a student's progress. These averages are then collected in a summary sheet, in columns called Tests, Quizzes etc. My problem is that the summary sheet will need to bring in the averages from all the sheets named after the tools used and place them in different columns. This is what I do not know how to do, since the summary sheet is my starting point.

I hope I made myself clear. :confused:

Thank you
Brutium
 
Upvote 0
Hello,


If there is similarity in the names like "Tools1", "Tools2"...this could be an opportunity.

Maybe a master list within the summary sheet of sheet names...(which may be done with a formula in itself)

Lastly, If familiar with UDF's, I use a SheetOffset function to capture data from an unknow future sheet.

Would you provide samples of the sheet names and also the formulas once constructed with the sheet names.

...I guess I am still not 100% clear, but would like to help.
 
Upvote 0
Thank repairman615

The following is the macro I use to create additional sheets.
I start with 3 sheets, one called Tools, the other Class and the third Worksheet which is my template. In Tools I have a list of activities which will be the the new sheets created by the macro. Each sheet will then calculate an average, an then this is SUPPOSED to be collected in my summary sheet, which is called Class.

Sub Addsheets()
Dim LR As Long, i As Long, MasterSheet As Long
Dim rngTopOfList As Range

With Sheets("tools")
Set rngTopOfList = .Range("B3")
LR = .Cells(.Rows.Count, rngTopOfList.Column).End(xlUp).Row

MasterSheet = ActiveWorkbook.Sheets.Count
If LR > rngTopOfList.Row Then
For i = 1 To LR - rngTopOfList.Row
ActiveWorkbook.Sheets(MasterSheet).Copy _
after:=Worksheets(MasterSheet + i - 1)
ActiveWorkbook.Sheets(MasterSheet + i).Name = _
rngTopOfList.Offset(i).Value
Next i
End If
ActiveWorkbook.Sheets(MasterSheet).Name = rngTopOfList.Value
End With
Set rngTopOfList = Nothing

End Sub

I hope this helps

Brutium
 
Upvote 0
I am thinking maybe add a few lines in the macro...for each sheet that is created in the loop, pull the name and throw it into a formula that is piped into the "Class" sheet.

Your Start Cell in Column will need to be your Top Cell for your summary.
Also the formula is an example...should bring the contents of Cell A1 from each sheet created.

Like:
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Addsheets()<br><SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, MasterSheet <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> rngTopOfList <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> wsName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br><SPAN style="color:#00007F">With</SPAN> Sheets("tools")<br><SPAN style="color:#00007F">Set</SPAN> rngTopOfList = .Range("B3")<br>LR = .Cells(.Rows.Count, rngTopOfList.Column).End(xlUp).Row<br><br>MasterSheet = ActiveWorkbook.Sheets.Count<br><SPAN style="color:#00007F">If</SPAN> LR > rngTopOfList.Row <SPAN style="color:#00007F">Then</SPAN><br><SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> LR - rngTopOfList.Row<br>ActiveWorkbook.Sheets(MasterSheet).Copy _<br>after:=Worksheets(MasterSheet + i - 1)<br>ActiveWorkbook.Sheets(MasterSheet + i).Name = _<br>rngTopOfList.Offset(i).Value <SPAN style="color:#007F00">'''Can be replaced with wsName if the following line is moved before.</SPAN><br><br><SPAN style="color:#007F00">''' Here begins changes</SPAN><br>wsName = rngTopOfList.Offset(i).Value<br><br>ActiveWorkbook.Sheets("Class").Range("Your Start cell in Column").Offset(i - 1, 0).Value = _<br>        "=" & wsName & "!A1"<br><SPAN style="color:#007F00">'''</SPAN><br><br><SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>ActiveWorkbook.Sheets(MasterSheet).Name = rngTopOfList.Value<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">Set</SPAN> rngTopOfList = <SPAN style="color:#00007F">Nothing</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0
Hello Repairman615
I tried your suggestion and changed these lines
''' Here begins changes
wsName = rngTopOfList.Offset(i).Value
ActiveWorkbook.Sheets("Class").Range("C9").Offset(i - 1, 0).Value = _
"=" & wsName & "!M9"
'''

Now the macro creates the worksheets but I do not get the results I was hoping for.
The averages should go in C9 for the first new sheet, D9 for the second Sheet etc. Instead I get #REF!A1 error.

Any ideas?

Brutium
 
Last edited:
Upvote 0
Try replacing:
Offset(i - 1, 0).

with
Offset(0, i - 1).

If that still causes a reference error...add a this line:

wsName = rngTopOfList.Offset(i).Value
msgbox wsName

this will display the wsName and may give a clue.



 
Upvote 0
Hello Repairman615,

Still some issues.
The entries seem to go a column off. What I mean is that the data that was supposed to go in E9 now goes in D9; what was supposed to go in D9 now goes to in C9 and what was supposed to go in C9 does not appear.

Any suggestion?

Brutium
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top