Gather data and placing into another sheet 1

[Marclem]

Hi

I have the following macro script which is running as expected but I would like to add two more values to the export sheet named NAV Export. I have attached the spreadsheet which you can see that at the moment it is only giving me data from FUND 1 GENADMIN showed under the NAV Export sheet after I added a row named TASK on top of each Fund (see row 34, 57, 80, 103..). Before it would give me data once I entered any dollar amount under each month per different Fund sections.

1 - I would like if possible to gather the data entered on any month for any particular fund, right now it is only looping thru the 1st Fund named GenAdmin
2 - I would like to be able to gather any TASK (see row 34, 57, 80, 103..) entered data to the proper fund into column K of the NAV Export sheet
3 - I would like to be able to gather data entered under row 53 (TOTAL INDIRECT EXPENSES (Recovery) that shows up on the other Funds as well (row 53, 76, 99, 122...) to be shown together with the other account number values (Worksheets("Nav Export").Range("E" & ExportRow).Value = AccountNumber)

I have researched online and tried a lot of different changes but I am not visual expert and request your help. Thank you

Sub Test_Macro()
'
' Test_Macro Macro
'

Dim Start As Range
Dim FundName
Dim ExportRow
'Dim ExportValue
Dim AccountNumber
Dim Month
Dim RC
Dim BudgetName
Dim Description
Dim Task

Sheets("MID YEAR REVIEW").Select

'Start = Range("B35")
RC = Range("C5").Value
Description = Range("C6").Value
BudgetName = Range("C7").Value
ExportRow = 1

If RC <> "" Then
'Clear our Export Tab
Worksheets("Nav Export").Range("A:Y").ClearContents

Range("B35").Select
Set Start = Range(ActiveCell.Address)
'Start.Select

While ActiveCell.Value <> ""
FundName = ActiveCell.Value
ActiveCell.Offset(1, 3).Select
For x = 1 To 15
AccountNumber = Range("A" & ActiveCell.Row).Value
For y = 1 To 12
If ActiveCell.Value <> "" And IsNumeric(ActiveCell) Then
Month = ActiveCell.Offset((x * -1), 0).Value
Worksheets("Nav Export").Range("A" & ExportRow).Value = Month
Worksheets("Nav Export").Range("C" & ExportRow).Value = RC & "16MID"
Worksheets("Nav Export").Range("D" & ExportRow).Value = "G/L Account"
Worksheets("Nav Export").Range("E" & ExportRow).Value = AccountNumber
Worksheets("Nav Export").Range("F" & ExportRow).Value = FundName
Worksheets("Nav Export").Range("I" & ExportRow).Value = RC
Worksheets("Nav Export").Range("Q" & ExportRow).Value = Description
Worksheets("Nav Export").Range("R" & ExportRow).Value = ActiveCell.Value
Worksheets("Nav Export").Range("R" & ExportRow).NumberFormat = "General"
Worksheets("Nav Export").Range("V" & ExportRow).Value = "G/L Account"
If BudgetName <> "" Then
Worksheets("Nav Export").Range("S" & ExportRow).Value = BudgetName
Else
Worksheets("Nav Export").Range("S" & ExportRow).Value = "MIDYEAR"
End If
ExportRow = ExportRow + 1
End If
ActiveCell.Offset(0, 1).Select
Next
ActiveCell.Offset(1, -12).Select
Next

ActiveCell.Offset(6, -3).Select

Wend
Range("C10").Select
MsgBox ("Finished!")
Else
MsgBox ("You must enter a Responsibility Center code to continue")
End If
End Sub

 

[SkipVought]

hi,


So this would be code added to your existing procedure, yes?

1 - I would like if possible to gather the data entered on any month for any particular fund, right now it is only looping thru the 1st Fund named GenAdmin

So the user would need to supply (select from a list) a FUND and a DATE. Is that correct?

2 - I would like to be able to gather any TASK (see row 34, 57, 80, 103..) entered data to the proper fund into column K of the NAV Export sheet

TASK: Do you mean a FUND? What "proper fund"? Is there a reason that the table on the NAV Export sheet has no heading values?

3 - I would like to be able to gather data entered under row 53 (TOTAL INDIRECT EXPENSES (Recovery) that shows up on the other Funds as well (row 53, 76, 99, 122...) to be shown together with the other account number values (Worksheets("Nav Export").Range("E" & ExportRow).Value = AccountNumber)

Huh? Why don't we have an example of what you are trying to describe. I'm not sure I understand: like UNDER row 53, meaning row 54? What column in that row? Other account number values?
Also your NAV sheet has data for Jan, Feb & Mar. What about, "gather the data entered on any month"? Do you mean any given month or any month containing data?

What happens in vagueness, stays in vagueness!


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[zelgar]

1. Try changing ActiveCell.Offset(6, -3).Select to ActiveCell.Offset(7, -3).Select
2. Currently, this information is being placed in Col F, if you want it in Col K make the following change to your current code:
Worksheets("Nav Export").Range("F" & ExportRow).Value = FundName to Worksheets("Nav Export").Range("K" & ExportRow).Value = FundName
3. I'm not certain what you want. First, you need'll to fix your spreadsheet so you don't have all of the #REF! errors (e.g., A121, A144...) so that if you're wanting to extract data from the TOTAL INDIRECT EXPENSES (Recovery) or TOTAL BUDGET you're not referencing a cell with a #REF! error which will end up crashing your macro.

 

[Marclem]

Hi Everyone,

I have uploaded the changes on the spreadsheet which now it no longer have REF# on the fields. If use don't enter any numeric amount under the months then it will not export the data (this is fine since I only want data that was entered)

Zelgar

I have made the changes you recommended as per my request 1 and it is once again looping thru the records, thank you

Now for the following:
2 - I would like to be able to gather any TASK (see row 34, 57, 80, 103..) entered data to the proper fund into column K of the NAV Export sheet (attached the updated spreadsheet look at the NAV Export - example sheet)
- SkipVought: correct, I do not need the headers - The FUND fields are fine, what I would like to do is if end-user enter any data in the TASK next column beside it (i.e. TEST-TASK - row 34 column B) I would like to include the entered data into column K of the nav export sheet (I have highlighted the end-result in the NAV Export - example sheet)
for example:
If you look at the FUND 1 (columnA) then in the same row under column B you can see entered data as: GENADMIN which when the macro runs it uploads GENAdmin under column F of the NAV Export sheet
I need to have as well any data entered for TASK to be shown in column K according with macro

3- As you can see the macro checks for any month entered numeric data doing a loop thru, it starts from range 35 onward which checks for any data entered under the months and it records the fund code under column E in the NAV Export sheet and the data for the whole row

I would like to as well be able to have the TOTAL INDIRECT EXPENSES (Recovery) code number shown under column E
Basically when end-user enter numeric amounts under below any months (JAN16, Feb16...) on any different FUND (FUND1, FUND2...), the current macro pulls data and exports to the NAV Export sheet.

I have attached a manual end result NAV Export sheet which you can see row 19 and 20 is the TOTAL INDIRECT EXPENSES (Recovery) row data taken from the main sheet

Thank you for your support!

 

[zelgar]

Here's the revised code that should take care of Items 2 & 3:

Sub Test_Macro()
'
' Test_Macro Macro
'
    
    Dim Start As Range
    Dim FundName
    Dim ExportRow
    'Dim ExportValue
    Dim AccountNumber
    Dim Month
    Dim RC
    Dim BudgetName
    Dim Description
    Dim Task
    
    Sheets("MID YEAR REVIEW").Select
    
    'Start = Range("B35")
    RC = Range("C5").Value
    Description = Range("C6").Value
    BudgetName = Range("C7").Value
    ExportRow = 1
    
    If RC <> "" Then
        'Clear our Export Tab
        Worksheets("Nav Export").Range("A:Y").ClearContents
        
        Range("B35").Select
        Set Start = Range(ActiveCell.Address)
        'Start.Select
        
        While ActiveCell.Value <> ""
            FundName = ActiveCell.Value
            Task = Range("b" & ActiveCell.Row - 1).Value
            ActiveCell.Offset(1, 3).Select
                        For x = 1 To 15
                AccountNumber = Range("A" & ActiveCell.Row).Value
                
                
                For y = 1 To 12
                    If ActiveCell.Value <> "" And IsNumeric(ActiveCell) Then
                        Month = ActiveCell.Offset(-x, 0).Value
                        Worksheets("Nav Export").Range("A" & ExportRow).Value = Month
                        Worksheets("Nav Export").Range("C" & ExportRow).Value = RC & "16MID"
                        Worksheets("Nav Export").Range("D" & ExportRow).Value = "G/L Account"
                        Worksheets("Nav Export").Range("E" & ExportRow).Value = AccountNumber
                        Worksheets("Nav Export").Range("F" & ExportRow).Value = FundName
                        Worksheets("Nav Export").Range("K" & ExportRow).Value = Task
                        Worksheets("Nav Export").Range("I" & ExportRow).Value = RC
                        Worksheets("Nav Export").Range("Q" & ExportRow).Value = Description
                        Worksheets("Nav Export").Range("R" & ExportRow).Value = ActiveCell.Value
                        Worksheets("Nav Export").Range("R" & ExportRow).NumberFormat = "General"
                        Worksheets("Nav Export").Range("V" & ExportRow).Value = "G/L Account"
                        If BudgetName <> "" Then
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = BudgetName
                        Else
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = "MIDYEAR"
                        End If
                        ExportRow = ExportRow + 1
                        End If
                    ActiveCell.Offset(0, 1).Select
                    Next
                    
                    ActiveCell.Offset(1, -12).Select
                Next
                ActiveCell.Offset(2, 0).Select
                For y = 1 To 12
                    If ActiveCell.Value <> "" And IsNumeric(ActiveCell) And ActiveCell.Value > 0 Then
                        Month = ActiveCell.Offset(-18, 0).Value
                        Worksheets("Nav Export").Range("A" & ExportRow).Value = Month
                        Worksheets("Nav Export").Range("C" & ExportRow).Value = RC & "16MID"
                        Worksheets("Nav Export").Range("D" & ExportRow).Value = "G/L Account"
                        Worksheets("Nav Export").Range("E" & ExportRow).Value = 49902
                        Worksheets("Nav Export").Range("F" & ExportRow).Value = FundName
                        Worksheets("Nav Export").Range("K" & ExportRow).Value = Task
                        Worksheets("Nav Export").Range("I" & ExportRow).Value = RC
                        Worksheets("Nav Export").Range("Q" & ExportRow).Value = Description
                        Worksheets("Nav Export").Range("R" & ExportRow).Value = Round(ActiveCell.Value, 0)
                        Worksheets("Nav Export").Range("R" & ExportRow).NumberFormat = "General"
                        Worksheets("Nav Export").Range("V" & ExportRow).Value = "G/L Account"
                        ExportRow = ExportRow + 1
                    End If
                    ActiveCell.Offset(0, 1).Select
                Next

                
            ActiveCell.Offset(5, -15).Select
            
           Wend
           Range("C10").Select
           MsgBox ("Finished!")
      Else
        MsgBox ("You must enter a Responsibility Center code to continue")
      End If
End Sub

 

[zelgar]

Alternatively, if instead of all of the Worksheets("Nav Export").Range("_" & ExportRow).Value = ____ you can use the Choose function to reduce the # of lines of code:

Sub Test_Macro2()
'
' Test_Macro Macro
'
    
    Dim Start As Range
    Dim FundName
    Dim ExportRow
    'Dim ExportValue
    Dim AccountNumber
    Dim Month
    Dim RC
    Dim BudgetName
    Dim Description
    Dim Task
    Dim i As Integer
    
    
    Sheets("MID YEAR REVIEW").Select
    
    'Start = Range("B35")
    RC = Range("C5").Value
    Description = Range("C6").Value
    BudgetName = Range("C7").Value
    ExportRow = 1
    
    If RC <> "" Then
        'Clear our Export Tab
        Worksheets("Nav Export").Range("A:Y").ClearContents
        
        Range("B35").Select
        Set Start = Range(ActiveCell.Address)
        'Start.Select
        
        While ActiveCell.Value <> ""
            FundName = ActiveCell.Value
            Task = Range("b" & ActiveCell.Row - 1).Value
            ActiveCell.Offset(1, 3).Select
                        For x = 1 To 15
                AccountNumber = Range("A" & ActiveCell.Row).Value
                
                
                For y = 1 To 12
                    If ActiveCell.Value <> "" And IsNumeric(ActiveCell) Then
                        Month = ActiveCell.Offset(-x, 0).Value
                        For i = 1 To 10
                            Worksheets("Nav Export").Range(Choose(i, "A", "C", "D", "E", "F", "K", "I", "Q", "R", "V") & ExportRow).Value = _
                                Choose(i, Month, RC & "16MID", "G/L Account", AccountNumber, FundName, Task, RC, Description, _
                                Round(ActiveCell.Value, 0), "G/L Account")
                        Next i
                        Worksheets("Nav Export").Range("R" & ExportRow).NumberFormat = "General"
                        If BudgetName <> "" Then
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = BudgetName
                        Else
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = "MIDYEAR"
                        End If
                        ExportRow = ExportRow + 1
                        End If
                    ActiveCell.Offset(0, 1).Select
                    Next
                    
                    ActiveCell.Offset(1, -12).Select
                Next
                ActiveCell.Offset(2, 0).Select
                For y = 1 To 12
                    If ActiveCell.Value <> "" And IsNumeric(ActiveCell) And ActiveCell.Value > 0 Then
                        Month = ActiveCell.Offset(-18, 0).Value
                        For i = 1 To 10
                            Worksheets("Nav Export").Range(Choose(i, "A", "C", "D", "E", "F", "K", "I", "Q", "R", "V") & ExportRow).Value = _
                                Choose(i, Month, RC & "16MID", "G/L Account", 49902, FundName, Task, RC, Description, _
                                Round(ActiveCell.Value, 0), "G/L Account")
                        Next i
                        Worksheets("Nav Export").Range("R" & ExportRow).NumberFormat = "General"

                        ExportRow = ExportRow + 1
                    End If
                    ActiveCell.Offset(0, 1).Select
                Next

                
            ActiveCell.Offset(5, -15).Select
            
           Wend
           Range("C10").Select
           MsgBox ("Finished!")
      Else
        MsgBox ("You must enter a Responsibility Center code to continue")
      End If
End Sub

 

[Andrzejek]

It would also be nice to have them declared as something other than Variants...

    Dim Start As Range
    Dim FundName [red]As ???[/red]
    Dim ExportRow [red]As ???[/red]
   [green] 'Dim ExportValue[/green]
    Dim AccountNumber [red]As ???[/red]
    Dim Month [red]As ???[/red]
    Dim RC [red]As ???[/red]
    Dim BudgetName [red]As ???[/red]
    Dim Description [red]As ???[/red]
    Dim Task [red]As ???[/red]
    Dim i As Integer

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Marclem]

Hi

First would like to thank everyone for your prompt responses!

Zelgar I am currently using your second macro which is working perfect! Thank you so much for your time and support on this!

 

[Marclem]

Hi Everyone,

Apologies beforehand for a long thread!

Based on the same spreadsheet (not sure if I need to create a new thread), I have two sheets named Actuals to Date and Budget (attached the updated spreadsheet).

I have created two buttons located at the MID YEAR REVIEW sheet, one named Copy Actuals to Date (I have assigned it to the Actuals_to_Date_Macro) and the other one named Copy Budget (I have assigned it to the Budget_Macro). Below what I would like the end-result to be if possible:

1- Actuals_to_Date_Macro

- Starting from MID YEAR REVIEW sheet range 35
- Copy only the amounts per each month from the Actuals to Date sheet filtering per fund (column B), per category (Column C) then copy its respective month numeric amounts into the MID YEAR REVIEW sheet into the correct month based on the Fund and the fund's category:

Basically:

- Add Fund Code name from Actuals to Date sheet's i.e. column B row 3 into MID YEAR REVIEW sheet's i.e. column B row 35 - If there is (are) more fund Code name then continue adding after every 22 rows (into every column B - row 23 - similar to the macro named NAVExport_Macro provided by Zelgar) then proceed below looping thru
- Match the following Cost Category from the Actuals to Date sheet with the MID YEAR REVIEW sheet then add the correct amount for the months from Actuals to Date sheet (note that currently it shows only months from January 2016 - June 2016, next month it will have a new column K for July) with MID YEAR REVIEW sheet:

Actuals to Date sheet MID YEAR REVIEW sheet
1_Salaries = Salaries
2_Actual Fringe = Actual Fringe
3_Allocated Fringe Benefits = Allocated Fringe Benefits
4_Allowances = Allowances
5_Office Expenses = Office Expenses
6_Intl Travel = Intl Travel
7_Local Travel = Local Travel
8_Equip and Soft = Equip and Soft
9_Outside Serv. = Outside Serv.
91_Vehicle = Vehicle Operations
92_Other Allowable Cost = Other Allowable Cost
93_Non-Allowable Costs = Non-Allowable Costs
95_P/T Disbursement = P/T Disbursement (excl. Sub awards)
95_P/T Subaward = P/T Sub awards
94_Capital Equipment >$5k = Capital Equipment >$5k
99_Indirect = TOTAL INDIRECT EXPENSES (Recovery)


2- Budget_Macro

- Starting from MID YEAR REVIEW sheet range 35
- Add Fund Code name from Budget sheet's i.e. column B row 3 into MID YEAR REVIEW sheet's i.e. column B row 35 - If there is (are) more fund Code name then continue adding after every 22 rows (into every column B - row 23 - similar to the macro named NAVExport_Macro provided by Zelgar) then proceed below looping thru
- Copy only the Total (column Q) amounts per each Cost Category from the Budget sheet filtering per fund (column B), per category (Column C) into the MID YEAR REVIEW sheet column C into the correct Cost category:
- Match the same Cost Category as shown above from Actuals to Date sheet with MID YEAR REVIEW sheet


Thank you for your support!

 

[zelgar]

Some questions & comments:
1. What is the data in Col A & B in the Actuals to Date and Budget sheets and how do they compare with the data in the MID YEAR REVIEW sheet?
2. The dates in the Actuals to Date sheet needs to be in the same format as in the MID YEAR REVIEW sheet (i.e., numbers in date format) in order to make things a lot easier.
3. Can you give an example of what you want the table to look like after running the macro's

 

[Marclem]

Hi Zelgar,

1. What is the data in Col A & B in the Actuals to Date and Budget sheets and how do they compare with the data in the MID YEAR REVIEW sheet?
- Basically the MID YEAR REVIEW sheet will have blank data under Col B row 35, then Col B row 58, then Col B row 80... which is where I would like the macro (separate macros will run as needed) to copy Col B in from both the Actuals to Date (Actuals_to_Date_Macro) and Budget (Budget_Macro) sheets to the MID YEAR REVIEW sheet per Fund Code changes (Col B row 35, then Col B row 58, then Col B row 80... of the MID YEAR REVIEW sheet)
[ul]
[li]As you can see both sheets have Col B which is named Fund Code that I would like to be added to the MID YEAR REVIEW sheet[/li]
[/ul]
Col A, I actually don't need any reference but Col B in both the Actuals to Date and Budget sheets

i.e.
I have attached an updated spreadsheet - highlighted in yellow is the data that should be added to the MID YEAR REVIEW sheet from the Actuals to Date sheet. Highlighted in green is the data that should be added to the MID YEAR REVIEW sheet from the Budget sheet

If you look at the Actuals Date sheet it has the Fund Code named MCARD005M (which would like MCARD005M to be added to the MID YEAR REVIEW Col B row 35)
[ul]
[li]Then from the Actuals Date sheet based on the above Fund named MCARD005M, add the amounts per the correct month[/li] as per each category matched
[/ul]
[ul]
[li]Then the next Fund Code in the Actuals Date sheet is named URF000004 (which would like URF000004 to be added to the MID YEAR REVIEW Col B row 58)[/li] as per each category matched
[/ul]
[ul]
[li]The continue adding the other Fund code names[/li]
[/ul]

If you look at the Budget Sheet sheet it also has the Fund Code named MCARD005M (which would like MCARD005M to be added to the MID YEAR REVIEW Col B row 35 or other row 58.. if one does not exist already - if it exists then add the data to correct place)
[ul]
[li]Then from the Budget sheet based on the above Fund named MCARD005M, add the Total amounts from Col Q of the Budget Sheet to the correct category matched from the Budget sheet and the MID YEAR REVIEW sheet into Col C[/li]
[/ul]
[ul]
[li]Then the next Fund Code in the Budget sheet is named URF000004 (which would like URF000004 to be added to the MID YEAR REVIEW Col B row 35 or other row 58.. if one does not exist already - if it exists then add the data to correct place)[/li]
[/ul]
[ul]
[li]The continue adding the other Fund code names and the Total amounts from Col Q[/li]
[/ul]


2. The dates in the Actuals to Date sheet needs to be in the same format as in the MID YEAR REVIEW sheet (i.e., numbers in date format) in order to make things a lot easier.
I believe I am not using Data formar just General format

3. Can you give an example of what you want the table to look like after running the macro's
I have highlighted the end-result


Thank you for you help on this!

 

[SkipVought]

The values in D2 on Actuals to Date is a real date. The remainder of the values in E2:I2 are TEXT (not real dates.

Here's what you need to do to get the first of the month from the date in D2:
[tt]
E2: =Date(Year(D2),Month(D2),1)
[/tt]
...and COPY this formula through to I2.

Format D2:I2 with the same date format.

faq68-5827.


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Marclem]

Hi Skip,

I believe when I copied and pasted the actuals to date data into the spreadsheet it for some reason it used D2 as a date format, the original actuals to date data shows up D2:I2 as TEXT, will that work having all as TEXT? I prefer if the script would run using the date January 2016... as TEXT since that's how the data will always be exported from our internal database.

Or if the answer is: it has to have date format because the MID YEAR REVIEW sheet date Jan-16, Feb-16 is setup as a custom date format, then what if I modified it as TEXT format as well? Would that work?


Thank you

 

[SkipVought]

Well do you need text or a date? They are two different animals. A date can be formatted any number of ways.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Marclem]

