Hi, I'm hoping someone can help out with my problem. What I'm trying to do is link my Seed Value from the Gamma Analysis and Log Normal Analysis to my LCG Calculator, however I'm only able to reference one of the spreadsheets leading me to "='Gamma Analysis'!B7". I'm looking for a way such that when one of the sheets are active the LCG Calculator takes the value from the active spreadsheet. Thank you in Advance!
Last edited by RizzlaXD; 12-07-2014 at 02:30 PM.
I havent got excel in front of me so cant test it but, cant you reference one of the sheets and if it returns an error then reference the other? Something like...
Please Login or Register to view this content.
If someone has helped you then please add to their Reputation
Hi, welcome to the forum
Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff)
Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thank you for your reply,
The function doesn't seem to be working correctly as I tried a seed value of 150 in the Gamma Analysis but it didn't update in the LCG Calculator =l I've attached my Excel workbook so you can see where it's not updating. Thank you again! =)
Thank you! ^^
Yeah I figured it was a much better idea =P
Thanks for the file
Im not really sure what you are trying to do hereCan you walk me through what you are doing, and samples of what you expect?
For ease what I'm trying to do is 'link' the seed value from the Analysis spreadsheets (Gamma and Log-Normal) so that it updates in the LCG Calculator. The problem I have is that I can only link the Seed Value to one of the Analysis spreadsheet but not both, I only end up doing something like "='Gamma Analysis'!B7" which ignores Log Normal Analysis' Seed value from updating.
OK, and how do you decide which sheet to use? Could you put an indicator on LCG which shows the sheet to use?
Umm well the decision is up to the user, I just want it so that whichever Worksheet is currently in use, it takes the seed value from that and inputs it into LCG B5
But as soon as you select LCG, then that is teh active sheet, and not the sheet you just came from?
Would it be practical to use a "switch" on the LCG sheet, so that whoever - can select which to use?
Oh no, The active sheets are going to be the Analysis sheets. The LCG Calculator is just used so that I can generate Pseudo-Random numbers which is used in the analysis Look at "Claim Amount" columns they're using "Random" which is the range of cells from LCG C7:C206.
Last edited by RizzlaXD; 12-07-2014 at 06:55 PM.
So will there always only be one of the two workbooks open and you just want it to use the one that's open?
Yup! One of the worksheets will be active and I want that seed value to be inputted to LCG. Sorry if what I'm saying is confusing =)Originally Posted by pjwhitfield
Last edited by RizzlaXD; 12-07-2014 at 07:16 PM.
So if I enter a value of 150 into the Seed Value input on Gamma Analysis, this will update the seed value on the LCG Calculator. So I want the same to happen with Seed Value input on Log Normal Analysis, e.g. I input Seed Value of 23 and this will update on the LCG Calculator
So, both sheets could have a seed in them, but you only want the 1 in the active sheet to be used?
OK, another thought...
How about if you do all the calcs in each sheet? So instead of haveing that LCG sheet, each of the other 2 sheets have their "own" LCG data in that sheet
Yes please =)
I did that at first but then the workbook slowed down a lot =l
As you can see it works when I have 2 individual LCG Calculators, but I want to use just 1 LCG Calculator for both analyses
Then perhaps review what formulas you are using and see if they can be streamlined?
The only other way I can think to do this would be to review the list of Open work books to find which of the two was open and then use that to potentially amend the formulas in the book.
eg Set the formulas up to use the ='Gamma Analysis'!B7 references and then in the Workbook_Open event cycle through all the Open workbooks for any with the name 'Normal Analysis' and if found, replaces all references in Formulas from 'Gamma Analysis' to 'Normal Analysis'
Sorry I'm not sure how I should approach this, I may just settle down to have 2 separate LCG as it seems to be the easiest method. Thank you for your help =)
The replace formulas was actually easier than I had expected.
So basically set your workbook up to always reference the "Normal Analysis" workbook and place the above as the Workbook_Open event. when it opens it checks if Gamma Analysis is open and if so, finds/replaces all formula references to "Normal" with "Gamma"Please Login or Register to view this content.
Thank you worked like a charm! ^^The replace formulas was actually easier than I had expected.
So basically set your workbook up to always reference the "Normal Analysis" workbook and place the above as the Workbook_Open event. when it opens it checks if Gamma Analysis is open and if so, finds/replaces all formula references to "Normal" with "Gamma"Please Login or Register to view this content.
One thing I've just thought with the above, if your workbook is then saved with a Gamma Calculation in then the code stops working! Could be worth sticking the reverse IF statement in replacing Gamma with Normal so it does the code for whichever is open.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks