[Solved] Need calculation/function for account name lookup

Discuss the spreadsheet application

[Solved] Need calculation/function for account name lookup

Postby rockna1964@gmail.com » Tue Jun 02, 2020 2:59 am

I have included a couple of screen shots from my spread sheet. What I need to do is have the account name popup in Col F rows 3 and up to 498
Needed is a calculation/function that says that if Col E Row 3 equals Col E Row 501 insert contents of Col F Row 501 to Col F Row 3. Of course when this calculation/function
is completed correctly I want to be able to copy it from Col F Row 3 to the rest of Col F as needed. I am sorry my rows didn't copy over.
TIA

        Col E         Col F
Acct # Account
3 1111 ie: General Acct
4 1121

501 1121 GENERAL ACCT
501 1124 PROJECT PATRIOT
Last edited by rockna1964@gmail.com on Wed Jun 03, 2020 7:37 pm, edited 6 times in total.
APACHE OPENOFFICE VERSION O4.1.5, OPERATING ON WINDOWS 10
rockna1964@gmail.com
 
Posts: 3
Joined: Tue Jun 02, 2020 2:24 am

Re: Need calculation/function

Postby robleyd » Tue Jun 02, 2020 3:39 am

In F3, try:
Code: Select all   Expand viewCollapse view
=IF(E3=E501;F501;"")

You can copy or drag that down column F as far as you need. This assumes there is a list of values from F501 down; if this isn't the case, perhaps you could provide a sample spreadsheet with values that show your situation and explain in a little more detail what you are needing to do.

See How to attach a document for details regarding attaching a document.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3374
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Need calculation/function

Postby robleyd » Tue Jun 02, 2020 3:43 am

Following the reformatting of your sample (and reading it more closely), it appears VLOOKUP may be the tool you need.

You show two rows labeled 501; I assume this is a typo.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3374
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Need calculation/function

Postby rockna1964@gmail.com » Wed Jun 03, 2020 8:42 am

Below I have copied the spread sheet. What I need is: Whatever account I type in Col E in the data entry section, I need the corresponding account name to automatically fill in Col F of the same row. All the accounts are listed from Row 501 through 581 Col E & F. Data entry is from Row 3 through 499. I tried the if function listed in this forum post and it worked as long as each entry was one row advanced in the accounts then quit working. I am just not familiar with the f functions. Never had to work with them. Only regular formulas.So I am open to any of the if functions recommended here. Thank you.

ROW COL A COL B COL C COL D COL E COL F COL G COL H COL I
Idx Folio month date Acct # Account Memo Debit Credit Comment
2 asset 0 2019/12/31 1111 Cash in hand Opening balance petty cash .00
3 asset 0 2019/12/31 1121 GENERAL ACCT 25.00
4 asset 0 2019/12/31 1124 PROJECT PATRIOT
5 asset 0 2019/12/31 1126 SCHOLARSHIP
6 asset 0 2019/12/31 1141 Uncashed Checks
7 asset 0 2019/12/31 1161 Prepaid Expenses
8 asset
9
10
11
12
13
14 First three Columns contain formula, do not delete (except to set opening balance month = 0)
15 Enter your transaction in Column D to Column I only
16
17 When entering opening balances, manually adjust the month to zero
18 Current Data Sheet Names Range set at 2,000 rows.
19 Use <Ctrl>+F3 to edit the Name Ranges
20 Sample account names are pasted to Cell D500 to provide Auto-fill functionality. Delete and enter your own accounts instead.
21 Select sample entries above and press <backspace> to delete
22
23 The next to last tip above was when the account number and account name were in the same column, I inserted a column
24 so I could have the Account names only in any report I printed for distribution to officers and members.
25 Row 3 through 499 are data entry rows. if more needed would use above tip to adjust the ledger accounts down.

