Thread: transfer data from journal to Ledger

Hi,

It all depends on your particular layout. We can't guess what it is.

Then of course do you want to post summary journal totals to your ledger or do you want a one to one match?

And you don't indicate what you mean by 'transfer data' Is this transfer entirely within a single worksheet or workbook or between workbooks. Or are you wanting to update some back office accounting system by uploading a file?

Upload an example workbook and I'm sure someone will be able to help. Obviously the trivial way to link cells together is just

=Sheet1!A1

Regards

Hi,

We still need to see what layouts you use. There is no common accounting standard which determines these things. Neither have you responded to the specific questions I put.

Unless we see this information we can't help. If you need further help you'll need to give us more.

Rgds

Regards

Hi,

We need to see where you enter your journal data numbers, and where you expect to see these entered values end up in your ledger. i,e which cells in a spreadsheet are being used. That's what I meant by 'layout'.

I interpreted your original post as relating to an Excel workbook. Are you entering numbers in a worksheet, and is your end result a ledger account which also exists in a worksheet.

If you're looking to enter numbers in Excel and end up with those same numbers in your (non excel) ledger, then that all depends what software you use for your ledger.

Regards

Yes of course. But we need to see what your journal sheet looks like, and what your ledger accounts sheet looks like. i.e. the layout as requested in my first post.

Without knowing these no one will be able to help you. Please upload the workbook you're using with example data in the journals and ledger accounts sections.

Rgds

Hi,

You're right, I wouldn't understand Arabic.

I'm British. I don't know what the 'usual' shape of an American journal might be. I'd be surprised if there was such a thing. Any company I've worked for has always had its own accounting methods and stationery. For instance you don't say whether the journal entries are posted on a one to one basis with the ledger or whether you summarise journal entries for the same account and post a single total. This is all basic information needed before anyone can help you.

Even if there were such a thing as a standard journal, we'd still need to know where they are located, i.e. which rows and columns.

Regards

Hi,

Is this along the lines you want?

I'm not sure whether you want to copy each row from the journal. I suspect not since there are more rows on the journal than on the sub ledgers. Hence I've assumed row 32 is the important row.

Rgds

Hi,

Can you go back to basics and describe what you are trying to achieve?

It seems to me that your requirement is to be able to enter journals as they occur showing for each journal in 5 columns:

1. Date
2. Journal Ref Number
3. Narrative
4. Account SubLedger involved.
5. Amount (Positive or Negative)

I believe you then want to be able to analyse those journals by Sub Ledger.

Is this the overall requirement?

If so I can see two methods of achieving this aim. The first and simplest method is just to have a filter on the journals table. This would allow you to select a particular sub ledger and see the total. You could if necessary also filter by date to see totals by period.

The other way would be to have a macro copy from the journal and create a list for each sub ledger. Although I don't see that this is necessarily better than the simple filtering option.

I'm attaching an example of what I mean. The new sheet Journal2 contains 3 journals. Note how this is already filtered on journal refs 1 & 3 and that the total at the top shows you that the journal is out of balance. If you deselect journal ref 1 leaving just journal 3 you'll see that this is in balance.

I have also made the journal the subject of a Pivot Table analysis which you see in column H. This automatically summarised every journal by date and tells you if any are out of balance.

If you are not familiar with Pivot Tables, play around with it. For instance unfilter the journals so that the pivot table rows won't be hidden, Now drag the 'Sub Ledger' field name down into the PT underneath the 'No' field and between the 'No' and 'Date' columns. See how instantaneously you see a complete analysis of your journals.

Hope this gives you some ideas.

Regards