Good Morning,
I have established a SQL connection to the database of our ERP system into a sheet called "Raw_Data" that contains our GL Account Data. I have created a pivot table using that data and it is reconciling correctly. Now I want to use the "Raw_Data" sheet and create another sheet that formats the data into data that I can use in a Pivot Table that is more end user friendly and will automatically update whenever an account is added to the "Raw_Data" Currently, the "Raw-Data" sheet looks like the data below. I can use a pivot table to show this information and it would look like the following:
TYPE GL ACCOUNT NUMBER GL ACCOUNT DESCRIPTION
<colgroup><col span="2"></colgroup><tbody>
</tbody>
The raw_data above is not end user friendly, so I created formulas in a separate sheet called "formatted_data" that yes the raw_data and puts it into columns that uses a more user friendly naming convention. In parenthesis is the raw_data for that is translated into what it means:
TYPE FUND FUNCTION LEVEL LOCATION PROGRAM OBJECT
EXPENSES (E), GENERAL FUND (1110), REGULAR ED (0000), DISTRICT (00), CENTRAL (00), GENERAL PROGRAM (00), CEOPA (611130)
EXPENSES (E), GENERAL FUND (1110), REGULAR ED (0000), DISTRICT (00), CENTRAL (00), GENERAL PROGRAM (00), CEAA (611150)
EXPENSES (E), GENERAL FUND (1110), REGULAR ED (0000), DISTRICT (00), CENTRAL (00), GENERAL PROGRAM (00), CUSTODIANS (611160)
Here are is a couple examples I used to parse out the data into columns so I can make a more end user friendly pivot table.
=IF(VALUE(LEFT(AK11,1))=5,"REVENUE",IF(VALUE(LEFT(AK11,1))=6,"EXPENSES","Check"))
=LEFT(AT11,4)
=VLOOKUP(D11,FundTitle,2,FALSE)
=IF(ROUNDDOWN(M11,-2)=0,"0000",ROUNDDOWN(M11,-2))
=VLOOKUP(G11,FunctionSummaryTitles,2,FALSE)
By creating this user friendly sheet "formatted_data" and I am able to create pivot tables that are more friendly to my department heads. I am currently pasting the raw numbers into fields in an excel spreadsheet and when we add an account number, I copy the formula down. I would like to have the raw data refresh in one sheet and it automatically pickup new accounts in the formatted_data sheet if the account number changes.
I am currently using named ranges and vlookup, on the static data, which works, but it doesn't help me when a new account is added. Any assistance is greatly appreciated!
Jack
I have established a SQL connection to the database of our ERP system into a sheet called "Raw_Data" that contains our GL Account Data. I have created a pivot table using that data and it is reconciling correctly. Now I want to use the "Raw_Data" sheet and create another sheet that formats the data into data that I can use in a Pivot Table that is more end user friendly and will automatically update whenever an account is added to the "Raw_Data" Currently, the "Raw-Data" sheet looks like the data below. I can use a pivot table to show this information and it would look like the following:
TYPE GL ACCOUNT NUMBER GL ACCOUNT DESCRIPTION
| E 1110-0000-00-00-000-611130- | GF-GE-DW-GP-SalCvdCEOPA |
| E 1110-0000-00-00-000-611150- | GF-GE-DW-GP-SalCvdCEAA |
| E 1110-0000-00-00-000-611160- | GF-GE-DW-GP-SalCvdCustodian |
<colgroup><col span="2"></colgroup><tbody>
</tbody>
The raw_data above is not end user friendly, so I created formulas in a separate sheet called "formatted_data" that yes the raw_data and puts it into columns that uses a more user friendly naming convention. In parenthesis is the raw_data for that is translated into what it means:
TYPE FUND FUNCTION LEVEL LOCATION PROGRAM OBJECT
EXPENSES (E), GENERAL FUND (1110), REGULAR ED (0000), DISTRICT (00), CENTRAL (00), GENERAL PROGRAM (00), CEOPA (611130)
EXPENSES (E), GENERAL FUND (1110), REGULAR ED (0000), DISTRICT (00), CENTRAL (00), GENERAL PROGRAM (00), CEAA (611150)
EXPENSES (E), GENERAL FUND (1110), REGULAR ED (0000), DISTRICT (00), CENTRAL (00), GENERAL PROGRAM (00), CUSTODIANS (611160)
Here are is a couple examples I used to parse out the data into columns so I can make a more end user friendly pivot table.
=IF(VALUE(LEFT(AK11,1))=5,"REVENUE",IF(VALUE(LEFT(AK11,1))=6,"EXPENSES","Check"))
=LEFT(AT11,4)
=VLOOKUP(D11,FundTitle,2,FALSE)
=IF(ROUNDDOWN(M11,-2)=0,"0000",ROUNDDOWN(M11,-2))
=VLOOKUP(G11,FunctionSummaryTitles,2,FALSE)
By creating this user friendly sheet "formatted_data" and I am able to create pivot tables that are more friendly to my department heads. I am currently pasting the raw numbers into fields in an excel spreadsheet and when we add an account number, I copy the formula down. I would like to have the raw data refresh in one sheet and it automatically pickup new accounts in the formatted_data sheet if the account number changes.
I am currently using named ranges and vlookup, on the static data, which works, but it doesn't help me when a new account is added. Any assistance is greatly appreciated!
Jack
