Need a formula to give me the last value in a column.
 

Within one workbook, I'm trying to have one tab read off another using a
formula. I need the "Summary" tab to display the last cell with data (not
null) in a particular column. For example, column A has data populated from
A2:A50, I want to show A50. Any suggestions?

Need a formula to give me the last value in a column.
 

Try one of these:

The VALUE of the last NUMERIC value in Col_A:
=LOOKUP(10^99,A:A)

The VALUE of the last TEXT cell in Col_A
=LOOKUP(REPT("z",255),A:A)

The VALUE of the last NON-BLANK, NON-ZERO cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<0),A1:A100)

The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"SMH" wrote in message
...
Within one workbook, I'm trying to have one tab read off another using a
formula. I need the "Summary" tab to display the last cell with data (not
null) in a particular column. For example, column A has data populated
from
A2:A50, I want to show A50. Any suggestions?

Need a formula to give me the last value in a column.
 

=LOOKUP(99^99,Sheet1!A:A)

Where Sheet1 is the name of the sheet containing data in column A - will
return the last value in the column.

"SMH" wrote:

Within one workbook, I'm trying to have one tab read off another using a
formula. I need the "Summary" tab to display the last cell with data (not
null) in a particular column. For example, column A has data populated from
A2:A50, I want to show A50. Any suggestions?

Need a formula to give me the last value in a column.
 

OH WOW!! Thank you so much! I really appreciate it! :)

"BoniM" wrote:

=LOOKUP(99^99,Sheet1!A:A)

Where Sheet1 is the name of the sheet containing data in column A - will
return the last value in the column.

"SMH" wrote:

Within one workbook, I'm trying to have one tab read off another using a
formula. I need the "Summary" tab to display the last cell with data (not
null) in a particular column. For example, column A has data populated from
A2:A50, I want to show A50. Any suggestions?

Need a formula to give me the last value in a column.
 

Ok so I have one more for you. I need to have a calculation formula within
that formula you just provided. For example, I need to take B50/A50, but
still need it to look at the last cell with data. Basically I need to ensure
every time I update the "Data" tab, the "Summary" tab is always updated with
the last entry from the "Data" tab.

Thoughts? Thanks again.

"SMH" wrote:

OH WOW!! Thank you so much! I really appreciate it! :)

"BoniM" wrote:

=LOOKUP(99^99,Sheet1!A:A)

Where Sheet1 is the name of the sheet containing data in column A - will
return the last value in the column.

"SMH" wrote:

Within one workbook, I'm trying to have one tab read off another using a
formula. I need the "Summary" tab to display the last cell with data (not
null) in a particular column. For example, column A has data populated from
A2:A50, I want to show A50. Any suggestions?

Need a formula to give me the last value in a column.
 

You want to divide the last value in column B by the last value in column A?
If I understand correctly:
=LOOKUP(99^99,Sheet1!B:B)/LOOKUP(99^99,Sheet1!A:A)

"SMH" wrote:

Ok so I have one more for you. I need to have a calculation formula within
that formula you just provided. For example, I need to take B50/A50, but
still need it to look at the last cell with data. Basically I need to ensure
every time I update the "Data" tab, the "Summary" tab is always updated with
the last entry from the "Data" tab.

Thoughts? Thanks again.

"SMH" wrote:

OH WOW!! Thank you so much! I really appreciate it! :)

"BoniM" wrote:

=LOOKUP(99^99,Sheet1!A:A)

Where Sheet1 is the name of the sheet containing data in column A - will
return the last value in the column.

"SMH" wrote:

Within one workbook, I'm trying to have one tab read off another using a
formula. I need the "Summary" tab to display the last cell with data (not
null) in a particular column. For example, column A has data populated from
A2:A50, I want to show A50. Any suggestions?

Need a formula to give me the last value in a column.
 

You got it! Again, thank you so very much!!

"BoniM" wrote:

You want to divide the last value in column B by the last value in column A?
If I understand correctly:
=LOOKUP(99^99,Sheet1!B:B)/LOOKUP(99^99,Sheet1!A:A)

