insert formulas depending on the sheet name

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

Presume you want a solution via VBA ? right ?
gowflow

Hi Gowflow, The formulas in the file are the correct formulas and yes VBA please

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

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

file with some data attached
book-formulas.xlsm

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

yes that is correct Rob

@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

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

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

No worries, no offense taken!

Thanks
Rob

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

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

Would you favor Rob's solution ? Let me know if you need VBA so we are not in a rat race.
gowflow

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

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.

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

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

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

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.

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

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

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

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.

ok no problem
gowlfow

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?

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

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

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

Hi Gowflow,

will you be able to assist?

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

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

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

hope this is what you want
Book-Formulas2.xlsx

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

I presume you mean the formula

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

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

sorry see what you mean
Book-Formulas2.xlsx

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

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

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

thet all refer to Rec sheet

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

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

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