-----------------Chart of Accounts------------------------------
Acct numbers are in col E and Account names are in col F
500 asset 1111 Cash in hand
501 asset 1121 GENERAL ACCT
502 asset 1124 PROJECT PATRIOT
503 asset 1126 SCHOLARSHIP
504 asset 1141 Uncashed Checks
505 asset 1161 Prepaid Expenses
506 asset 1171 SCHOLORSHIP FUNDS AT STATE
507 asset 1181 Internal Receivables
508 asset 1191 Accounts Receivables
509
510 asset 1211 FLAGS
511 asset 1214 STORAGE CONTAINERS
512 asset 1217 OTHER
513
514
515
516
517 payable 2111 Accounts Payable
518 payable 2114 PREPAID NATIONAL DUES
519 payable 2118 PREPAID PRESIDENT GENERAL'S PROJECT
520 payable 2120 PREPAID STATE REGENT PROJECT
521 payable 2122 PREPAID CONTINENTAL CONGRESS
522 payable 2124 PREPAID STATE CONVENTION
523 payable 2126 PREPAID SCHOLORSHIP DONATIONS
524 payable 2128 PREPAID VETERANS PROJECTS
525 payable 2130 PREPAID AMERICAN SPIRIT
526 payable 2140 PREPAID OTHER DONATIONS
527 payable 2150 Uncashed Checks
528 payable 2160 Other Payable
529
530
531 income 4001 NATIONAL PREPAID DUES' INC
532 income 4003 STATE PREPAID DUES INC
533 income 4005 CHAPTER DUES INC
534 income 4010 ASSOCIATE MEMBER DUES INC
535 income 4020 INTEREST INCOME
536 income 4030 MEMBER DONATIONS INC
537 income 4040 FUNDRAISING INCOME
538
539 income 4060 CONT. CONGRES PREPAID INC
540 income 4062 STATE CONV PREPAID INC
541 income 4064 NEW APP FEES & DONAT. INC
542 income 4066 REINST FEES & DONAT PRPD INC
543
544 income 4070 OTHER DONATIONS
545 income 4075 OTHER INCOME



549 5000 NATIONAL CONTRIBUTIONS
550 expense 5001 VARIOUS
551 expense 5003 PRESIDENT GENERAL PROJECT
552 expense 5005 ADV CO-OP AMERICAN SPIRIT
553 TOTAL NATIONAL EXPENSE
554
555 expense 5010 STATE CONTRIBUTIONS
556 expense 5011 VARIOUS EXPENSES
557 expense 5013 STATE REGENT PROJECT
558 TOTAL STATE CONTRIBUTIONS
559
560 5020-5040 CHAPTER EXPENSES
561 expense 5021 CHECKS/DEPOSIT SLIPS
562 expense 5023 OFFICE SUPPLIES
563 expense 5025 DUES POSTAGE
564 expense 5027 MISC/SPEAKES/CERTIFICATES
565 expense 5029 AWARDS CERTIFICATES/FRAMES
566 expense 5031 DISTRICT DUES BASKET
567 expense 5033 GOOD CITIZEN METALS/AWARDS
568 expense 5035 HISTORIAN SCRAPBOOK
569 expense 5037 GRAVE INSIGNIAS MEMORIALS
570 expense 5039 YEARBOOK PUBLICATIONS
571 expense 5041 WEBSITE EXPENSE
572 expense 5043 VETERAN ASSISTANCE EXPENSE
573 expense 5045 HONORARY PIN
574 expense 5047 OTHER CHAPTER EXPENSES
575 TOTAL CHAPTER EXPENSES
576
577 NON-BUDGET EXPENSES
578 5500 CHAPTER EXPENSES
579 expense 5501 MISCELLANEOUS EXP
580 expense 5503 REGENT BUDGET
581 expense 5505 OTHER ADVERTISING FEES
rockna1964@gmail.com
 
Posts: 3
Joined: Tue Jun 02, 2020 2:24 am

Re: Need calculation/function

Postby Villeroy » Wed Jun 03, 2020 9:56 am

[Tutorial] VLOOKUP questions and answers
[Tutorial] Ten concepts that every Calc user should know, i.e. point 8 on absolute/relative references
[Tutorial] Absolute, relative and mixed references

and any book on Excel or similar spreadsheets since 1993
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28371
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [SOLVED]Need calculation/function for account name look

Postby rockna1964@gmail.com » Wed Jun 03, 2020 7:40 pm

Thank you for your quick responses. I was able to figure out how to do my calculation by studying [Tutorial] VLOOKUP questions and answers.
APACHE OPENOFFICE VERSION O4.1.5, OPERATING ON WINDOWS 10
rockna1964@gmail.com
 
Posts: 3
Joined: Tue Jun 02, 2020 2:24 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 34 guests