"SMH" wrote:

Ok so I have one more for you. I need to have a calculation formula within
that formula you just provided. For example, I need to take B50/A50, but
still need it to look at the last cell with data. Basically I need to ensure
every time I update the "Data" tab, the "Summary" tab is always updated with
the last entry from the "Data" tab.

Thoughts? Thanks again.

"SMH" wrote:

OH WOW!! Thank you so much! I really appreciate it! :)

"BoniM" wrote:

=LOOKUP(99^99,Sheet1!A:A)

Where Sheet1 is the name of the sheet containing data in column A - will
return the last value in the column.

"SMH" wrote:

Within one workbook, I'm trying to have one tab read off another using a
formula. I need the "Summary" tab to display the last cell with data (not
null) in a particular column. For example, column A has data populated from
A2:A50, I want to show A50. Any suggestions?

Need a formula to give me the last value in a column.
 

So I'm trying to nest one more function, but it isn't working.
Looking for the last value of A plus the last value of B divided by the last
value of C.

Here is what I have, but when trying to add additional parentheses it gives
me the results of A plus B.

=LOOKUP(99^99,Data!A:A)+LOOKUP(99^99,Data!B:B)/LOOKUP(99^99,Data!C:C)


"BoniM" wrote:

You want to divide the last value in column B by the last value in column A?
If I understand correctly:
=LOOKUP(99^99,Sheet1!B:B)/LOOKUP(99^99,Sheet1!A:A)

"SMH" wrote:

Ok so I have one more for you. I need to have a calculation formula within
that formula you just provided. For example, I need to take B50/A50, but
still need it to look at the last cell with data. Basically I need to ensure
every time I update the "Data" tab, the "Summary" tab is always updated with
the last entry from the "Data" tab.

Thoughts? Thanks again.

"SMH" wrote:

OH WOW!! Thank you so much! I really appreciate it! :)

"BoniM" wrote:

=LOOKUP(99^99,Sheet1!A:A)

Where Sheet1 is the name of the sheet containing data in column A - will
return the last value in the column.

"SMH" wrote:

Within one workbook, I'm trying to have one tab read off another using a
formula. I need the "Summary" tab to display the last cell with data (not
null) in a particular column. For example, column A has data populated from
A2:A50, I want to show A50. Any suggestions?

Need a formula to give me the last value in a column.
 

Nevermind! I got it!

=(LOOKUP(99^99,Data!M:M)+LOOKUP(99^99,Data!P:P))/LOOKUP(99^99,Data!F:F)

"SMH" wrote:

So I'm trying to nest one more function, but it isn't working.
Looking for the last value of A plus the last value of B divided by the last
value of C.

Here is what I have, but when trying to add additional parentheses it gives
me the results of A plus B.

=LOOKUP(99^99,Data!A:A)+LOOKUP(99^99,Data!B:B)/LOOKUP(99^99,Data!C:C)


"BoniM" wrote:

You want to divide the last value in column B by the last value in column A?
If I understand correctly:
=LOOKUP(99^99,Sheet1!B:B)/LOOKUP(99^99,Sheet1!A:A)

"SMH" wrote:

Ok so I have one more for you. I need to have a calculation formula within
that formula you just provided. For example, I need to take B50/A50, but
still need it to look at the last cell with data. Basically I need to ensure
every time I update the "Data" tab, the "Summary" tab is always updated with
the last entry from the "Data" tab.

Thoughts? Thanks again.

"SMH" wrote:

OH WOW!! Thank you so much! I really appreciate it! :)

"BoniM" wrote:

=LOOKUP(99^99,Sheet1!A:A)

Where Sheet1 is the name of the sheet containing data in column A - will
return the last value in the column.

"SMH" wrote:

Within one workbook, I'm trying to have one tab read off another using a
formula. I need the "Summary" tab to display the last cell with data (not
null) in a particular column. For example, column A has data populated from
A2:A50, I want to show A50. Any suggestions?

Need a formula to give me the last value in a column.
 

