Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-2883649%22%20slang%3D%22en-US%22%3EFormula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2883649%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20workbook%20that%20captures%20percentages%20from%20data%20produced%20by%202%20reports.%26nbsp%3B%20Both%20of%20these%20workbooks%20are%20required%20to%20open%20when%20my%20%22Percentages%22%20workbook%20is%20opened%20in%20order%20to%20capture%20these%20percentages.%26nbsp%3B%20The%20objective%20is%20to%20run%20these%202%20reports%20ever%20Monday%20but%20this%20is%20not%20always%20the%20case.%26nbsp%3B%20What%20I%20would%20like%20to%20do%20is%20record%20the%20date%20the%20Percentages%20workbook%20is%20opened%20and%20under%20that%20date%20capture%20the%20Percentages%20for%20historical%20purposes.%26nbsp%3B%20Another%20words%3B%20Column%20A%20has%20the%20Identifier%20words%2C%20ie..%2C%20Occupied%20%25%20as%20well%20as%20Occupancy%20%25%20w%2FDown%20Units%3A%20in%20it.%26nbsp%3B%20So%20that's%20saying%20A4%20and%20A6.%26nbsp%3B%20B4%20has%20the%20percentage%20for%20A4%20and%20B6%20has%20the%20percentage%20for%20A6.%26nbsp%3B%20What%20I%20would%20like%20to%20have%20happen%20is%20upon%20successful%20capturing%20of%20the%20percentages%2C%20the%20date%20be%20placed%20into%20cell%20B3.%20Now%20the%20next%20time%20the%20reports%20are%20run%20and%20the%20%22Percentages%22%20workbook%20is%20opened%20I%20like%20to%20have%20the%20percentages%20captured%20in%20cells%20C4%20%26amp%3B%20C6%20and%20the%20date%20be%20recorded%20and%20placed%20into%20cell%20C3.%26nbsp%3B%20Basically%2C%20I%20need%20to%20capture%20the%20percentages%20and%20record%20them%20each%20time%20the%20%22Percentages%22%20workbook%20is%20opened%20in%20the%20cells%20of%20the%20'Percentages%22%20workbook.%20This%20of%20course%20means%20the%20formula%20used%20to%20capture%20the%20previous%20percentages%20must%20no%20longer%20effect%20the%20previous%20percentages%20as%20the%20percentages%20will%20most%20likely%20not%20be%20the%20same.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20something%20anyone%20can%20help%20me%20with%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECarl%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2883649%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2883724%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2883724%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1171720%22%20target%3D%22_blank%22%3E%40Carl_61%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20provide%20sample%20workbooks%20with%20dummy%20data%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2884070%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2884070%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20the%20Workbooks%20I%20had%20sent%20to%20you%2C%20the%20Unit%20Vacancy%20Report%20and%20Unit%20Availability%20Report%20and%20the%20Percentages%20Workbook%20are%20the%20Workbooks%20I%20am%20speaking%20about.%20Using%20these%20same%20workbooks%20I%20just%20want%20to%20do%20as%20I%20mention%20in%20the%20above%20question.%20Do%20you%20still%20have%20access%20to%20them%3F%26nbsp%3B%20Otherwise%20I%20have%20to%20take%20some%20time%20to%20dummy%20up%20the%20new%20Workbooks.%26nbsp%3B%20Percentages%20workbook%20has%20hanged%20some%20because%20I%20came%20to%20realize%20the%20reports%20I%20was%20working%20with%20had%20been%20modified%20by%20someone%20already.%20I%20changed%20the%20formulas%20to%20reflect%20those%20changes%2Fcorrections.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2884435%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2884435%22%20slang%3D%22en-US%22%3ESorry%20Hans%2C%20I%20was%20still%20at%20work%20when%20I%20last%20posted%20and%20had%20to%20get%20on%20down%20the%20road%20to%20home.%20Iwill%20send%20you%20newer%20dummy%20uped%20workbooks%20tomorrow%20when%20I%20get%20a%20chance.%20Hopefully%20you%20see%20and%20understand%20what%20I%20am%20trying%20to%20accomplish.%3C%2FLINGO-BODY%3E
Contributor

I have a workbook that captures percentages from data produced by 2 reports.  Both of these workbooks are required to open when my "Percentages" workbook is opened in order to capture these percentages.  The objective is to run these 2 reports ever Monday but this is not always the case.  What I would like to do is record the date the Percentages workbook is opened and under that date capture the Percentages for historical purposes.  Another words; Column A has the Identifier words, ie.., Occupied % as well as Occupancy % w/Down Units: in it.  So that's saying A4 and A6.  B4 has the percentage for A4 and B6 has the percentage for A6.  What I would like to have happen is upon successful capturing of the percentages, the date be placed into cell B3. Now the next time the reports are run and the "Percentages" workbook is opened I like to have the percentages captured in cells C4 & C6 and the date be recorded and placed into cell C3.  Basically, I need to capture the percentages and record them each time the "Percentages" workbook is opened in the cells of the 'Percentages" workbook. This of course means the formula used to capture the previous percentages must no longer effect the previous percentages as the percentages will most likely not be the same.

 

Is this something anyone can help me with?

 

Carl 

6 Replies

@Carl_61 

Could you provide sample workbooks with dummy data?

@Hans Vogelaar 

Using the Workbooks I had sent to you, the Unit Vacancy Report and Unit Availability Report and the Percentages Workbook are the Workbooks I am speaking about. Using these same workbooks I just want to do as I mention in the above question. Do you still have access to them?  Otherwise I have to take some time to dummy up the new Workbooks.  Percentages workbook has hanged some because I came to realize the reports I was working with had been modified by someone already. I changed the formulas to reflect those changes/corrections.

Sorry Hans, I was still at work when I last posted and had to get on down the road to home. Iwill send you newer dummy uped workbooks tomorrow when I get a chance. Hopefully you see and understand what I am trying to accomplish.

@Carl_61 

I don't have the workbooks from your earlier discussion anymore.

Ok. I’ll hopefully get them to you later today. I’m not at work yet.

@Carl_61 

I replied to your private messages,