Data Entry Form

**forskare** · 07-22-2019, 04:24 PM

I found a small excel sheet online that calculates the daily interest rate and added cells to show the daily interest * 30 days, a late fee and the new total due. Once I got it working, I copied it so I now have 12 of them. Next, I copied the formula in the New Total Due cell and pasted it in the Balance due cell of the next Unit/s, so on and so forth until all were programed. Now, I have 12 micro monthly calculators showing 12 months worth of delinquent charges. All I need to do is enter the beginning balance in the first month for each member which is past due. Wham, bam, thank you very much Excel. (See Attached).

Now for the member info and to link it with the correct values in the monthly calculators. I suppose I could use a separate sheet for each delinquent member since at this point there are on 25 or so. However, in the long run, that's not so practical. A better way might be to have 2 sheets, Monthly Calculators and Delinquent Members and kink them together. After watching several videos on linking sheets, it's will take a fair amount of time to come up with the best way to accomplish this task.

Ideally, I would be handy to show a form for entering member info and below that, the mini calculators. then as each member was loaded into the form along with the calculators, I could print it as an invoice. I see there are basic data entry forms (based on inserting a table) and some pretty cool looking forms (I don't know how to create those). For printing as an invoice, a basic data entry form is not the best choice because of the buttons.

If this were a database, it would be a 2 table relational setup. Im essence, I am looking to use Excel as a relational spreadsheet. What/How is the best way to get this done?

Thank you for any and all suggestions.

**JeteMc** · 07-25-2019, 07:53 PM

Hello forskare and Welcome to Excel Forum.
From the description it sounds as if you would just need a list of delinquent members and their beginning balances. Lets say that the members list are in cells A2:A26 and the balance amounts are B2:B26. Set aside a cell on the Monthly calculators sheet to pull up the members name using data validation (i.e. cell A1). You could then put a formula in the Principle amount cell of month 1 that reads: =INDEX(Sheet2!B2:B26,MATCH(A1,Sheet2!A2:A26,0))
To see this method applied, please upload a sample of workbook from which you from which the screenshot in post #1 is taken by clicking on the GO ADVANCED button below the Quick Reply window and then scrolling down to Manage Attachments to open the upload window.
Let us know if you have any questions.

**forskare** · 07-25-2019, 10:47 PM

Should be attached

Thank you!

**JeteMc** · 07-25-2019, 11:10 PM

The lists are on sheet2, and the formula is in cell C9 on sheet 1.
Test by using the drop down in cell B2 on sheet 1.
Let us know if you have any questions.

**forskare** · 07-25-2019, 11:15 PM

Fabulous, Outstandingly fabulous. Exactly what I had in mind. Now just to populate sheet 2.

Thank you VERY Much

**JeteMc** · 07-25-2019, 11:26 PM

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

**forskare** · 07-26-2019, 10:32 PM

Hello,

In order to understand this better so that I can make changes, please explain this:

=INDEX(Sheet2!B2:B26,MATCH(A1,Sheet2!A2:A26,0))

So far, I understand an index is created and cell A2 is getting its data from Sheet B. However, I do not need Column B, Only column A. I think the formula should be changed to:

=INDEX(A1,Sheet2!A2:A200,0)

I changed A26 to A200 to allow for more names.

Is the changed formula correct?

My goal is to place a data entry cells above the calculators.

Thank you

**JeteMc** · 07-27-2019, 12:18 AM

The formula to which you are referring looks up (or indexes) the amount of the balance in column B of sheet2 based on matching the name placed in the cell containing the data validation (B2) with the list of names in column A of sheet2.
So if you want to place the lookup name in cell A1, you would then need to assign that cell the data validation.
The formula for balance could then read: =INDEX(Sheet2!B2:B200,MATCH(A1,Sheet2!A2:A200,0))
However you might want to consider changing the range on sheet2 into a table and using structured reference formulas as follows.
For the data validation for cell A1: =INDIRECT("table1[name]")
For cell C9 is: =INDEX(Table1[Bal. Due],MATCH(A1,Table1[Name],0))
I added a formula to C7: =INDEX(Table1[Due Date],MATCH(A1,Table1[Name],0)) so that it will change the due date based on the name selected.
Test by selecting cell C7 on sheet 2 and pressing the tab key, then enter a name balance amount and date. When you select cell A1 on sheet 1, the name should be added and if you select the new name the date and amount should change accordingly.
This will avoid having lots of empty rows in the drop down box if you only have 25 or soo names to start with.
Let us know if you have any questions.

Data Entry Form

LinkBack

Thread Tools

Rate This Thread

Display

Data Entry Form

Re: Data Entry Form

Re: Data Entry Form

Re: Data Entry Form

Re: Data Entry Form

Re: Data Entry Form

Re: Data Entry Form

Re: Data Entry Form

Thread Information

Users Browsing this Thread

Similar Threads

Form Data Entry

Is there a way to make a Data Entry Form by using the worksheet as the form?

Data Entry Form

[SOLVED] I have a split database wih a back-end and an entry form. Entry form is not allowing edits

[SOLVED] Re: Data entry form

[SOLVED] Data Entry Form

[SOLVED] Data Entry form

Data Entry Form (similar to default Excel Data>Form)

Bookmarks

Bookmarks

Posting Permissions