i know this was a few months back, but i have tried using all of these
formulas and cannot get one to work with a text box.
i have a text box in a worksheet. if i simply type: =LOG!A1 then it displays
the text in A1 of my Log sheet.
however, i need it to display the text in the last cell with text in that
sheet.
i have typed in all of your options and keep getting a "The text you entered
is not a valid reference or defined name."

Thoughts?
--
jana


"Ron Coderre" wrote:

Try one of these:

The VALUE of the last NUMERIC value in Col_A:
=LOOKUP(10^99,A:A)

The VALUE of the last TEXT cell in Col_A
=LOOKUP(REPT("z",255),A:A)

The VALUE of the last NON-BLANK, NON-ZERO cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<0),A1:A100)

The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"SMH" wrote in message
...
Within one workbook, I'm trying to have one tab read off another using a
formula. I need the "Summary" tab to display the last cell with data (not
null) in a particular column. For example, column A has data populated
from
A2:A50, I want to show A50. Any suggestions?

Need a formula to give me the last value in a column.
 

Where do we look for the the last text entry?

Here's what you need to do:

Get the address of the cell in question.

Wrap that inside an INDIRECT function.

Give the INDIRECT function a defined name.

Then, as the source for the textbox use =defined_name

--
Biff
Microsoft Excel MVP


"janabanana" wrote in message
...
i know this was a few months back, but i have tried using all of these
formulas and cannot get one to work with a text box.
i have a text box in a worksheet. if i simply type: =LOG!A1 then it
displays
the text in A1 of my Log sheet.
however, i need it to display the text in the last cell with text in that
sheet.
i have typed in all of your options and keep getting a "The text you
entered
is not a valid reference or defined name."

Thoughts?
--
jana


"Ron Coderre" wrote:

Try one of these:

The VALUE of the last NUMERIC value in Col_A:
=LOOKUP(10^99,A:A)

The VALUE of the last TEXT cell in Col_A
=LOOKUP(REPT("z",255),A:A)

The VALUE of the last NON-BLANK, NON-ZERO cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<0),A1:A100)

The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"SMH" wrote in message
...
Within one workbook, I'm trying to have one tab read off another using
a
formula. I need the "Summary" tab to display the last cell with data
(not
null) in a particular column. For example, column A has data populated
from
A2:A50, I want to show A50. Any suggestions?

Need a formula to give me the last value in a column.
 

The formula that has worked so far (when typing it in a cell) is:
=LOOKUP(2,1/(LOG!K1:K1000<0),LOG!K1:K1000).

Now I have a text box that I would like to type this formula in (using the
formula bar after I select the correct box) and it doesn't seem to want to go.
--
jana


"T. Valko" wrote:

Where do we look for the the last text entry?

Here's what you need to do:

Get the address of the cell in question.

Wrap that inside an INDIRECT function.

Give the INDIRECT function a defined name.

Then, as the source for the textbox use =defined_name

--
Biff
Microsoft Excel MVP


"janabanana" wrote in message
...
i know this was a few months back, but i have tried using all of these
formulas and cannot get one to work with a text box.
i have a text box in a worksheet. if i simply type: =LOG!A1 then it
displays
the text in A1 of my Log sheet.
however, i need it to display the text in the last cell with text in that
sheet.
i have typed in all of your options and keep getting a "The text you
entered
is not a valid reference or defined name."

Thoughts?
--
jana


"Ron Coderre" wrote:

Try one of these:

The VALUE of the last NUMERIC value in Col_A:
=LOOKUP(10^99,A:A)

The VALUE of the last TEXT cell in Col_A
=LOOKUP(REPT("z",255),A:A)

The VALUE of the last NON-BLANK, NON-ZERO cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<0),A1:A100)

The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"SMH" wrote in message
...
Within one workbook, I'm trying to have one tab read off another using
a
formula. I need the "Summary" tab to display the last cell with data
(not
null) in a particular column. For example, column A has data populated
from
A2:A50, I want to show A50. Any suggestions?

Need a formula to give me the last value in a column.
 

Try this:

Goto the menu InsertNameDefine
Name: LastCell
Refers to:

