•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Translating a Bank Statement to a Journal Entry
Our take
Translating bank statements into journal entries can feel daunting, especially when descriptions vary in format and character limits. You need a streamlined approach to extract critical information and match it to the correct accounts efficiently. For example, transforming "CDS0000589" to "check 589" for account 110, and simplifying "BANK STTLEMENT FEE 260319 58363" to "bnk fee 0319" for account 650 is essential. Discover how to leverage formulas that reference specific cells and tables to automate this process, making your accounting tasks faster and more accurate.
I need to translate a bank statement in a spreadsheet to a format suitable for uploading to my accounting system.
-The banks' descriptions are in different formats with important info in different spots.
-The journal entry descriptions can only hold about half the characters that the statement's descriptions can.
-I also need it to reference the relevant journal accounts.
"CDS0000589" needs to be "check 589" to account 110
"BANK STTLEMENT FEE 260319 58363" needs to be "bnk fee 0319" to account 650
Is there some kind of formula for "check cell x for a chunk of data that matches table y and apply the formula in that row"?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Formatting question for automating data entryIm going to try to articulate what I need and if it’s possible to do inside excel. At my job I have to record the amount of patrons using our facilities. and specify what particular services are being used. at the end of each quarter. (3 month period) I must tally up all the numbers and provide a total for each aspect of our facility as well as the total overall. For example. 1st quarter numbers. 100 patrons used theatre. 250 patrons used Game room 450 patrons used computer lab so on and so forth. Now that you have the gist in your head. Imagine a spreadsheet where the first form is just a data entry sheet. it’s essentially just a box that never changes. You input the numbers for the week, and that data gets automatically moved to a different cell that has the total amount. so that at the end of the quarter I can easily see my total without having to backtrack or tediously add. if anyone has some insight on how I can do this Please reach out. If you have any questions about my wording or understanding exactly what I mean please also reach out. If you read all this I appreciate your time. submitted by /u/Beneficial-Yard-9006 [link] [comments]
- How do people translate Excel sheets into other languages without breaking the formatting?I'm curious how people deal with this. When translating Excel sheets or Word documents, most translation tools work fine for plain text but struggle with structured files. Tables break, columns move, formatting gets messy. What workflow do people usually use? Do you: • copy everything manually into translators • hire translators for the files • use some specific tool • or just deal with the formatting mess after? Trying to understand how people solve this problem today. submitted by /u/Barmon_easy [link] [comments]
- tips for making input and output cells more obvious to the user?Just curious what tips/tricks y'all have for making a spreadsheet more appealing and easier to distinguish where data should be entered, vs where it's calculated. I'm drafting up a very large calculator where it's not going to be super obvious which columns are expected as inputs to a novice submitted by /u/223specialist [link] [comments]
- Change contents of cells based on a drop downSo, I'm trying to generate a spreadsheet to make the forms we use in the office to issue keys. When people are given an apartment, we have a sheet with all the key numbers on it, and then they sign for it. At the moment, we've got 22 different PDFs and Word documents, and it doesn't work. So, what I want to do is this. I've built the template in Excel and created a Reference sheet which will have all the numbers in it. What I am trying to do is set it up so that when you use the Apartment number from a dropdown, it populates with all the key numbers. This sounds like it should be easy to do, but my Excel brain is broken. Advice would be much appreciated! submitted by /u/Hot_Syrup_1774 [link] [comments]