Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
divide column(x) by column(y) to give column(x/y) in excel? | New Users to Excel | |||
Evaluating One Column To Give A Answer To A Third | Excel Worksheet Functions | |||
Give RELEVANT responses to questions. DO NOT give usless list | Excel Worksheet Functions | |||
How can give the same name to a row or column in different sheets. | Excel Discussion (Misc queries) | |||
calculate which cells in column A will give me the total of column | Excel Worksheet Functions |