Hi Skip,

For me it does not matter, the Actuals to date sheet from Col D:I, row 2, the dates will always show up in there as TEXT as General category cell which needs to match with MID YEAR REVIEW sheet filtered by Fund with the correct month (in MID YEAR REVIEW the months are setup as DATE field with custom category as mmm-yy). I believe we don't have to worry about the date field as I am explaining below in more detail.

Attached the latest spreadsheet

Below I will try my best to summarize what I am trying to accomplish: (apologies but not sure how to best explain this)

When I run Macro named Actuals_to_Date_Macro I would like to
1- Clear any data located under the following cells starting from range 34 onward:
[ul]
[li]Col B row 34 and it shows up again after every 23 rows in the same Col B(which is the Task info), [/li]
[/ul]
[ul]
[li]Col B row 35 and it shows up again after every 23 rows in the same Col B(which is the Fund name info)[/li]
[/ul]
[ul]
[li]Col C from row 36-50 and it shows up again after every 9 rows[/li]
[/ul]
[ul]
[li]Col E-P from row 36-50 and it shows up again after every 9 rows[/li]
[/ul]
[ul]
[li]Col A row 52 (which is a percentage cell format field) and it shows up again after every 23 rows. set it back to 0%[/li]
[/ul]


2- Copy certain data from the Actuals to date sheet into certain fields of the MID YEAR REVIEW sheet:
- Loop thru all records in the MID YEAR REVIEW starting from range 35 onward Col B row 35 (it shows up again after every 23 rows) looking for a match fund name from the Actuals to Date sheet Col B row 3 (a new fund name shows up again after every Total name cell shows up under Col C)
- If not a match fund name is found then add the fund name from the Actuals to date sheet to the 1st available blank cell in MID YEAR REVIEW sheet (starts at Col B row 35 and it shows up again after every 23 rows in the same Col B(which is the Fund name info) -- If a match fund name is found then go towards the fund name in MID YEAR REVIEW sheet
- Before copying the numeric values from the Actuals to date sheet into the MID YEAR REVIEW sheet a match needs to be made with the Category names (MID YEAR REVIEW COL B row 36-50 and row 53), below how they will match with the Actuals to Date sheet Col C (starts at row 3 onward):
Actuals to Date sheet MID YEAR REVIEW sheet
1_Salaries = Salaries
2_Actual Fringe = Actual Fringe
3_Allocated Fringe Benefits = Allocated Fringe Benefits
4_Allowances = Allowances
5_Office Expenses = Office Expenses
6_Intl Travel = Intl Travel
7_Local Travel = Local Travel
8_Equip and Soft = Equip and Soft
9_Outside Serv. = Outside Serv.
91_Vehicle = Vehicle Operations
92_Other Allowable Cost = Other Allowable Cost
93_Non-Allowable Costs = Non-Allowable Costs
95_P/T Disbursement = P/T Disbursement (excl. Sub awards)
95_P/T Subaward = P/T Sub awards
94_Capital Equipment >$5k = Capital Equipment >$5k
99_Indirect = TOTAL INDIRECT EXPENSES (Recovery)
[ul]
[li]i.e. fund named MCARD005M in the Actuals to date sheet Col B row 3 would be added to MID YEAR REVIEW sheet col B row 35[/li][/ul]
[ul]
[li]In the Actuals to Date sheet the following category shows up (Col C row 3-7) that belongs to fund MCARD005M[/li][/ul]
5_Office Expenses
6_Intl Travel
8_Equip and Soft
9_Outside Serv.
92_Other Allowable Cost

- From the above Category matches I mentioned before, as an example 5_Office Expenses data from Col D-I row 3 will be added into MID YEAR REVIEW sheet Col E-J row 40
- 6_Intl Travel data from Col D-I row 4 will be added into MID YEAR REVIEW sheet Col E-J row 41
- 8_Equip and Soft data from Col D-I row 5 will be added into MID YEAR REVIEW sheet Col E-J row 43
- 9_Outside Serv. data from Col D-I row 6 will be added into MID YEAR REVIEW sheet Col E-J row 44
- 92_Other Allowable Cost data from Col D-I row 7 will be added into MID YEAR REVIEW sheet Col E-J row 46

Then every-time it sees the Total cell name in the Actuals to Date sheet Col C it needs to to start step 2 onward again to add the next Fund name (i.e. next fund would be URF000004) and add the numeric data values as per the matched categories between the two sheets...

I have highlighted in yellow the end-result from above example in MID YEAR REVIEW sheet


Thank you!