How to get last entered value in a ROW?

  • Thread starter Thread starter Excelwiz wanabee
  • Start date Start date
E

Excelwiz wanabee

Hi,

I'm wondering if ya'll excel "wizards" can help me out. I want to be able to
get the "last entered" value for a particular row and subtract from it a
fixed field (i.e. L15-F15 where L15 is the last entered value (or G15, H15,
etc.; increments monthly) and F15 is a field that does not change in the
equation.)

Thanks,

Bill
 
Try this to get the last value of row 15

ActiveSheet.Cells(15, Columns.Count).End(xlToLeft).Value
 
Bill,

Try something like

Dim LastRng As Range
Set LastRng = Range("IV15").End(xlToLeft)
MsgBox LastRng.Address


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
If you want a worksheet function, this gets the L15

=OFFSET(A15,,COUNT(15:15)-1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Bob,

When I paste that into cell D15 I get a blue dot followed by a zero - and is
A15 supposed to be F15? I get same result.

Bill
 
Chip - how can I get that value into, say, cell D15? And where do I paste
that programming? Please
excuse my ignorance :-)

Bill
 
Hi Excelwiz

Sub test()
ActiveSheet.Range("c1").Value = ActiveSheet.Cells(15, Columns.Count).End(xlToLeft).Value
End Sub

Alt-F11
Insert>Module from the menubar
paste the sub in there
Alt-Q to go back to Excel

If you do Alt-F8 you get a list of your macro's
Select "test" and press Run
 
Tom, that appears to work! And I looked at the MATCH function but am unsure
as to the purpose of values you entered for that function; can you explain
why the 9.9999... ?

Thanks,

Bill
 
It is the highest numerical value that can be stored in a cell as a number -
using the rules of Match and ordered data (3rd argument is a 1), it gets the
job done that you requested.
 
Hi,

I'm wondering if ya'll excel "wizards" can help me out. I want to be able to
get the "last entered" value for a particular row and subtract from it a
fixed field (i.e. L15-F15 where L15 is the last entered value (or G15, H15,
etc.; increments monthly) and F15 is a field that does not change in the
equation.)

Thanks,

Bill
Click to expand...

If there are no "blanks" then Bob Philips worksheet formula will work fine. If
there may be blanks in the row, then the *array-entered* formula:

=F15-OFFSET(A15,0,-1+MAX(ISNUMBER(15:15)*COLUMN(1:256)))

should subtract from F15 the last numeric entry in Row 15.

To array-enter a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.


--ron
 
Ron,

Good point!

Bob

Ron Rosenfeld said:
If there are no "blanks" then Bob Philips worksheet formula will work fine. If
there may be blanks in the row, then the *array-entered* formula:

=F15-OFFSET(A15,0,-1+MAX(ISNUMBER(15:15)*COLUMN(1:256)))

should subtract from F15 the last numeric entry in Row 15.

To array-enter a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.


--ron
Click to expand...
 
Another way is to redesign your spreadsheet. I take it you
have a spreadsheet which contains monthly data in columns
and you want to know the result of the last entered month.

As you are entering the new month's data to the right of all
other data you have to "find" which column you have used
for that month. If you enter this months data into column B
(or the first column after the row titles), then you can use
column B for your formula cos that is always where the last
month's data will be. All you need to do it select one cell
on the current last month's data and, using menus, select
Insert --> Row.

To make sure your formula does not refer to the column to the
right of the one you just entered you need to make it an indirect
formula like this.

= INDIRECT("B15") + F15

If you actually want this formula in all cells in the column then
a more general one would be

= INDIRECT("B:B") + F:F

That would add the data in the current row for the current month
(column B) to the data in the current row in column F.

The other advantage in doing it this way is that you only need to print
one page to get the latest data and it will always be the first page. This
assumes that all the rows for this month can fit on one page. It saves
working out how many pages you need to skip as your get more and
more months added and thus a wider and wider spreadsheet.

Steve.
 
Back
Top