Copy data from one sheet to several sheets by using Formulas

Hi All,


Need to Split the Source Data in "Sheet1" into Multiple Sheets Using the "Example" Sheet in the Excel File .

Explanation is provided in the Excel file. Kindly do the needful.

If Need any Details or information Please let me know.


Thanks,

Please note that the Subject Title does not meet the requirement for a title - see Rule 1.

I'll change it for you since you are relatively new but note for the next time you start a thread.

What's the essential requirement here.

Is it to be able to see the data for each of the 4 basic sets of data or is it vital the data is split into four sheets.

A reoorganised set of data with a Pivot Table might offer a solution which will show you the annual results for each measure required

Originally Posted by Richard Buttrey

What's the essential requirement here.

Is it to be able to see the data for each of the 4 basic sets of data or is it vital the data is split into four sheets.

A reoorganised set of data with a Pivot Table might offer a solution which will show you the annual results for each measure required

Hi Richard,

I Have Given the sheet with Data for four columns (Sheet1) only for easy understanding, Generally i have data with many columns (100's of columns). Every Month i get the new Data. For simplicity i want to Sub Divided the Data into Sheets with Formulas. I'm thinking by Using the Formulas it is to easy to work.

Please Let me know your thoughts on this.

Thanks,

This is a proposed solution for the BR values. HD, HE and BA should follow the same pattern.
The dates are entered in column A using:
A2: =YEAR(MIN(Sheet1!A2:A6075))
A3:A30 =IF(A2< YEAR(MAX(Sheet1!A$2:A$6075)),SUM(A2,1),"")
The BR Max value of the year is populated using: =IF(A2="","",AGGREGATE(14,6,Sheet1!B$2:B$6075/(YEAR(Sheet1!A$2:A$6075)=A2),1))
The BR Min value of the year is populated using: =IF(A2="","",AGGREGATE(15,6,Sheet1!B$2:B$6075/(YEAR(Sheet1!A$2:A$6075)=A2),1))
The BR MinMax change % is populated using: =IF(A2="","",('Min-Max'!B2/'Min-Max'!C2)-1)
The BR start value of the year is populated using: =IF(A2="","",INDEX(Sheet1!B$2:B$6075,MATCH(AGGREGATE(15,6,Sheet1!A$2:A$6075/(YEAR(Sheet1!A$2:A$6075)=A2),1),Sheet1!A$2:A$6075,0)))
The BR end value of the year is populated using: =IF(A2="","",INDEX(Sheet1!B$2:B$6075,MATCH(AGGREGATE(14,6,Sheet1!A$2:A$6075/(YEAR(Sheet1!A$2:A$6075)=A2)/(Sheet1!B$2:B$6075<>"null"),1),Sheet1!A$2:A$6075,0)))
The BR StartEnd change % is populated using: =IF(A2="","",('Start-End'!C2/'Start-End'!B2)-1)
Let us know if you have any questions.

Hi JeteMC,

Thanks for your work. Everything is Perfect except in Min-Max Sheet. In Min Values , Starting value is getting "0" value. Attached the screenshot for reference.Is it possible to exclude zeros at the starting value?. Can you please verify..

Attachment 660805
Thanks.

My suggestion is to hide the zeroes using conditional formatting, providing you want to keep the General number formatting.
Select B2> Ctrl + Shift + right arrow > Ctrl + Shift + down arrow > Conditional Formatting > Format cells that contain > Cell value equal to 0 (zero) > select Font > Color > white
You could select custom number formatting and use something like: #.######;;;
I would not suggest modifying the formula i.e.

Formula:

Please Login or Register  to view this content.

as that causes the formula to run the aggregate calculation twice in each of the 208 cells.
Let us know if you have any questions.

If I understand correctly then try the following for D2 and down on the Min-Max sheet:

Formula:

Please Login or Register  to view this content.

D2 and down on the Start-End sheet:

Formula:

Please Login or Register  to view this content.

Selecting either of the attachments in post #10 results in the following message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator"
In the future please utilize the instructions in the banner at the top of the page to attach an .xlsx file.
Let us know if you have any questions.

Try: =IF(A2="","",IFERROR(AGGREGATE(15,6,Sheet1!C$2:C$6075/(YEAR(Sheet1!A$2:A$6075)=A2)/(Sheet1!C$2:C$6075>0),1),""))
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.