| 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.
=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? |
|
#3
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? |
|
#4
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? |
|
#5
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? |
|
#6
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? |
|
#7
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? |
|
#8
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? |
|
#9
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? |
|
#10
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? |
|
#11
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? |
|
#12
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? |
| 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 | |||
Hybrid Mode
