[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

I have a possible reference issue!

Posted on 2011-10-11
Medium Priority
196 Views
I have attached my spreadsheet that I am working on.  In the Monthly report tab, I need the check number that goes with each payment to populate from the loan data tab to cell E 17 in a similar fashion to how it is currently done in the J column.

if for some reason any tabs are locked, the password is loan1
Finished-Amortization-Simple-Int.xls
0
Question by:binaryman101

LVL 26

Accepted Solution

redmondb earned 2000 total points
ID: 36950436
Hi, binaryman101.

Do you need more than a simple lookup?
=VLOOKUP(A17,'Loan Data'!\$B\$18:\$L\$22487,11,0)

Regards,
Brian
0

Author Closing Comment

ID: 36950465
I was trying to complicate it too much...thanks a ton Brian!
0

LVL 26

Expert Comment

ID: 36950494
Hey, I thought I was the expert on overcomplication!

Thanks, binaryman101!
0

LVL 1

Expert Comment

ID: 36950524
You could define the range in Loan Data,  starting with the Date column,  and use the vlookup function to return the check number.

I defined a range on the Loan Data tab that I named Check_Number to cover B18:L22487 cells .

Then in the Monthly Report tab, in cell E17 I used this formula to return the check number.
=VLOOKUP(A17,Check_Number,11,FALSE)

Good Luck!
Lorrie
0

LVL 19

Expert Comment

ID: 36950533
or perhaps

=VLOOKUP(A17,\$B\$28:\$J\$59,9,0)

:)
0

LVL 26

Expert Comment

ID: 36950970
regmigrant, worser and worser! sigh

binaryman101, remigran't suggestion gives the same answer as mine, but a lot more efficiently!
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Suggested Courses
Course of the Month19 days, 15 hours left to enroll