insert formulas depending on the sheet name

Could an expert help me out with my dilemma please.

My file could have anything from 2 to 11 sheets. The first sheet [named Rec] is excluded from the criteria, I am only looking at sheets named from Book 1 to Book 10

For each sheet [named Book ?] I need to insert formulas in Row 9 and for each sheet the formulas in B9, C9 & D9  will be different. Other formulas in Row will be consistent in each sheet.

This all sounds very complicated so I have attached a Dummy file that hopefully will make clear my request.

Thanks in advance.
book-formulas.xlsm
JagwarmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
ok lets take it one step at a time.

You need the formula in only B9,C9 and D9 of each of the 'book' sheets ? or it has to go down also b10,C10,D10 etc...
then please give us the formula that goes in B and C and D is it the correct one that you have in the file ?

gowflow
gowflowCommented:
Presume you want a solution via VBA ? right ?
gowflow
JagwarmanAuthor Commented:
Hi Gowflow, The formulas in the file are the correct formulas and yes VBA please
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

JagwarmanAuthor Commented:
to clarify, when I create the new file it will always contain the sheet 'Rec' and may contain sheets Book 3 and book 5 or Book 1, Book 2 Book 10 etc
gowflowCommented:
yes but here are the formulas I see
B9 =IF(Rec!B96="Book",Rec!C96," ")
C9 =IF(Rec!F96="L","Sell","Buy")
D9 =IF(Rec!B20="Book",IF(Rec!F20<=0,-Rec!H20,Rec!H20),0)

As you can see C9 does not refrence Book !! also here these formulas does not refrence a sheet name but instead they refrence a string "Book" which has nothing to do with sheet name
UNLESS
Rec!F96 refrence a sheet name !!!

you are posting just part of the story cannot work like this I need to see the orginal Rec!F96 if it has also formulas etcc...


as so far it is not clear what you want.
gowflow
JagwarmanAuthor Commented:
file with some data attached
book-formulas.xlsm
Rob HensonFinance AnalystCommented:
Ok, I think I see what you are trying to do. Row 96 on the Rec sheet has data for Book 1, Rec sheet, Row 97 has data for Book 2, Rec sheet, Row 98 has data for Book 3 etc etc...

So the relevant Book tab has to check the relevant row on the Rec sheet.

Is that correct?

Thanks
Rob H
Rob HensonFinance AnalystCommented:
OK, so if the above is correct; a few amendments to the file.

On the Rec sheet label cells A20:A29 and cells A96:A105 with labels Book 1 to Book 10.

Then formulas on the Book tabs as follows:

A1 (or elsewhere but adjust following formulas for new cell)
=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1),1)+1,LEN(CELL("filename",$A$1)))
This will give the sheet name for reference.

Then the following formulas:
B9 - =IF(VLOOKUP($A$1,Rec!$A$96:$AM$105,2,FALSE)="Book",VLOOKUP($A$1,Rec!$A$96:$AM$105,3,FALSE)," ")
C9 - =IF(VLOOKUP($A$1,Rec!$A$96:$AM$105,6,FALSE)="L","Sell","Buy")
D9 - =IF(VLOOKUP($A$1,Rec!$A$96:$AM$105,2,FALSE)="Book",ABS(VLOOKUP($A$1,Rec!$A$20:$I$29,8,FALSE)),0)

Thanks
Rob H
JagwarmanAuthor Commented:
yes that is correct Rob
gowflowCommented:
@Rob do not follow your reasoning.

My understanding is that OP wants a VBA solution. If VBA is, then why to propose that the formulas need to be amended ?

Jagwarman please clarify what you need as your answer: yes that is correct Rob leave us not really understanding what you want ? Are you satisfied with Rob answer ? does it solve it for you ? if yes then fine. Else kindly clarify.

gowflow
Rob HensonFinance AnalystCommented:
I read the response of "Yes that is correct" as being response to my comment prior to supplying the formulas, ie clarifying what is required/understanding of question.