=INDIRECT("Log!K"&LOOKUP(2,1/(Log!$K$1:$K$1000<0),ROW(Log!$K$1:$K$1000)))

OK

Click within the textbox to make it active
Enter this formula in the formula bar: =LastCell
Hit ENTER


--
Biff
Microsoft Excel MVP


"janabanana" wrote in message
...
The formula that has worked so far (when typing it in a cell) is:
=LOOKUP(2,1/(LOG!K1:K1000<0),LOG!K1:K1000).

Now I have a text box that I would like to type this formula in (using the
formula bar after I select the correct box) and it doesn't seem to want to
go.
--
jana


"T. Valko" wrote:

Where do we look for the the last text entry?

Here's what you need to do:

Get the address of the cell in question.

Wrap that inside an INDIRECT function.

Give the INDIRECT function a defined name.

Then, as the source for the textbox use =defined_name

--
Biff
Microsoft Excel MVP


"janabanana" wrote in message
...
i know this was a few months back, but i have tried using all of these
formulas and cannot get one to work with a text box.
i have a text box in a worksheet. if i simply type: =LOG!A1 then it
displays
the text in A1 of my Log sheet.
however, i need it to display the text in the last cell with text in
that
sheet.
i have typed in all of your options and keep getting a "The text you
entered
is not a valid reference or defined name."

Thoughts?
--
jana


"Ron Coderre" wrote:

Try one of these:

The VALUE of the last NUMERIC value in Col_A:
=LOOKUP(10^99,A:A)

The VALUE of the last TEXT cell in Col_A
=LOOKUP(REPT("z",255),A:A)

The VALUE of the last NON-BLANK, NON-ZERO cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<0),A1:A100)

The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"SMH" wrote in message
...
Within one workbook, I'm trying to have one tab read off another
using
a
formula. I need the "Summary" tab to display the last cell with data
(not
null) in a particular column. For example, column A has data
populated
from
A2:A50, I want to show A50. Any suggestions?

Need a formula to give me the last value in a column.
 

Worked wonderfully.
Thank you so much.
--
jana


"T. Valko" wrote:

Try this:

Goto the menu InsertNameDefine
Name: LastCell
Refers to:

=INDIRECT("Log!K"&LOOKUP(2,1/(Log!$K$1:$K$1000<0),ROW(Log!$K$1:$K$1000)))

OK

Click within the textbox to make it active
Enter this formula in the formula bar: =LastCell
Hit ENTER


--
Biff
Microsoft Excel MVP


"janabanana" wrote in message
...
The formula that has worked so far (when typing it in a cell) is:
=LOOKUP(2,1/(LOG!K1:K1000<0),LOG!K1:K1000).

Now I have a text box that I would like to type this formula in (using the
formula bar after I select the correct box) and it doesn't seem to want to
go.
--
jana


"T. Valko" wrote:

Where do we look for the the last text entry?

Here's what you need to do:

Get the address of the cell in question.

Wrap that inside an INDIRECT function.

Give the INDIRECT function a defined name.

Then, as the source for the textbox use =defined_name

--
Biff
Microsoft Excel MVP


"janabanana" wrote in message
...
i know this was a few months back, but i have tried using all of these
formulas and cannot get one to work with a text box.
i have a text box in a worksheet. if i simply type: =LOG!A1 then it
displays
the text in A1 of my Log sheet.
however, i need it to display the text in the last cell with text in
that
sheet.
i have typed in all of your options and keep getting a "The text you
entered
is not a valid reference or defined name."

Thoughts?
--
jana


"Ron Coderre" wrote:

Try one of these:

The VALUE of the last NUMERIC value in Col_A:
=LOOKUP(10^99,A:A)

The VALUE of the last TEXT cell in Col_A
=LOOKUP(REPT("z",255),A:A)

The VALUE of the last NON-BLANK, NON-ZERO cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<0),A1:A100)

The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"SMH" wrote in message
...
Within one workbook, I'm trying to have one tab read off another
using
a
formula. I need the "Summary" tab to display the last cell with data
(not
null) in a particular column. For example, column A has data
populated
from
A2:A50, I want to show A50. Any suggestions?

