We're creating a tracking system for our vendors. I want to input pricing by month for each item we buy from each vendor. In another workbook, I want to display only the most recent price we've paid.
Is there a way to tell Excel to reference the rightmost cell of an array that contains data?
If you were, for example, entering values in cells A1:Z1 and wanted to see the latest entry reading from left to right, this formula work:
=LOOKUP(2, 1 / (A1:Z1<>""), A1:Z1)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Great! That seems to work. can you walk me through the terms in the formula?
Highlight the cell and then activate the Evaluate Formula icon on the Formula Auditing toolbar. With that tool, you can watch the formula unfold one step at a time from the inside out.
In a nutshell, that little "snippet" is used to find the last cell in a range with a value <> "" and then return that value.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks