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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Excelwiz wanabee" wrote in message .. .
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

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

"Excelwiz wanabee" wrote in
message .. .
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

=INDEX(G15:AB15,1,MATCH(9.99999999999999E+307,G15: AB15,1))-F15

--
Regards,
Tom Ogilvy



"Excelwiz wanabee" wrote in message
.. .
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

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)

"Excelwiz wanabee" wrote in message
.. .
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

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

"Bob Phillips" wrote in message
...
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)

"Excelwiz wanabee" wrote in message
.. .
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

Ron - please excuse my ignorance but where do I paste that into?

Bill

"Ron de Bruin" wrote in message
...
Try this to get the last value of row 15

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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Excelwiz wanabee" wrote in message
.. .
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

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

Bill

"Chip Pearson" wrote in message
...
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

"Excelwiz wanabee" wrote in
message .. .
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

Hi Excelwiz

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

Alt-F11
InsertModule 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


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Excelwiz wanabee" wrote in message .. .
Ron - please excuse my ignorance but where do I paste that into?

Bill

"Ron de Bruin" wrote in message
...
Try this to get the last value of row 15

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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Excelwiz wanabee" wrote in message
.. .
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

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

"Tom Ogilvy" wrote in message
...
=INDEX(G15:AB15,1,MATCH(9.99999999999999E+307,G15: AB15,1))-F15

--
Regards,
Tom Ogilvy



"Excelwiz wanabee" wrote in message
.. .
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

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.

--
Regards,
Tom Ogilvy

"Excelwiz wanabee" wrote in message
.. .
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

"Tom Ogilvy" wrote in message
...
=INDEX(G15:AB15,1,MATCH(9.99999999999999E+307,G15: AB15,1))-F15

--
Regards,
Tom Ogilvy



"Excelwiz wanabee" wrote in message
.. .
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

On Tue, 02 Dec 2003 14:56:11 GMT, "Excelwiz wanabee"
wrote:

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



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" wrote in message
...
On Tue, 02 Dec 2003 14:56:11 GMT, "Excelwiz wanabee"
wrote:

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



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

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.



"Bob Phillips" wrote in message ...
Ron,

Good point!

Bob

"Ron Rosenfeld" wrote in message
...
On Tue, 02 Dec 2003 14:56:11 GMT, "Excelwiz wanabee"
wrote:

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



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