Need a formula to give me the last value in a column.
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"janabanana" wrote in message
...
Worked wonderfully.
Thank you so much.
--
jana


"T. Valko" wrote:

Try this:

Goto the menu InsertNameDefine
Name: LastCell
Refers to:

=INDIRECT("Log!K"&LOOKUP(2,1/(Log!$K$1:$K$1000<0),ROW(Log!$K$1:$K$1000)))

OK

Click within the textbox to make it active
Enter this formula in the formula bar: =LastCell
Hit ENTER


--
Biff
Microsoft Excel MVP


"janabanana" wrote in message
...
The formula that has worked so far (when typing it in a cell) is:
=LOOKUP(2,1/(LOG!K1:K1000<0),LOG!K1:K1000).

Now I have a text box that I would like to type this formula in (using
the
formula bar after I select the correct box) and it doesn't seem to want
to
go.
--
jana


"T. Valko" wrote:

Where do we look for the the last text entry?

Here's what you need to do:

Get the address of the cell in question.

Wrap that inside an INDIRECT function.

Give the INDIRECT function a defined name.

Then, as the source for the textbox use =defined_name

--
Biff
Microsoft Excel MVP


"janabanana" wrote in message
...
i know this was a few months back, but i have tried using all of
these
formulas and cannot get one to work with a text box.
i have a text box in a worksheet. if i simply type: =LOG!A1 then it
displays
the text in A1 of my Log sheet.
however, i need it to display the text in the last cell with text in
that
sheet.
i have typed in all of your options and keep getting a "The text you
entered
is not a valid reference or defined name."

Thoughts?
--
jana


"Ron Coderre" wrote:

Try one of these:

The VALUE of the last NUMERIC value in Col_A:
=LOOKUP(10^99,A:A)

The VALUE of the last TEXT cell in Col_A
=LOOKUP(REPT("z",255),A:A)

The VALUE of the last NON-BLANK, NON-ZERO cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<0),A1:A100)

The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"SMH" wrote in message
...
Within one workbook, I'm trying to have one tab read off another
using
a
formula. I need the "Summary" tab to display the last cell with
data
(not
null) in a particular column. For example, column A has data
populated
from
A2:A50, I want to show A50. Any suggestions?

Need a formula to give me the last value in a column.
 

ok, Mr. Formula Man, here is another one for you:
i hope i can describe it well enough for you.
formula history:
sheet = LOG
sheet = PRINT
In the LOG sheet, i have a column (X) that contains a formula where if i
type in a number in another column(Y), it returns a name that equals that
number (displays an operator's name instead of the operator's number).
then in the PRINT sheet, i have a formula that looks back at the LOG sheet,
finds the last non-blank cell in column X, and displays it's value in
PRINT!E51
Problem:
if i type in a number in LOG!columnY, sometimes that number does not have a
name associated with it. in that case, it returns #n/a, i have a conditional
format in this column that says if the return is an error (#n/a) then hide it
(font=white).
but, back in the PRINT sheet, it is still returning the last non-blank cell.
What i need it to return in PRINT! is a blank cell since it couldn't find a
name to match the number.what i want it to do is look at the first non-blank
ROW in the LOG,
formula in PRINT sheet something like this: Destination cell (PRINT!E51) go
to the last non-blank row in LOG!columnA, if it is non-blank, then use the
value in LOG column X, no matter what it is, not necessarily the last
non-blank in column X.

if you are still confused about what i want, i could maybe try sending you
the workbook
--
jana


"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"janabanana" wrote in message
...
Worked wonderfully.
Thank you so much.
--
jana


"T. Valko" wrote:

Try this:

Goto the menu InsertNameDefine
Name: LastCell
Refers to:

=INDIRECT("Log!K"&LOOKUP(2,1/(Log!$K$1:$K$1000<0),ROW(Log!$K$1:$K$1000)))

OK

Click within the textbox to make it active
Enter this formula in the formula bar: =LastCell
Hit ENTER


--
Biff
Microsoft Excel MVP


"janabanana" wrote in message
...
The formula that has worked so far (when typing it in a cell) is:
=LOOKUP(2,1/(LOG!K1:K1000<0),LOG!K1:K1000).

Now I have a text box that I would like to type this formula in (using
the
formula bar after I select the correct box) and it doesn't seem to want
to
go.
--
jana


"T. Valko" wrote:

Where do we look for the the last text entry?

Here's what you need to do:

Get the address of the cell in question.

Wrap that inside an INDIRECT function.

Give the INDIRECT function a defined name.

Then, as the source for the textbox use =defined_name

--
Biff
Microsoft Excel MVP


"janabanana" wrote in message
...
i know this was a few months back, but i have tried using all of
these
formulas and cannot get one to work with a text box.
i have a text box in a worksheet. if i simply type: =LOG!A1 then it
displays
the text in A1 of my Log sheet.
however, i need it to display the text in the last cell with text in
that
sheet.
i have typed in all of your options and keep getting a "The text you
entered
is not a valid reference or defined name."

Thoughts?
--
jana


"Ron Coderre" wrote:

Try one of these:

The VALUE of the last NUMERIC value in Col_A:
=LOOKUP(10^99,A:A)

The VALUE of the last TEXT cell in Col_A
=LOOKUP(REPT("z",255),A:A)

The VALUE of the last NON-BLANK, NON-ZERO cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<0),A1:A100)

