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

[SMH]

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?

 

[Ron Coderre]

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)

 

[BoniM]

=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]

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

 

[SMH]

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.

 

[BoniM]

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]

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

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]

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)

 

[SMH]

Nevermind! I got it!

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

 

[janabanana]

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?

 

[T. Valko]

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

 

[janabanana]

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.

 

[T. Valko]

Try this:

Goto the menu Insert>Name>Define
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

 

[janabanana]

Worked wonderfully.
Thank you so much.
--
jana

Try this:

Goto the menu Insert>Name>Define
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

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[janabanana]

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

 

[janabanana]

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.

 

[Pete_UK]

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.

 

[janabanana]

here goes:
i have 3 sheets: LOG, PRINT, OPERATORS
LOG: column I contains the formula: =VLOOKUP(H2,'OPERATORSLIST'!a,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

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.

 

[janabanana]

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

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.