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

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

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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

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.

B9 =IF(Rec!B96="Book",Rec!C96

C9 =IF(Rec!F96="L","Sell","Bu

D9 =IF(Rec!B20="Book",IF(Rec!

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

book-formulas.xlsm

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

Is that correct?

Thanks

Rob H

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)

This will give the sheet name for reference.

Then the following formulas:

B9 - =IF(VLOOKUP($A$1,Rec!$A$96

C9 - =IF(VLOOKUP($A$1,Rec!$A$96

D9 - =IF(VLOOKUP($A$1,Rec!$A$96

Thanks

Rob H

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

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

gowflow

I am sorry if I have caused problems

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

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

Thanks

Rob

Again sorry to have caused any problems.

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

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

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

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.

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

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

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.

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?

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

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

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

gowlfow

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

I hope I made myself clear.

gowlfow

hope this is what you want

Book-Formulas2.xlsx

Book-Formulas2.xlsx

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]R

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-fo

thanks

gowlfow

=IF('[book-formulas.xlsm]R

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

Book-Formulas2.xlsx

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

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

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

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

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

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
```

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 trialgowflow

Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

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