The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"SMH" wrote in message
...
Within one workbook, I'm trying to have one tab read off another
using
a
formula. I need the "Summary" tab to display the last cell with
data
(not
null) in a particular column. For example, column A has data
populated
from
A2:A50, I want to show A50. Any suggestions?

Need a formula to give me the last value in a column.
 

Hey, I hope you aren't up this early and haven't started helping me yet.
I decided to make sure that all of the operator numbers had an operator name
associated with it. I added the word TRAIN to my list of names. Because
usually if I have an operator number, but no name, it's because they are in
Training anyway.

Thanks anyway.
--
jana


"janabanana" wrote:

ok, Mr. Formula Man, here is another one for you:
i hope i can describe it well enough for you.
formula history:
sheet = LOG
sheet = PRINT
In the LOG sheet, i have a column (X) that contains a formula where if i
type in a number in another column(Y), it returns a name that equals that
number (displays an operator's name instead of the operator's number).
then in the PRINT sheet, i have a formula that looks back at the LOG sheet,
finds the last non-blank cell in column X, and displays it's value in
PRINT!E51
Problem:
if i type in a number in LOG!columnY, sometimes that number does not have a
name associated with it. in that case, it returns #n/a, i have a conditional
format in this column that says if the return is an error (#n/a) then hide it
(font=white).
but, back in the PRINT sheet, it is still returning the last non-blank cell.
What i need it to return in PRINT! is a blank cell since it couldn't find a
name to match the number.what i want it to do is look at the first non-blank
ROW in the LOG,
formula in PRINT sheet something like this: Destination cell (PRINT!E51) go
to the last non-blank row in LOG!columnA, if it is non-blank, then use the
value in LOG column X, no matter what it is, not necessarily the last
non-blank in column X.

if you are still confused about what i want, i could maybe try sending you
the workbook
--
jana


"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"janabanana" wrote in message
...
Worked wonderfully.
Thank you so much.
--
jana


"T. Valko" wrote:

Try this:

Goto the menu InsertNameDefine
Name: LastCell
Refers to:

=INDIRECT("Log!K"&LOOKUP(2,1/(Log!$K$1:$K$1000<0),ROW(Log!$K$1:$K$1000)))

OK

Click within the textbox to make it active
Enter this formula in the formula bar: =LastCell
Hit ENTER


--
Biff
Microsoft Excel MVP


"janabanana" wrote in message
...
The formula that has worked so far (when typing it in a cell) is:
=LOOKUP(2,1/(LOG!K1:K1000<0),LOG!K1:K1000).

Now I have a text box that I would like to type this formula in (using
the
formula bar after I select the correct box) and it doesn't seem to want
to
go.
--
jana


"T. Valko" wrote:

Where do we look for the the last text entry?

Here's what you need to do:

Get the address of the cell in question.

Wrap that inside an INDIRECT function.

Give the INDIRECT function a defined name.

Then, as the source for the textbox use =defined_name

--
Biff
Microsoft Excel MVP


"janabanana" wrote in message
...
i know this was a few months back, but i have tried using all of
these
formulas and cannot get one to work with a text box.
i have a text box in a worksheet. if i simply type: =LOG!A1 then it
displays
the text in A1 of my Log sheet.
however, i need it to display the text in the last cell with text in
that
sheet.
i have typed in all of your options and keep getting a "The text you
entered
is not a valid reference or defined name."

Thoughts?
--
jana


"Ron Coderre" wrote:

Try one of these:

The VALUE of the last NUMERIC value in Col_A:
=LOOKUP(10^99,A:A)

The VALUE of the last TEXT cell in Col_A
=LOOKUP(REPT("z",255),A:A)

The VALUE of the last NON-BLANK, NON-ZERO cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<0),A1:A100)

