Tags:Microsoft, Excel, 2007, Network on a University
I'm attaching a spreadsheet from a project that I have to complete within 2-3 days. As can be seen, it is 4:00 am, and I've been working on this since yesterday morning. Because it is a very complicated multi-step process, that includes VLOOKUP type conversions, columns being added, departments filtered, totals produced, and the results transferred to the final budget sheet, within a very short period of time, the person in charge can determine the point value of helping with portions of this project. Maybe it's too large, or impossible to really do, I don't know, but this is what I've been tasked with, and any help would be appreciated.
The steps are listed that need to occur, from receiving the "Raw Data" through to the finished product. Everything is now done manually, with some steps producing a Pivot table which is then copied and pasted into the Dept's. worksheet cell. I don't know if a Pivot needs to be produced , and did not include one, if the Categories and Accounts can eventually just be totaled and transferred to the correct cell. If anyone who has experience with something as complicated as this could suggest a good starting area to efficiently do this, that would help also. I will go home for a couple of hours and then begin again.
Could you explain briefly what each worksheet is for, and give an example of a number that should appear in the first period on the budget summary, plus how it should be calculated?
The Worksheet being produced is the Dept Budget for the year and goes to about 30 departments. When we get the final version Tuesday Morning, all of the steps, which are currently being done manually, will have to be completed and sent out to each department by noon that day.
I have already completed renaming the Months, inserting the two columns and created a VLOOKUP for both columns within the Raw Data worksheet. Since the Raw Data has more account codes than we use, I tried to find a way to return any value between 50 and 70 to one Category Name (General & Admin Exp) while still being able to use the others as is. I could not find a way to do this, and since there was no one to help, I just manually entered each number 50, 51, etc. in the first column and then the same Category Name in the second column. To try and make things easier, I've also created Named Ranges for the VLOOKUP Function.
There are four steps remaining
1. Remove the leading 0 in the Dartment_Numbers column I've been trying to find an easy way of removing the leading 0 and believe that the ExtactElement function should work.
2. Remove Unnecessary Rows I think I know how to remove all of the Rows in the Department_Name column that have just a 00* or any number greater than 300 (we sometimes get errors in the Raw Data) or any row in Col B Group1_DESC that has FTE in it. I found some code on this site that may work.
The next two steps will be critical, that I really would like some help:
3. Filter and copy from each unique department from the Department column to their own separate worksheet and name the worksheet tab by the Department. Only Column F Categories and the Month Columns need to be copied to the separate worksheet. However, the tricky part is copying Categories since their values are due to a VLOOKUP value.
4. Total the values for each Category , under each Month column, to create a single entry, that will go under each Month, and then Delete the individual rows. The number of rows that have values for each Category will vary.
Each Worksheet should then have only 5-6 rows with totals under each Month.
Each Worksheet created would look something like this:
For Department M1500 (M1500 on Tab)
Categories Sep Oct Nov Jan Feb Mar Apr May Jun Jul Aug FFS Revenue 50,000 49,000 xxx & Salaries & Benefits 30,000 10,500 xxx&
I hope this helps explain a little better some of the tasks with this project.
Without going in to details, this looks like a problem which pivot tables might help in - you can output each department to a separate page, hide certain values and rename columns. But I haven't gone through all the data yet.
