Solved

# I have a possible reference issue!

Posted on 2011-10-11
188 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

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

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

LVL 26

Expert Comment

Hey, I thought I was the expert on overcomplication!

Thanks, binaryman101!
0

LVL 1

Expert Comment

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

or perhaps

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

:)
0

LVL 26

Expert Comment

regmigrant, worser and worser! sigh

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

## Featured Post

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.