In general, with questions on EE, even if OP has specified VBA I assume that they have specified that only because they are not aware of a formula driven way of resolving their question. On occasions it is clear that VBA would be the only practical route but in a scenario such as this a formula solution may be suitable. On this occasion, the OP did not specify VBA in the question, only in response to your question, so saw no reason why not to suggest a formula driven solution.

Thanks
Rob H
gowflowCommented:
Fine and pls do not mis-understand my comment as not pushing for VBA or formula but only stating what was said. Will leave it to Jagwarman to advise.

gowflow
Rob HensonFinance AnalystCommented:
No worries, no offense taken!

Thanks
Rob
JagwarmanAuthor Commented:
This will be part of a bigger Macro and therefore I would like a VBA solution. My answer of 'that is correct' refers to B20 refers to book1, B22 refers to Book 2 etc

I am sorry if I have caused problems
Rob HensonFinance AnalystCommented:
I assume it would make sense to have a template tab which the routine takes a copy of and renames.

With the formula already in place on the template, you wouldn't then need that part done by VBA.

For example, if you were creating a new tab for Book 3, copy template and rename Book 3 (manually or by VBA) the formula in A1 would recalc and give value of Book 3. The formulas in B9:D9 on that sheet would then look at the rows with Book 3 on the Rec tab.

Thanks
Rob H
gowflowCommented:
Would you favor Rob's solution ? Let me know if you need VBA so we are not in a rat race.
gowflow
Rob HensonFinance AnalystCommented:
My suggestion would only cover part of it, a template from which to copy when creating new tabs.

I suspect there would still be a need for VBA to do the copying and renaming.

Thanks
Rob
JagwarmanAuthor Commented:
I like Rob's suggestion and I am sure I will be able to use in the future but as I said right at the start I am looking for VBA.

Again sorry to have caused any problems.
gowflowCommented:
ok so if VBA is your choice I need to have some questions answered first to assess how we will do this:

I take it that you have a variable workbook that have as a base sheet Rec and then you will be adding sheets called Book x where you need the replication of certain formulas.

If my understanding is correct then
1) do you have a template of this Book worksheet ? if yes which one from the ones you posted ?
2) Are the sheets Book x static or their rows grows ?
3) If answer to 2) is rows grows then what determin the new row like what column is the indicator that the user want to add a row ? so we can thru code duplicate the formulas ?


If my logic above is correct and in the correct path then I envision for you to have a button in sheet Rec that is called Create Worksheet that when it is activated it will prompt you for a name say 10 and it will create from the Template stored a new worksheet called Book 10 with the appropriate formulas. and when the user trigger a certain column that we agree on then it will create formulas for that column as agreed.

Kindly advise you comments on above to see how to move from here.
gowflow
JagwarmanAuthor Commented:
Hi Gowflow

I take it that you have a variable workbook that have as a base sheet Rec and then you will be adding sheets called Book x where you need the replication of certain formulas.

- There is a workbook that the user uses to record all of their detail. The main sheet of this is the sheet Rec. There are also the 10 sheets Book 1 - Book 10 which are populated when the user inputs details into the Rec Sheet. There are other sheets but we are not looking at any of those.

I create a new workbook and copy the sheet Rec plus any relevant 'Book' sheet that has data on into the new workbook with all of the details.

[and this is where my request comes into play]

The user then needs the new workbook to have the formulas in the 'Book' sheets because they will be using the new workbook at some point in the future to amend data in the Rec Sheet in the new workbook

All rows are static there are no moving rows or columns
gowflowCommented:
mmm ic

Meanwhile I had al clearer look at your workbook and noticed that in Col B of every book
Book1 is related to row 96
Book2 is related to row 97
Book3 is related to row 98
Book4 is related to row 99
Book5 is related to row 100
etc ...

