Sage 50:Extract/Amend/Import Nominal Accounts
PostTrans imports Nominal Accounts, which could be manually entered with the assistance of in-cell
searching, using copy and paste,
imported from CSV file or extracted from
Sage 50 using the Extract button.
Related Pages
Import Nominal Account Features in Sage 50
Simply add new lines to the spreadsheet from row 30 on-wards. Then press Import button, on the PostTrans Toolbar, to import single or multiple Suppliers (Examples shown below).
Advantages
- Highlighted Validation of codes and data
- Search for Account, Stock, Project, Nominal, using our In-Cell Searching
- Add formula, vba or macros to manipulate the imported data
- Press Extract to populate the sheet with existing data, amend and Import
- Easy importation from CSV file
Examples of importing Sage 50 Nominal Accounts
Nominal Account template for importing into Sage 50
Demonstration Video
Sage 50 - Posting a Nominal Journal with VAT PostTrans
| |
0:00 |
Video Introduction |
| |
1:50 |
Opening Nominal Journal Example |
| |
2:30 |
Posting a Normal Nominal Journal into Sage 50 |
| |
3:25 |
Posting a Nominal Journal with Contra Nominal line |
| |
4:00 |
Using In cell searching to enter nominal lines |
| |
6:10 |
Posting a VAT Nominal Journal into Sage 50 |
| |
7:20 |
How to find more information and Training |
Template Structure for Static Data
The template must have tag ‘Sage50nom’ in cell A1 to instruct PostTrans that we are interested in
Sage 50 Nominal Accounts, and that the sheet is a PostTrans Template.
Pressing Import or Extract on a blank sheet will either open an existing
example or create basic template for you to customise.
Row 1 contains a series of tags, which denote to PostTrans the data to be read/written in each column. These tags, and data columns can be rearranged to
suit your needs, and the tags are added using the Tags button.
See on-line Training
Deleting the Tag name, in the cell, from this row removes the Sage 50
mapping. Table of Nominal Accounts Tags
Hovering over these tags, in row 1, will show a comment which explains the tags purpose, data type and any other useful details.
Common Features
In-Cell Searching
Any Tag ending with ^ with have a in-cell search facility to look up associated values. Use SPACE + TAB to see all values, or SPACE and
TEXT to perform a text search on description.
These are returned to the cell as "CODE, DESCRIPTION". PostTrans
only uses the CODE and ignores the comma and anything after it, thus data from a field
would only need the CODE part of the data if from an external imported
file.
In-cell Searching can be turned off by removing the ^ from the end of the tag name, or name in first line of comment (Header cells) to disable in-cell searching for a single column, or header cell.
All in-cell searching can be turned off in Setup. In-cell
searching.
Extracting the Data
The data extracted using the Extract button can be filtered, by entering criteria in row 2. Hover over cell A2 for instructions.
Extracting Data
Amending Nominal Accounts Data
Having extracted data, the user can now simply edit the Nominal Accounts in Microsoft Excel, before re-importing it back into
Sage 50.
PostTrans uses the tagged column NoCRC to detect changes in rows.
Therefore,
only records/rows which have changed since extraction are updated. If
you add or delete a tagged column, PostTrans will treat ALL rows as having
changed, and thus ALL records will be updated on next Import.
Importing the Data
Simply press the Import button to validate and import the data into
Sage 50.
PostTrans will read the Header cells and then process the rows of data until NoCode
column contains no data. The Sage Company imported into can be set in the Setup window.
PostTrans ONLY edits those fields listed on the sheet when importing, thus
any existing values, on a record, are unaffected. New records may
have default values for certain fields.
File Import Button
This can be enabled in Setup to easily import cvs, or fixed length files, into sheet for importation. Formula can also be added, or macros/VBA
to manipulate this data before importation. Import from CSV file.
Validation
Any values and associated codes will be invalid during import.
The cursor will be moved to highlight any problems in data.
Evaluation and License
This functionality will work with Sage Demo data without a license. A license is required to import into any other Sage Company.
Pricing
What else can I do?
Other Sage 50 Extract/Import types
Frequently Asked Questions?
Got another question? It may well be covered on our Frequently Asked Questions page.
Other Useful Topics
Clear Transaction and copy down formula
Not all these tags/columns are needed. The table below lists the required and recommended:
|
Tag Name
|
Data Type
|
Description
|
|
Important Fields:
|
|
Sage50nom
|
Text Req
|
Returns `POSTED` if Trans line has been posted and will be ignored if `POSTED` (required). Also reports errors/warnings in data row
|
|
NoCRC
|
Text Req
|
Contains CRC number to help PostTrans detect changes in current row.
|
|
NoCode
|
Text 8 EOF Req
|
Department Code
|
|
NoName
|
Text 60 Req
|
Department Name
|
|
NoType^
|
Integer
|
Account Type Code
In Cell Searching values:Nominal Code
1, Normal
2, Bank
4, Control
|
|
NoInactive
|
Yes/No-
|
Is Inactive
Sage V24 2018 onwards
|
|
NoAuditRef
|
Text 20
|
Incomplete Records Reference
|
|
NoBal
|
Double
|
Balance
|
|
Balance:
|
|
NoBalBF
|
Double
|
Balance Brought Forward
|
|
NoBalMon1
|
Double
|
Balance Month 1
|
|
NoBalMon2
|
Double
|
Balance Month 2
|
|
NoBalMon3
|
Double
|
Balance Month 3
|
|
NoBalMon4
|
Double
|
Balance Month 4
|
|
NoBalMon5
|
Double
|
Balance Month 5
|
|
NoBalMon6
|
Double
|
Balance Month 6
|
|
NoBalMon7
|
Double
|
Balance Month 7
|
|
NoBalMon8
|
Double
|
Balance Month 8
|
|
NoBalMon9
|
Double
|
Balance Month 9
|
|
NoBalMon10
|
Double
|
Balance Month 10
|
|
NoBalMon11
|
Double
|
Balance Month 11
|
|
NoBalMon12
|
Double
|
Balance Month 12
|
|
NoBalFut
|
Double
|
Balance Future
|
|
Budget:
|
|
NoBudgetMon1
|
Double
|
Budget Month 1
|
|
NoBudgetMon2
|
Double
|
Budget Month 2
|
|
NoBudgetMon3
|
Double
|
Budget Month 3
|
|
NoBudgetMon4
|
Double
|
Budget Month 4
|
|
NoBudgetMon5
|
Double
|
Budget Month 5
|
|
NoBudgetMon6
|
Double
|
Budget Month 6
|
|
NoBudgetMon7
|
Double
|
Budget Month 7
|
|
NoBudgetMon8
|
Double
|
Budget Month 8
|
|
NoBudgetMon9
|
Double
|
Budget Month 9
|
|
NoBudgetMon10
|
Double
|
Budget Month 10
|
|
NoBudgetMon11
|
Double
|
Budget Month 11
|
|
NoBudgetMon12
|
Double
|
Budget Month 12
|
|
Credit:
|
|
NoCreditBF
|
Double
|
Credit Brought Forward
|
|
NoCreditMon1
|
Double
|
Credit Month 1
|
|
NoCreditMon2
|
Double
|
Credit Month 2
|
|
NoCreditMon3
|
Double
|
Credit Month 3
|
|
NoCreditMon4
|
Double
|
Credit Month 4
|
|
NoCreditMon5
|
Double
|
Credit Month 5
|
|
NoCreditMon6
|
Double
|
Credit Month 6
|
|
NoCreditMon7
|
Double
|
Credit Month 7
|
|
NoCreditMon8
|
Double
|
Credit Month 8
|
|
NoCreditMon9
|
Double
|
Credit Month 9
|
|
NoCreditMon10
|
Double
|
Credit Month 10
|
|
NoCreditMon11
|
Double
|
Credit Month 11
|
|
NoCreditMon12
|
Double
|
Credit Month 12
|
|
NoCreditFut
|
Double
|
Credit for Future
|
|
Debit:
|
|
NoDebitBF
|
Double
|
Debit Brought Forward
|
|
NoDebitMon1
|
Double
|
Debit Month 1
|
|
NoDebitMon2
|
Double
|
Debit Month 2
|
|
NoDebitMon3
|
Double
|
Debit Month 3
|
|
NoDebitMon4
|
Double
|
Debit Month 4
|
|
NoDebitMon5
|
Double
|
Debit Month 5
|
|
NoDebitMon6
|
Double
|
Debit Month 6
|
|
NoDebitMon7
|
Double
|
Debit Month 7
|
|
NoDebitMon8
|
Double
|
Debit Month 8
|
|
NoDebitMon9
|
Double
|
Debit Month 9
|
|
NoDebitMon10
|
Double
|
Debit Month 10
|
|
NoDebitMon11
|
Double
|
Debit Month 11
|
|
NoDebitMon12
|
Double
|
Debit Month 12
|
|
NoDebitFut
|
Double
|
Debit for Future
|
|
Prior Years:
|
|
NoPriYrMon1
|
Double
|
Prior Year 1 Month 1
|
|
NoPriYrMon2
|
Double
|
Prior Year 1 Month 2
|
|
NoPriYrMon3
|
Double
|
Prior Year 1 Month 3
|
|
NoPriYrMon4
|
Double
|
Prior Year 1 Month 4
|
|
NoPriYrMon5
|
Double
|
Prior Year 1 Month 5
|
|
NoPriYrMon6
|
Double
|
Prior Year 1 Month 6
|
|
NoPriYrMon7
|
Double
|
Prior Year 1 Month 7
|
|
NoPriYrMon8
|
Double
|
Prior Year 1 Month 8
|
|
NoPriYrMon9
|
Double
|
Prior Year 1 Month 9
|
|
NoPriYrMon10
|
Double
|
Prior Year 1 Month 10
|
|
NoPriYrMon11
|
Double
|
Prior Year 1 Month 11
|
|
NoPriYrMon12
|
Double
|
Prior Year 1 Month 12
|
|
NoPriYr2Mon1
|
Double
|
Prior Year 2 Month 1
|
|
NoPriYr2Mon2
|
Double
|
Prior Year 2 Month 2
|
|
NoPriYr2Mon3
|
Double
|
Prior Year 2 Month 3
|
|
NoPriYr2Mon4
|
Double
|
Prior Year 2 Month 4
|
|
NoPriYr2Mon5
|
Double
|
Prior Year 2 Month 5
|
|
NoPriYr2Mon6
|
Double
|
Prior Year 2 Month 6
|
|
NoPriYr2Mon7
|
Double
|
Prior Year 2 Month 7
|
|
NoPriYr2Mon8
|
Double
|
Prior Year 2 Month 8
|
|
NoPriYr2Mon9
|
Double
|
Prior Year 2 Month 9
|
|
NoPriYr2Mon10
|
Double
|
Prior Year 2 Month 10
|
|
NoPriYr2Mon11
|
Double
|
Prior Year 2 Month 11
|
|
NoPriYr2Mon12
|
Double
|
Prior Year 2 Month 12
|
|
NoPriYr3Mon1
|
Double
|
Prior Year 3 Month 1
|
|
NoPriYr3Mon2
|
Double
|
Prior Year 3 Month 2
|
|
NoPriYr3Mon3
|
Double
|
Prior Year 3 Month 3
|
|
NoPriYr3Mon4
|
Double
|
Prior Year 3 Month 4
|
|
NoPriYr3Mon5
|
Double
|
Prior Year 3 Month 5
|
|
NoPriYr3Mon6
|
Double
|
Prior Year 3 Month 6
|
|
NoPriYr3Mon7
|
Double
|
Prior Year 3 Month 7
|
|
NoPriYr3Mon8
|
Double
|
Prior Year 3 Month 8
|
|
NoPriYr3Mon9
|
Double
|
Prior Year 3 Month 9
|
|
NoPriYr3Mon10
|
Double
|
Prior Year 3 Month 10
|
|
NoPriYr3Mon11
|
Double
|
Prior Year 3 Month 11
|
|
NoPriYr3Mon12
|
Double
|
Prior Year 3 Month 12
|
|
NoPriYr4Mon1
|
Double
|
Prior Year 4 Month 1
|
|
NoPriYr4Mon2
|
Double
|
Prior Year 4 Month 2
|
|
NoPriYr4Mon3
|
Double
|
Prior Year 4 Month 3
|
|
NoPriYr4Mon4
|
Double
|
Prior Year 4 Month 4
|
|
NoPriYr4Mon5
|
Double
|
Prior Year 4 Month 5
|
|
NoPriYr4Mon6
|
Double
|
Prior Year 4 Month 6
|
|
NoPriYr4Mon7
|
Double
|
Prior Year 4 Month 7
|
|
NoPriYr4Mon8
|
Double
|
Prior Year 4 Month 8
|
|
NoPriYr4Mon9
|
Double
|
Prior Year 4 Month 9
|
|
NoPriYr4Mon10
|
Double
|
Prior Year 4 Month 10
|
|
NoPriYr4Mon11
|
Double
|
Prior Year 4 Month 11
|
|
NoPriYr4Mon12
|
Double
|
Prior Year 4 Month 12
|
|
NoPriYr5Mon1
|
Double
|
Prior Year 5 Month 1
|
|
NoPriYr5Mon2
|
Double
|
Prior Year 5 Month 2
|
|
NoPriYr5Mon3
|
Double
|
Prior Year 5 Month 3
|
|
NoPriYr5Mon4
|
Double
|
Prior Year 5 Month 4
|
|
NoPriYr5Mon5
|
Double
|
Prior Year 5 Month 5
|
|
NoPriYr5Mon6
|
Double
|
Prior Year 5 Month 6
|
|
NoPriYr5Mon7
|
Double
|
Prior Year 5 Month 7
|
|
NoPriYr5Mon8
|
Double
|
Prior Year 5 Month 8
|
|
NoPriYr5Mon9
|
Double
|
Prior Year 5 Month 9
|
|
NoPriYr5Mon10
|
Double
|
Prior Year 5 Month 10
|
|
NoPriYr5Mon11
|
Double
|
Prior Year 5 Month 11
|
|
NoPriYr5Mon12
|
Double
|
Prior Year 5 Month 12
|
|
Other:
|
|
NoQuickRatioFlg
|
Yes/No-
|
Quick Ratio Account Flag
|
|
Notes:
|
|
Memo
|
Memo
|
Nominal Notes
|
Req = Field is required EOF = End of file, Stop when no more values
With data type Yes/No fields, if cell starts with 'y' or 'Y' is taken as TRUE
Where ^ denotes a In Cell Code Search, delete ^ at end of tag, to turn off code search
All Text fields are trimmed (spaces before) by default. Add ' to the end of the tag name to stop this.
Related Example Sheets
You can easily access the example sheets by pressing Help button, on the PostTrans Toolbar to access these related examples, or downloading them from the links below.
To use the template, with Sage 50, install PostTrans. Download
Trial with Sage demo company. Purchase a month subscription for Live Sage Company. Price
Watch the On-line Training course on how to use and customise the templates.
Transaction Data - Nominal Journals:
|
|
|
|
|
Nominal Journal - 2 Col
|
Sheet:'Nominal 2 Col'
Nominal Journal with Credits and Debits
Main page for Nominal Journal
Help article for Nominal Journal
|
|
|
Nominal Journal - 1 Col
|
Sheet:'Nominal 1 Col'
Nominal Journal with Single Credit/Debit Column
Help article for Nominal Journal - 1 Col
|
|
|
Nominal - Contra
|
Sheet:'Contra'
Total to contra nominal account
Main page for VAT Nominal Journal
|
|
|
Nominal - VAT
|
Sheet:'NOM with VAT'
Calculates VAT on a nominal and posts to VAT control account, plus total to contra account
Main page for VAT Nominal Journal
|
|
|
|
|
Manual VAT
|
Sheet:'Manual VAT'
Manual VAT on a order
Main page for VAT on Transactions
|
|
|
|
|
Nominal Codes
|
Sheet:'Nominal Codes'
Add/update Nominal Codes
|