Looking ways to Automate Board Financials

[Zuhaib_Raja]

Hi All

 I wanted to automate my excel workbooks that I used for board financial. These are numerous worksheets in each workbook. Currently, I am encoding the values manually and changing the each charts by “Editing The Data Selecting” and it’s taking atleast 4 days to complete the overall worksheet. I want to automate the entire workbook and want to started with dynamic table and charts which mean if I input any data into X axis and Y axis, it automatically update my charts. Since I joined this job, I had assigned to work on these reports and I have no clue how old is these excel spreadsheets and It’s completely impossible to create a new ones because it has the date since 2015/2016 that populated with multiple rows. But I only show the data for most recent YTD that means current we are in March 2020 so I need to show in the graph only Feb 2019 through March 2020.

 Could anyone please suggest me ideas or youtube tutorials how could I do that? I was trying to follow Leila Tips through YouTube videos but in my case, I get lost due to so much data and I can’t create charts from zero (0). Also, I can’t change the formatting of my spreadsheet. Please give me some ideas how can I update the current charts with dynamic table (CTRL + T).

Thank you

[#2]

@Zuhaib_Raja 

Is it at all possible for you to render any actual names or other identifiers anonymous and then upload the actual sheets. In the absence of actually seeing what you're working with, it's awfully hard to give anything other than VERY general suggestions. And it sounds as if you've already got enough knowledge to know some general ideas....the very fact that you're talking of dynamic arrays (for example) suggests that you're not a total novice.

Old spreadsheets, though, can be something of a challenge, because new features often do expect certain layouts....anyway, I'm sure there are people here who could help, but we'd be far better equipped to help you if we could see the actual spreadsheets.

[#3]

@mathetes 
 Hi Mathetes
 Thank you for your feedback. As advised, I have prepared the sample to excel data spreadsheet that only consists of a few values and rows. Remember the one I am working have hundreds of rows and multiple worksheets and I can't change any formatting and also impossible to create new charts that are the combination of a variety of different layouts. In this excel I added the data since Feb 18 MM, YYYY but I only took the values of YTD since Jan 19. I do hide the other rows in the real dataset that I don't consider to mention in the graphs however I didn't hide in this excel sheet. I want to automate at this point, if I input any values in row # 48 to add Mar 20 values in automatically update/reflect into my charts. Any help in this regard will be appreciated

Thank you

[#4]

@Zuhaib_Raja 

The only thing I've done here at this point is to convert your data into a Table, named "Table1," and made that (by designating "=Table1" as the data range for the chart) the data source for the two charts. Doing those two steps has the result of the charts automatically including any new rows as they get added. 

So that's a start. Let us know if this resolves your situation. I'm going to guess that it won't fully, but you will need to come back with more details if more is needed.

[#5]

@mathetes 

Hi Mathetes

 Thank you for your support. However, I do have some concerns to discuss

1. First, how did you create the Table and assign the Table Name? Did you use CTRL + T command?
2. Second, how did you choose the data source?
3. Now, if you go back to my first concern/explanation and spreadsheet, I mentioned “In this excel I added the data since Feb 18 MM, YYYY but I only took the values of YTD since Jan 19. I do hide the other rows in the real working dataset/spreadsheet that I don't consider to mention in the graphs however I didn't hide in this excel sheet.” That being said, I need to customize my chart data, when you create the table and choose data from the table then you are selecting the data from the beginning if I am not correct. We can customize it manually but in this case, I want to automate that I need the data select of the last year only. For instance, if I am creating a report of April 20 then my data point will start from April 19 to April 20 and if I make a report May 20 then it will be May 19 to May 20. I hope it does make any sense.

Let me know if you have any questions

Thank you once again

[#6]

@Zuhaib_Raja 

• First, how did you create the Table and assign the Table Name? Did you use CTRL + T command?

I selected the whole data set, headings included, and used Insert....Table. The name was assigned by Excel and can be changed.

• Second, how did you choose the data source?

Right click with the cursor in the chart; then pick "Select Data...." from the menu that appears. At that point the image that I included with the last one shows that I just entered "=Table1" as the source data.

As to the rest of your questions, I'd have to do a lot of playing around with such steps as

(a) setting some start and finish dates for any reporting you want (as user-established variables) somewhere on one of your sheets

(b) the use of OFFSET and INDIRECT (or some such) to determine the dimensions of the subset of the full dataset that you want included.

In other words, I'm not sure how I'd resolve all of your concerns in order to make this fully automated. The restrictions you've articulated -- no freedom to change layouts, etc.--are probably things I'd want to challenge. Anyway, at this point I am interested in seeing what others come up with. I've used INDIRECT and OFFSET and the like on some of my own projects, but I'd need to "play" with them, which will take time. So I'm hoping some of the far more-expert experts here will chime in.

In the meantime, are you comfortable digging in yourself to see whether you could use functions such as those two I mentioned? Make a copy of your data that you can use without destroying anything... And then use sites like https://exceljet.net/ to explore both how to use those functions, but also follow their cross-references to other features. That's what I would do were I in your place.

[#7]

@mathetes 

 Hi

 Thank you so much for your help and responses. I came up with another solution. I tried to follow this approach however, I was unable to create a dynamic table. When I tried to create a table "CTRL + T", it changes my header names into Column 1, Column 2 and so on. Also, it doesn't add the new values/dates/fields into my From and To Date fields that I created to select my chart ranges. Please check this video and let me know where I am lacking

https://www.youtube.com/watch?v=sHfWRb2yUrM

Thank you

[#8]

@Zuhaib_Raja 

I haven't watched the video in its entirety yet but can see that it would be helpful. I am afraid I'd need to see your actual spreadsheet, though, to be able to tell you what you're lacking. Without that I'd just be speculating. Can you replicate your situation with the sample database you created earlier?

[#9]

@Zuhaib_Raja 

I've watched that video all the way through now, and am looking forward to seeing your actual spreadsheet to see if I can identify what you might have missed. What she described is complicated in its details, so it's not hard to imagine having difficulties the first times you use it. But the results are valuable for the kind of situation you are facing.

[#10]

@mathetes 
 Thank you so much for helping out and keep showing the interest to solve this issue. Unfortunately, I am unable to share the actual sheet on which I am working on. That's too confidential data, however I tried to create the sample data for public but unluckily, the sample data is working perfectly the way I wanted to design LOL meaning it's complete automated. But when I work with actual excel worksheet with same logic, approach and functionality then I am not getting the same results meaning I couldn't integrate the Table selection with drop down list so whenever I add new values it doesn't pop up the newly encoded data/values into my drop down list. I am hustling with this situation only. I tried to work in many ways but I am stuck though it worked in the sample data sheet but not the actual one  :(

[#11]

@Zuhaib_Raja 

Somebody else earlier today was helped with this creation of a dynamic drop down. Go look through this thread for the contribution @Riny_van_Eekelen made and I think you might find your answer.

https://techcommunity.microsoft.com/t5/excel/help-with-data-calculations/m-p/1303683#M58581

[#12]

@Zuhaib_Raja 

I just came across a very helpful YouTube video, produced by Microsoft a year or two ago. In it, the use of new functions--I'm trusting you now have access to them--used in connection with Dynamic Arrays, could very well be even better for your purposes than the Pivot Table. 

The full video is nearly an hour in duration, but it will save you far more than that amount of time.

https://www.youtube.com/watch?v=9I9DtFOVPIg