is this correct ? and do you have other formulas that need to be reworked ?
as I see all the books have row 9 that have data and it is the same exact data which I think is not reflecting reality and the rest of rows are empty !

Now the issue comes when you need to copy these worksheet to an other workbook and instead of you doing it manually maybe we need to work on a routine at this point could you please clarify what you copy ? like you copy Rec and all the Book sheets ? only ? the full Rec and the full Book sheets or just a part ?

gowflow
JagwarmanAuthor Commented:
Each 'Book' looks at one of the rows, so like you say

Book1 is related to row 96
Book2 is related to row 97
Etc
Etc

Each book only has a Row 9 that has formulas. B9, C9 and D9 refer to the specific rows as in Book1 is related to row 96,  Book2 is related to row 97

The other cells in row 9 are always the same. i.e. E9 on all 'Book' sheets is =Rec!D5 and F9 is =Rec!D2 etc

Only the relevant 'Book' sheets are included in the New Work so the new workbook could have Rec Sheet, Book 3 and Book 5, or Rec Sheet and Book 1 and Book 4 and Book 10

Hope this helps.
gowflowCommented:
ok so when you do this manually what is the problem exactly if you can let me know then I could fix it via code as not sure what is the problem youare encountering.
gowflow
JagwarmanAuthor Commented:
Hi Gowflow

First we are trying to get away from all manual stuff as the number of files they create every day is very time consuming.

Second there is already a Macro that is working within their input file that does stuff and because they were doing the copying of files manually they want it automated.

The new file is already created by my Macro with the Rec Sheet and the relevant Book sheets so the only part I was looking for was to get the correct formulas into the correct Book sheets.

Thanks
gowflowCommented:
You have macros in this workbook and you did not post it !!! Sorry cannot work on bits and pieces.

Let me be clear
You need to post the same workbook as-is that you have and simply change the confidential info. or else we will be endlessly going back and forth wasting both your time and my time.

also you say

The new file is already created by my Macro with the Rec Sheet and the relevant Book sheets so the only part I was looking for was to get the correct formulas into the correct Book sheets.

In the workbook you posted I don't see any problem the formulas are correctly referenced don't see what is wrong. Pls post what is happening when you copy a worksheet indicating how it is being copied and what it should copy instead.

gowlfow
JagwarmanAuthor Commented:
Gowflow

all of the work I do, and probably most other people who ask for help on EE will have macros in the files. As 99% of my macro is complete I am only asking for the 1% I cannot do myself and it does not seem right to me that I should post a workbook with my entire code that has nothing to do with the piece of code I have asked for.

I have tested Robs solution and it works so I will accept his solution and build it into my file.

Thank you for your time and apologies if I have caused you any issues.
gowflowCommented:
ok no problem
gowlfow
JagwarmanAuthor Commented:
Gowflow/Rob

