Automate the Formulas I must apply to my Quarterly / Yearly Data

[Leicester City Fox]

Hi All

Good Morning

I am looking for ideas and examples that I can apply to work on the easiest and quickest way to work in my job, I am given a bunch of data and asked to put in formula to into cells and columns that workout the from data proved following ( Quarter to Quarter Comparisons / Yearly (four Quarters) :

I always have to apply manually the following formulas to the quarter or yearly data:

• Workout the Quarter from date
• Workout Age of Person from DOB
• Remove Duplicate IDs (unique ids)
• Count number Duplicate IDs for certain provider
• Workout if person is “Working Age” 18 to 64 and 65 + “Elderly”
• Percentage Change (Increase / Decrease) compression of quarter figure against last quarter
• Workout the percentage of different services the person uses
• Workout the Average figure from the quarters provided against previous quarters or years
• Then I produce Charts (Pie Chart, Bar Chart, Graphs showing Quarter Changes for the points above).

Instead of me have to manually always enter a formula above to work out the Quarter to Quarter Comparison or yearly (four quarters).

I was wonder if there away where I can click on a cell or column and get it to apply the automatically any of the formulas above by pressing on button instead of me manually having to put them in each time.

Some times the data comes in different format not the same column or cell that why I want the formula to only apply to any cell or column I click into.

Also, it be a bonus if I could get to automatically do graphs instead just putting the data in pivots once I formatted the data and applied the formulas above and ten crate the graphs manually.

I am looking for ideas and solutions the easiest way to do this in excel to achieve this if possible, with examples how it can be done this and best and quickest way to save time.

I thank you for your ideas and examples in order to me the way I work less receptive by always adding the same old formulas to my data.

Many Thanks

The Leicester Fox

 

[Chrisdontm]

Since no one is answering, this is how I would work on this.

On my first sheet rrange the downloaded information one time into the columns I want them to be.

Then, on the second worksheet create formulas to get the information from the first worksheet.

Then, the next time I get a download, rearrange the information how I want, then copy it into the first sheet that I set up. My formulas on the second tab will already look in the right columns.

I hope this helps you some.

 

[vmjan02]

@Leicester City Fox

Can you be more clear
1) are you saying that the activity you are doing it daily and that to manual?
2) Do you want to automate it?
3) If 2 is yes, then would require more information and some sample data to automate the same.

 

[Leicester City Fox]

Since no one is answering, this is how I would work on this.

On my first sheet rrange the downloaded information one time into the columns I want them to be.

Then, on the second worksheet create formulas to get the information from the first worksheet.

Then, the next time I get a download, rearrange the information how I want, then copy it into the first sheet that I set up. My formulas on the second tab will already look in the right columns.

I hope this helps you some.

Hi Chrisdom

Good morning

Thank you for taking the time out to reply and thanks for the idea as I just trying to look for better ways to work

Many Thanks

The Leicester Fox

 

[Leicester City Fox]

@Leicester City Fox

Can you be more clear
1) are you saying that the activity you are doing it daily and that to manual?
2) Do you want to automate it?
3) If 2 is yes, then would require more information and some sample data to automate the same.

Hi Vmjam02

Good Morning

Please see answers to your question:

1) are you saying that the activity you are doing it daily and that to manual? It Quarterly reporting I apply the formula every quarter
2) Do you want to automate it? I want to Automate these formulas or Just create a button that does them for me each time
3) If 2 is yes, then would require more information and some sample data to automate the same. The 8 Formulas I use are Standard excel Formulas I apply to the columns of Data to get answer. This site don't let you a attach data samples . But the formulas are standard to get the results.

I thank you for your consideration

The Leicester Fox

 

[vmjan02]

Good Morning,

Use this code and it will do the automation part for all your 8 formulas. do it for 8 diff times.

With Worksheets("") ' add the worksheet name here
    With .Range("C2:C" & .Range("A" & .Rows.Count).End(xlUp).Row)
        .Formula = "=TEXT(B3,""MM/DD/YYYY"")" 'Change the formula here'
        .Value = .Value
     End With
End With