The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"SMH" wrote in message
...
Within one workbook, I'm trying to have one tab read off another
using
a
formula. I need the "Summary" tab to display the last cell with
data
(not
null) in a particular column. For example, column A has data
populated
from
A2:A50, I want to show A50. Any suggestions?

Need a formula to give me the last value in a column.
 

Is the formula that returns the operator's name a VLOOKUP or INDEX/
MATCH type formula? If so, then you can avoid returning the #N/A error
by changing it to something like this:

=IF(ISNA(your_formula),"",your_formula)

Then see what effect this has on your formula in the PRINT sheet to
return the last non-blank cell.

Hope this helps (in Biff's absence).

Pete

PS. It always helps to post the formulae that you are using.

On Aug 14, 1:53*pm, janabanana
wrote:
ok, Mr. Formula Man, here is another one for you:
i hope i can describe it well enough for you.
formula history:
sheet = LOG
sheet = PRINT
In the LOG sheet, i have a column (X) that contains a formula where if i
type in a number in another column(Y), it returns a name that equals that
number (displays an operator's name instead of the operator's number).
then in the PRINT sheet, i have a formula that looks back at the LOG sheet,
finds the last non-blank cell in column X, and displays it's value in
PRINT!E51
Problem:
if i type in a number in LOG!columnY, sometimes that number does not have a
name associated with it. in that case, it returns #n/a, i have a conditional
format in this column that says if the return is an error (#n/a) then hide it
(font=white).
but, back in the PRINT sheet, it is still returning the last non-blank cell.
What i need it to return in PRINT! is a blank cell since it couldn't find a
name to match the number.what i want it to do is look at the first non-blank
ROW in the LOG,
formula in PRINT sheet something like this: Destination cell (PRINT!E51) go
to the last non-blank row in LOG!columnA, if it is non-blank, then use the
value in LOG column X, no matter what it is, not necessarily the last
non-blank in column X. *

if you are still confused about what i want, i could maybe try sending you
the workbook
--
jana

Need a formula to give me the last value in a column.
 

here goes:
i have 3 sheets: LOG, PRINT, OPERATORS
LOG: column I contains the formula: =VLOOKUP(H2,'OPERATORSLIST'!a:D,4,FALSE)
the "H2" in the fomula changes to "H3, H4..." depending on what row you are
on. this looks at the OPERATORS LIST and returns the value in column D after
it matches the operator number i typed in H.
I have a conditional format for column I that is =ISERROR(I2) format font to
white. This hides the error messages that I get in the rows that I have not
used yet. It also displays this error message (#N/A) if it cannot match a
number with a name.
PRINT: cell E51 contains the formula:
=LOOKUP(2,1/(LOG!I1:I1000<0),LOG!I1:I1000) this looks for the last populated
cell in LOG column I and displays the value found there. A cell with the
error message #N/A is considered a blank cell.

I want PRINT!E51 to be blank if it finds an error message in LOG!I, if
anything in that same row is non-blank. In otherwords, PRINT!E52 should look
to see if there are any populated columns in the last used row of LOG!, if
there is, always use the value in column I, even if it is blank.

did that help?
--
jana


"Pete_UK" wrote:

Is the formula that returns the operator's name a VLOOKUP or INDEX/
MATCH type formula? If so, then you can avoid returning the #N/A error
by changing it to something like this:

=IF(ISNA(your_formula),"",your_formula)

Then see what effect this has on your formula in the PRINT sheet to
return the last non-blank cell.

Hope this helps (in Biff's absence).

Pete

PS. It always helps to post the formulae that you are using.

On Aug 14, 1:53 pm, janabanana
wrote:
ok, Mr. Formula Man, here is another one for you:
i hope i can describe it well enough for you.
formula history:
sheet = LOG
sheet = PRINT
In the LOG sheet, i have a column (X) that contains a formula where if i
type in a number in another column(Y), it returns a name that equals that
number (displays an operator's name instead of the operator's number).
then in the PRINT sheet, i have a formula that looks back at the LOG sheet,
finds the last non-blank cell in column X, and displays it's value in
PRINT!E51
Problem:
if i type in a number in LOG!columnY, sometimes that number does not have a
name associated with it. in that case, it returns #n/a, i have a conditional
format in this column that says if the return is an error (#n/a) then hide it
(font=white).
but, back in the PRINT sheet, it is still returning the last non-blank cell.
What i need it to return in PRINT! is a blank cell since it couldn't find a
name to match the number.what i want it to do is look at the first non-blank
ROW in the LOG,
formula in PRINT sheet something like this: Destination cell (PRINT!E51) go
to the last non-blank row in LOG!columnA, if it is non-blank, then use the
value in LOG column X, no matter what it is, not necessarily the last
non-blank in column X.

if you are still confused about what i want, i could maybe try sending you
the workbook
--
jana

Need a formula to give me the last value in a column.
 

i got it!
sometimes if i mess around a little, i can figure things out.
i changed the formula in E52 in the print sheet from:
=LOOKUP(2,1/(LOG!I1:I1000<0),LOG!I1:I1000)
to:
=LOOKUP(2,1/(LOG!A1:A1000<0),LOG!I1:I1000)
I then added a conditional format to not display if it was #N/A

it worked.

--
jana


"Pete_UK" wrote:

Is the formula that returns the operator's name a VLOOKUP or INDEX/
MATCH type formula? If so, then you can avoid returning the #N/A error
by changing it to something like this:

=IF(ISNA(your_formula),"",your_formula)

Then see what effect this has on your formula in the PRINT sheet to
return the last non-blank cell.

Hope this helps (in Biff's absence).

Pete

PS. It always helps to post the formulae that you are using.

On Aug 14, 1:53 pm, janabanana
wrote:
ok, Mr. Formula Man, here is another one for you:
i hope i can describe it well enough for you.
formula history:
sheet = LOG
sheet = PRINT
In the LOG sheet, i have a column (X) that contains a formula where if i
type in a number in another column(Y), it returns a name that equals that
number (displays an operator's name instead of the operator's number).
then in the PRINT sheet, i have a formula that looks back at the LOG sheet,
finds the last non-blank cell in column X, and displays it's value in
PRINT!E51
Problem:
if i type in a number in LOG!columnY, sometimes that number does not have a
name associated with it. in that case, it returns #n/a, i have a conditional
format in this column that says if the return is an error (#n/a) then hide it
(font=white).
but, back in the PRINT sheet, it is still returning the last non-blank cell.
What i need it to return in PRINT! is a blank cell since it couldn't find a
name to match the number.what i want it to do is look at the first non-blank
ROW in the LOG,
formula in PRINT sheet something like this: Destination cell (PRINT!E51) go
to the last non-blank row in LOG!columnA, if it is non-blank, then use the
value in LOG column X, no matter what it is, not necessarily the last
non-blank in column X.

if you are still confused about what i want, i could maybe try sending you
the workbook
--
jana