Whilst I can use Robs formula solution as he said I still need some code that will select the 'Book' Sheet[s] that are in the new workbook because when I copy the data into a new workbook the formula adds the original workbook name into the formula :-(

So, I still need your help.

I need some code that will select each sheet with the name 'Book ?' in my new workbook and then insert the formulas Rob has provided.

Can you/will you be able to assist?
Rob HensonFinance AnalystCommented:
Once you have created the new book, with erroneous links, and saved it you can then use the Edit Links window to change the links.

Select the link to the old Workbook and click Change Source. Browse to your new workbook, select and Click OK. The link to the old workbook should disappear. All formulas linking to the old book will have been amended to have the external workbook link removed.

This process can be Automated through VBA but the Workbook names need to be formulated so you can change the links.

If the link still shows in the Edit Links Window, it could be that there are Range Names referring to the old workbook as well.

Thanks
Rob H
gowflowCommented:
Don't get me wrong you are the one who jumped the gun, still and will always be happy to help but my request was only in an attempt to make sure the code made will not contradict or obstruct any code already there. But can understand you are not willing to share it for your own reasons.

Anyway, my question now is the following:

When you create a new workbook suppose we get you a routine to fix the formulas, this routine will reside in the original workbook, what you will do is apart from copying whatever sheet you have you will also copy this routine ? or the whole workbook with the entire code is copied onto the new workbook ?

gowflow
JagwarmanAuthor Commented:
Hi Gowflow,

I will only copy the Rec Sheet and any relevant Sheets 'Book xx' I then want to add the formula into the sheets 'Book xx' in the new workboook.

All the VBA code is in and remains in the original workbook.

I presume the code will loop so something like [ in layman's terms]

[Excluding the sheet 'Rec'] find the sheet named 'Book 1' put in formulas in Row 9

find 'Book 2'  put in formulas in Row 9

find 'Book 3' put in formulas in row 9

until book 10

 Thanks
JagwarmanAuthor Commented:
Hi Gowflow,

will you be able to assist?
gowflowCommented:
If you simply copy the book sheets to the new workbook together with the Rec and save it. can you then post this sample workbook as I have already a solution but not sure it will fix your problem but need to test it on your produced file. Can oyu do this please ?
gowlfow
JagwarmanAuthor Commented:
Hi Gowflow.

I posted in my original request. That file contains the Rec sheet and all 10 Book sheets so potentially you could remove a few of the book sheets to test. However let me know if you want a different file saved.

Thanks
gowflowCommented:
Well the workbook oyu posted does not have links to an outside workbook. I presume your problem is when you copy sheets from a workbook to a new workbook it copies the sheets and change the reference of formulas to point to the other workbook and this is your problem. I need to see your problem in a workbook as I already mentioned I do not see a problem in the workbook you posted.

I hope I made myself clear.
gowlfow
JagwarmanAuthor Commented:
hope this is what you want
Book-Formulas2.xlsx
gowflowCommented:
yes you succeeded in Book 10 only as book 6 is empty sheet and book 3 you simply copied Rec I think you did this very quickly.

Its fine.
As I am pulling the file from the web I get this in Sheet Book 10 Cell B9
=IF('http://filedb.experts-exchange.com/incoming/2015/03_w13/904679/[book-formulas.xlsm]Rec'!B105="Book",'http://filedb.experts-exchange.com/incoming/2015/03_w13/904679/[book-formulas.xlsm]Rec'!C105," ")

Now I need you to do this:
Open the file you just created and paste here what you have in Book 10 of cell B9

I suspect you would have something like
=IF('C:/directory/[book-formulas.xlsm]Rec'!B105="Book",'C:/directory/[book-formulas.xlsm]/[book-formulas.xlsm]Rec'!C105," ")

thanks
gowlfow
JagwarmanAuthor Commented:
I presume you mean the formula

=IF('[book-formulas.xlsm]Rec'!B105="Book",'[book-formulas.xlsm]Rec'!C105," ")
gowflowCommented:
GREAT !!!!!

We will get there .... finally

question:
Do your main file is always called:
book-formulas.xlsm ???

Like you will always copy or create new workbook out of this book-formulas.xlsm or they may be other workbboks that you can use that will have a different name ?

Also is same file or different they all reside on C ???

I need a common denominator.
gowflow
JagwarmanAuthor Commented:
sorry see what you mean
Book-Formulas2.xlsx
JagwarmanAuthor Commented:
Hmm that is a difficult one.

Do your main file is always called:  book-formulas.xlsm ???  No.

Like you will always copy or create new workbook out of this book-formulas.xlsm  No

 or they may be other workbboks that you can use that will have a different name ?  Yes



They can reside anywhere because there are lots of users


I think I am missing something Gowflow.

If I create the formulas in a module and call it say GetFormulas

is it not easier to loop through and look for Book 1 Book 2 etc and then when found just call GetFormulas because the formulas will be the same.

or am I mad
gowflowCommented:
You mean create them as UDF ? User defined Formulas ?
Well no you will still have this problem as the new workbook oyu are creating is an .xlsx and not an .xlsm so it have no code to refer to !

Please clarify your proposal of GetFormulas if I did not get it correctly.
gowlfow
gowflowCommented:
ok lets stay in the solution path and lets not get split thin.

Question again:
Does all the formulas you have in the 'Book' Sheets all of them refer to Rec sheet or you have some that refer to other sheets ?

And this question applies to all sheets that are created in the new workbook meaning  Sheet Rec and all the Book sheets.

gowlfow
JagwarmanAuthor Commented:
thet all refer to Rec sheet
gowflowCommented:
ok here is the story.

When you are coping certain sheets and creating a new workbook, you are creating it as .xlsx so if we need to add a macro there to fix the formulas it has to be created as .xlsm and then simply copy the macro that I will make for you and place it in a module in that new workbook.

do you have a problem with this ?
gowflow
JagwarmanAuthor Commented:
Morning gowflow.

the way my macro works is all macros are in the main file which is xlsm.

I Add New and create a new workbook, then copy all the files into the new workbook. I then want to do the formulas. I name the new workbook and then save it in it's own path. I would like the macro to remain in the original workbook please.

Thanks
gowflowCommented:
mmm that is what I thought. I have the version ready please do me a favor I would like to go in 2 steps here as the way you want it more complicated and require more work I would suggest you try what I will post on a new file that you create like I explained and if it is good and satissfiable to you the we close this one and you open a related question where I would work to change the macro to work remotely from an other workbook this is more tricky especially that you have plenty of workbooks that can be opened as you mentioned.

Pls find the test file inside here is the macro. Simply create as you do a new file and put in it all your sheets (put a lot) create a module and copy this macro in the module and save the file as whatever .xlsm then close all your files and open the new file created and activate macros and run the macro that is inside and check the results.

You want to check the formulas prior to running the macro to see how they look pointing to your roginal file then run the macro and see results.

If you are satisfied then I think we would have answered correctly this question then we can move to making it work remotely from an other workbook and this one being an xlsx.

here is the code that is imbedded in the attached workbook.

Sub THISWBRemPathVar()
Dim WS As Worksheet
Dim cCell As Range, Rng As Range
Dim sNewFormula As String, sRemPath As String
Dim lQuote As Long, lExcl As Long, lOcc As Long

'---> Disable Events
With Application
    .EnableEvents = False
    .DisplayAlerts = False
    .ScreenUpdating = False
End With
    
    
For Each WS In ActiveWorkbook.Worksheets
        
    On Error Resume Next
    Set Rng = WS.UsedRange.SpecialCells(xlCellTypeFormulas)
    If Err = 0 Then
        On Error GoTo 0
        If Not Rng Is Nothing Then
            For Each cCell In Rng
                If InStr(1, cCell.Formula, "'C:\") <> 0 Then
                    lQuote = InStr(1, cCell.Formula, "'")
                    lExcl = InStr(1, cCell.Formula, "!")
                    sRemPath = Mid(cCell.Formula, lQuote, lExcl - lQuote + 1)
                    sNewFormula = Replace(cCell.Formula, sRemPath, "Rec!")
                    cCell.Formula = sNewFormula
                    lOcc = lOcc + 1
                End If
            
            Next cCell
        End If
    Else
        On Error GoTo 0
    End If
        
    '---> Reset Variables
    'lOcc = 0
    lQuote = 0
    lExcl = 0
    sRemPath = ""
    sNewFormula = ""
    
Next WS
    

'---> Enable Events
With Application
    .EnableEvents = True
    .DisplayAlerts = True
    .ScreenUpdating = True
End With

MsgBox "A total of " & lOcc & " formulas processed successfully."

End Sub

Open in new window



gowflow
Test.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gowflowCommented:
If you need help on having the macro work from your main workbook please put a link of the new question in here and will be glad to assist.
gowflow
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.