I am having a problem and for ease of reading, I will expose my problem by topics. The first three topics explain how the worksheet is organized and the others what I want to do.
i) Basically, I have like a "main" sheet with all staff expenses and it is organized in 7 columns and around 2000 rows. The columns are: "staff" (with the staff names like "Michael"), "Expense code" (like "S123P-Taxi"), "Staff RC" (like "T3310"), "BRC" (like "5432"), "Description" (like "Professor"), "Expense date" (like "16-03-2015") and "Gross Expense" (like "400").
ii) This "main" sheet is also organized by groups and sub-groups. There are two hierarchy levels for groups. For instance imagine that "Michael" have more than one expense code, so "Michael" is a group with many expense codes inside like "S017P - Taxi", "S019P - Meals" and "S012P - Other transports". Now each sub-group can have more than one line associated with, and these lines are filled with the respective information (see next schema).
Stafff | Expense code | Staff RC | BRC | Description | Expense date | Gross expense
Michael
______ S017P-Taxi
__________________M3310____5432____Professor_____16-03-2015 __440
__________________M3310____5432____Professor _____16-07-2015 __450
______S019P-Meals
__________________M3310____5432____Professor_____16-03-2015 ___24
__________________M3310____5432____Professor _____28-03-2015 ___18
Sarah
______S123P-Taxi
__________________M3324____5433____Student ______16-03-2015 ___200
__________________M3324____5433____Student ______16-07-2015 ___450
iii) In the previous example "Michael" and "Sarah" are first level groups and "S017P-Taxi" and "S019P-Meals" second level groups. Besides this "main" sheet I have another sheet, also organized hierarchically by groups, but with more sub-levels like it will be illustrated. The information on the two sheets are the same but are differently organized. The second sheet is presented on the next schema.
Year|Month & Year|Expense Code|Staff|Staff RC|BRC| Description|Exp. date|GrossExp
2015
_____March 2015
__________________S123P-Taxi
_____________________________Michael
___________________________________M3310_5432_Professor_16-03-2015_440
_____________________________Sarah
___________________________________M3324_5433_Student _16-03-2015_200
__________________S019P-Meals
_____________________________Michael
__________________________________ M3310_5432_Professor_16-03-2015__24
__________________________________ M3310_5432_Professor_28-03-2015__18
_____July 2015
__________________S123P-Taxi
_____________________________Michael
__________________________________M3310_5432_Professor_16-07-2015_450
__________________S123P-Taxi
_____________________________Sarah
__________________________________M3324_5433_Student_16-07-2015__450
iv) So, when adding some new line with some new information at "main" sheet I want that this new info will be automatically added to the second sheet and it must be correctly organized by date. The new row must be placed on the correct subgroup of the second sheet, which means that it must correspond to a specific year, month, expense code and person. Each new row must be organized by date. Note that I have information for all months of 2014, 2015 and 2016.
Do you have any idea how can I do this? I think that I will need to use Macros to make this thing work. If you need further information please ask.
Thank you very much for your time and sorry for the length of this post.
Bookmarks