Finance 3.0 - Social Network for Finance
Smart financial thinking
Updating Multiple Excel Sheets
Hi all,
I'm working on a model in which mulitple sheets are used. The problem that i'm facing is:
I have 2 workbooks say book1.xls and book2.xls. The Book1.xls is my master file in which all the working are done, whereas the Book2.xls is the user interface by which different values are passed. Now my concern is related to the processing of data in the master file. since the user interface (Book2.xls) will be the only file which would be open at any given point of time and the user would input values in this sheet only, this book2.xls is linked with book1.xls.
I'm having problem with processing the book1.xls as the values should be updated in the book1.xls and then the results should be retrived from book1.xls to book2.xls. Since the book1.xls is closed the data is not processed in this sheet.
Can anyone help me on this one...!!
I have tried to write a macro to open the book1.xls automatically, pull the values from book2.xls and the return back the output to book2.xls.
this works fine if there is only 2 sheets in the folder one open and other close, but when multiple sheets are placed in the folder it open all other sheet except the desired one.
Please provide any alternate solution.....!!
thanks in advance.
Abhishek
Tags: excel, multiple excel sheets
Replies to This Discussion
-
Permalink Reply by Dipu James on -
try this and let me know. if it does not work so that i will keep my mouth shut the next time i see a similar question ;-)
try pasting the data as a link under paste paste link and paste the formula to all the blank colums in book 1 where the new entries are suppose to get updated.
every time you open the work book 1 update the links....
dipu
-
Permalink Reply by Abhishek Pradhan on -
I have tried this one, but every time i pass new values in book2, the desired result is not shown as the calculations in book1 are not processed to return back the values.
I have attached to files, in book2 E7 enter value and press update. the macro will run and it will open book1 (all the processing would be done) and the resultant output will be returned to book2
Since this is a very simple calculation and involves only 2 sheets it is working fine. I'm having problem doing the same of a specific file in different folder.
Can you suggest some changes in macro in book2...?
Thanks for showing interest.
Abhishek - Attachments:
-
Permalink Reply by lilolee on
-
Why not have Book1 and Book2 as a workspace. Password protect Book2 so that it can't be altered, except by you. That way when Book1 has new values Book2 will calculate and return the answers.
Mind you what is wrong with having the workings done in the same book as the interface, except hide and protect the working sheets?
There probably is a macro solution, but you would have to use ExecuteExcel4Macro method.
-
Permalink Reply by Abhishek Pradhan on
-
what you recommend is very logical and i agree with you but the problem is with having single sheet and hiding the working sheet.
Please refer to the sheets attached. this would give an idea of what i'm looking for.
Using password is not the preferred solution since it can be cracked using freely available softwares.
Also the model will be in parcels, so that the processing sheets would be placed in a separate folder and the snapshot sheet would be made available separately.
Can you suggest some changes in macro in book2...?
Also can you elaborate more on ExecuteExcel4Macro method.
Thanks for showing interest.
Abhishek - Attachments:
Sign in
Badge
Continuing Education
 |
Accounting |
 |
Banking Regulations |
 |
Certification Training |
 |
Commercial Bank Management |
 |
Corporate Finance & Valuation |
 |
Treasury Management |
 |
Credit Risk Management |
 |
Financial Derivatives |
 |
Financial Institution Management |
 |
Financial Markets |
 |
Financial Modeling |
 |
Financial Planning |
 |
Market Risk Management |
 |
Operational Risk Management |
 |
Operations & Compliance |
 |
Quantitative Analysis |
 |
Strategic Financial Management |
Newsletter
Linkedin
RSS
Facebook
Widgets
Twitter© 2010 Created by Finance 3.0.
