| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula: Return last entry in column
Greetings. On one tab i have several projects (taking approx 6 columns each),
each having several rows beneath with project steps. The project steps each have a corresponging Status colum (column to the right of the step). I've constructed a summary tab that returns certain hi level data elements for each project (using hlookup). However, i'd like to include a current status column in the summary tab, for each project. Assuming the current status is the last status listed in the status column, how to i return that value to the summary tab? Hope someone out there can help. Many thanks. Formula for summary tab this column? project tab Start Status Proj1 Proj2 Proj1 2/1/07 On schedule Start: 2/1/07 Start: 3/3/07 Proj2 3/3/07 Behind schedule Step1: On schedule Step1: Behind schedule Step2: Behind schedule Step2: On schedule Step3: On schedule Step3: Behind schedule |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula: Return last entry in column
On Sheet three starting in A1 I have three columns of text
apple dog blue pear cat green peach bird yellow plum red prune On Sheet1 I have these formulas =OFFSET(Sheet3!A:A,COUNTA(Sheet3!A:A)-1,0,1) =OFFSET(Sheet3!B:B,COUNTA(Sheet3!B:B)-1,0,1) =OFFSET(Sheet3!C:C,COUNTA(Sheet3!C:C)-1,0,1) and they return, respectively prune bird red Hope you can adapt to your use best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Suzanne" wrote in message ... Greetings. On one tab i have several projects (taking approx 6 columns each), each having several rows beneath with project steps. The project steps each have a corresponging Status colum (column to the right of the step). I've constructed a summary tab that returns certain hi level data elements for each project (using hlookup). However, i'd like to include a current status column in the summary tab, for each project. Assuming the current status is the last status listed in the status column, how to i return that value to the summary tab? Hope someone out there can help. Many thanks. Formula for summary tab this column? project tab Start Status Proj1 Proj2 Proj1 2/1/07 On schedule Start: 2/1/07 Start: 3/3/07 Proj2 3/3/07 Behind schedule Step1: On schedule Step1: Behind schedule Step2: Behind schedule Step2: On schedule Step3: On schedule Step3: Behind schedule |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula: Return last entry in column
How Exciting Bernard! Thank you. I'm going to try straight away. Thanks
again for responding so quickly. I'll post back in a few... "Bernard Liengme" wrote: On Sheet three starting in A1 I have three columns of text apple dog blue pear cat green peach bird yellow plum red prune On Sheet1 I have these formulas =OFFSET(Sheet3!A:A,COUNTA(Sheet3!A:A)-1,0,1) =OFFSET(Sheet3!B:B,COUNTA(Sheet3!B:B)-1,0,1) =OFFSET(Sheet3!C:C,COUNTA(Sheet3!C:C)-1,0,1) and they return, respectively prune bird red Hope you can adapt to your use best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Suzanne" wrote in message ... Greetings. On one tab i have several projects (taking approx 6 columns each), each having several rows beneath with project steps. The project steps each have a corresponging Status colum (column to the right of the step). I've constructed a summary tab that returns certain hi level data elements for each project (using hlookup). However, i'd like to include a current status column in the summary tab, for each project. Assuming the current status is the last status listed in the status column, how to i return that value to the summary tab? Hope someone out there can help. Many thanks. Formula for summary tab this column? project tab Start Status Proj1 Proj2 Proj1 2/1/07 On schedule Start: 2/1/07 Start: 3/3/07 Proj2 3/3/07 Behind schedule Step1: On schedule Step1: Behind schedule Step2: Behind schedule Step2: On schedule Step3: On schedule Step3: Behind schedule |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula: Return last entry in column
Worked like a charm! It's a keeper. Again, thanks!
"Bernard Liengme" wrote: On Sheet three starting in A1 I have three columns of text apple dog blue pear cat green peach bird yellow plum red prune On Sheet1 I have these formulas =OFFSET(Sheet3!A:A,COUNTA(Sheet3!A:A)-1,0,1) =OFFSET(Sheet3!B:B,COUNTA(Sheet3!B:B)-1,0,1) =OFFSET(Sheet3!C:C,COUNTA(Sheet3!C:C)-1,0,1) and they return, respectively prune bird red Hope you can adapt to your use best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Suzanne" wrote in message ... Greetings. On one tab i have several projects (taking approx 6 columns each), each having several rows beneath with project steps. The project steps each have a corresponging Status colum (column to the right of the step). I've constructed a summary tab that returns certain hi level data elements for each project (using hlookup). However, i'd like to include a current status column in the summary tab, for each project. Assuming the current status is the last status listed in the status column, how to i return that value to the summary tab? Hope someone out there can help. Many thanks. Formula for summary tab this column? project tab Start Status Proj1 Proj2 Proj1 2/1/07 On schedule Start: 2/1/07 Start: 3/3/07 Proj2 3/3/07 Behind schedule Step1: On schedule Step1: Behind schedule Step2: Behind schedule Step2: On schedule Step3: On schedule Step3: Behind schedule |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula: Return last entry in column
Bernard, anyway to incorporate an hlookup type function as i will not know
which columns proj1 and its status will be in? The projects haven't been input into the sheet yet. I'd like to set it up so that wherever the project happens to be on the project tab, the summary tab finds the appropriate status... "Bernard Liengme" wrote: On Sheet three starting in A1 I have three columns of text apple dog blue pear cat green peach bird yellow plum red prune On Sheet1 I have these formulas =OFFSET(Sheet3!A:A,COUNTA(Sheet3!A:A)-1,0,1) =OFFSET(Sheet3!B:B,COUNTA(Sheet3!B:B)-1,0,1) =OFFSET(Sheet3!C:C,COUNTA(Sheet3!C:C)-1,0,1) and they return, respectively prune bird red Hope you can adapt to your use best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Suzanne" wrote in message ... Greetings. On one tab i have several projects (taking approx 6 columns each), each having several rows beneath with project steps. The project steps each have a corresponging Status colum (column to the right of the step). I've constructed a summary tab that returns certain hi level data elements for each project (using hlookup). However, i'd like to include a current status column in the summary tab, for each project. Assuming the current status is the last status listed in the status column, how to i return that value to the summary tab? Hope someone out there can help. Many thanks. Formula for summary tab this column? project tab Start Status Proj1 Proj2 Proj1 2/1/07 On schedule Start: 2/1/07 Start: 3/3/07 Proj2 3/3/07 Behind schedule Step1: On schedule Step1: Behind schedule Step2: Behind schedule Step2: On schedule Step3: On schedule Step3: Behind schedule |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula: Return last entry in column
On Sheet3 I inserted a new row.
A1 has Proj 1, B1 has Proj 2, C1 has Proj 3 On Sheet 1 in A7 I enter: Proj N (N being 1,2 or 3) I use =IF(MATCH(A7,Sheet3!A1:C1,0)=1,OFFSET(Sheet3!A:A,C OUNTA(Sheet3!A:A)-1,0,1),IF(MATCH(A7,Sheet3!A1:C1,0)=2,OFFSET(Sheet3 !B:B,COUNTA(Sheet3!B:B)-1,0,1),IF(MATCH(A7,Sheet3!A1:C1,0)=3,OFFSET(Sheet3 !C:C,COUNTA(Sheet3!C:C)-1,0,1)))) to get the last item in the appropriate column best wsihes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Suzanne" wrote in message ... Bernard, anyway to incorporate an hlookup type function as i will not know which columns proj1 and its status will be in? The projects haven't been input into the sheet yet. I'd like to set it up so that wherever the project happens to be on the project tab, the summary tab finds the appropriate status... "Bernard Liengme" wrote: On Sheet three starting in A1 I have three columns of text apple dog blue pear cat green peach bird yellow plum red prune On Sheet1 I have these formulas =OFFSET(Sheet3!A:A,COUNTA(Sheet3!A:A)-1,0,1) =OFFSET(Sheet3!B:B,COUNTA(Sheet3!B:B)-1,0,1) =OFFSET(Sheet3!C:C,COUNTA(Sheet3!C:C)-1,0,1) and they return, respectively prune bird red Hope you can adapt to your use best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Suzanne" wrote in message ... Greetings. On one tab i have several projects (taking approx 6 columns each), each having several rows beneath with project steps. The project steps each have a corresponging Status colum (column to the right of the step). I've constructed a summary tab that returns certain hi level data elements for each project (using hlookup). However, i'd like to include a current status column in the summary tab, for each project. Assuming the current status is the last status listed in the status column, how to i return that value to the summary tab? Hope someone out there can help. Many thanks. Formula for summary tab this column? project tab Start Status Proj1 Proj2 Proj1 2/1/07 On schedule Start: 2/1/07 Start: 3/3/07 Proj2 3/3/07 Behind schedule Step1: On schedule Step1: Behind schedule Step2: Behind schedule Step2: On schedule Step3: On schedule Step3: Behind schedule |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula: Return last entry in column
Amazing. Thanks so much.
"Bernard Liengme" wrote: On Sheet3 I inserted a new row. A1 has Proj 1, B1 has Proj 2, C1 has Proj 3 On Sheet 1 in A7 I enter: Proj N (N being 1,2 or 3) I use =IF(MATCH(A7,Sheet3!A1:C1,0)=1,OFFSET(Sheet3!A:A,C OUNTA(Sheet3!A:A)-1,0,1),IF(MATCH(A7,Sheet3!A1:C1,0)=2,OFFSET(Sheet3 !B:B,COUNTA(Sheet3!B:B)-1,0,1),IF(MATCH(A7,Sheet3!A1:C1,0)=3,OFFSET(Sheet3 !C:C,COUNTA(Sheet3!C:C)-1,0,1)))) to get the last item in the appropriate column best wsihes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Suzanne" wrote in message ... Bernard, anyway to incorporate an hlookup type function as i will not know which columns proj1 and its status will be in? The projects haven't been input into the sheet yet. I'd like to set it up so that wherever the project happens to be on the project tab, the summary tab finds the appropriate status... "Bernard Liengme" wrote: On Sheet three starting in A1 I have three columns of text apple dog blue pear cat green peach bird yellow plum red prune On Sheet1 I have these formulas =OFFSET(Sheet3!A:A,COUNTA(Sheet3!A:A)-1,0,1) =OFFSET(Sheet3!B:B,COUNTA(Sheet3!B:B)-1,0,1) =OFFSET(Sheet3!C:C,COUNTA(Sheet3!C:C)-1,0,1) and they return, respectively prune bird red Hope you can adapt to your use best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Suzanne" wrote in message ... Greetings. On one tab i have several projects (taking approx 6 columns each), each having several rows beneath with project steps. The project steps each have a corresponging Status colum (column to the right of the step). I've constructed a summary tab that returns certain hi level data elements for each project (using hlookup). However, i'd like to include a current status column in the summary tab, for each project. Assuming the current status is the last status listed in the status column, how to i return that value to the summary tab? Hope someone out there can help. Many thanks. Formula for summary tab this column? project tab Start Status Proj1 Proj2 Proj1 2/1/07 On schedule Start: 2/1/07 Start: 3/3/07 Proj2 3/3/07 Behind schedule Step1: On schedule Step1: Behind schedule Step2: Behind schedule Step2: On schedule Step3: On schedule Step3: Behind schedule |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula: Return last entry in column
Me again. Hi. Using your first formula, i'm running into an issue because
the cells in the 'status' column contain a formula that calculates the status. Hence, the counta is counting the cells with formulas and no status. However, the column to the left of the status column is Task Complete Date, manual date input. I tried on my own to modify the formula to look for the last entry in the Taks Complete Date column and return the value from the same row, next column to the right, being the status. I was unsucessful. Any ideas? btw, i checked out your web site, very nice. "Bernard Liengme" wrote: On Sheet3 I inserted a new row. A1 has Proj 1, B1 has Proj 2, C1 has Proj 3 On Sheet 1 in A7 I enter: Proj N (N being 1,2 or 3) I use =IF(MATCH(A7,Sheet3!A1:C1,0)=1,OFFSET(Sheet3!A:A,C OUNTA(Sheet3!A:A)-1,0,1),IF(MATCH(A7,Sheet3!A1:C1,0)=2,OFFSET(Sheet3 !B:B,COUNTA(Sheet3!B:B)-1,0,1),IF(MATCH(A7,Sheet3!A1:C1,0)=3,OFFSET(Sheet3 !C:C,COUNTA(Sheet3!C:C)-1,0,1)))) to get the last item in the appropriate column best wsihes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Suzanne" wrote in message ... Bernard, anyway to incorporate an hlookup type function as i will not know which columns proj1 and its status will be in? The projects haven't been input into the sheet yet. I'd like to set it up so that wherever the project happens to be on the project tab, the summary tab finds the appropriate status... "Bernard Liengme" wrote: On Sheet three starting in A1 I have three columns of text apple dog blue pear cat green peach bird yellow plum red prune On Sheet1 I have these formulas =OFFSET(Sheet3!A:A,COUNTA(Sheet3!A:A)-1,0,1) =OFFSET(Sheet3!B:B,COUNTA(Sheet3!B:B)-1,0,1) =OFFSET(Sheet3!C:C,COUNTA(Sheet3!C:C)-1,0,1) and they return, respectively prune bird red Hope you can adapt to your use best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Suzanne" wrote in message ... Greetings. On one tab i have several projects (taking approx 6 columns each), each having several rows beneath with project steps. The project steps each have a corresponging Status colum (column to the right of the step). I've constructed a summary tab that returns certain hi level data elements for each project (using hlookup). However, i'd like to include a current status column in the summary tab, for each project. Assuming the current status is the last status listed in the status column, how to i return that value to the summary tab? Hope someone out there can help. Many thanks. Formula for summary tab this column? project tab Start Status Proj1 Proj2 Proj1 2/1/07 On schedule Start: 2/1/07 Start: 3/3/07 Proj2 3/3/07 Behind schedule Step1: On schedule Step1: Behind schedule Step2: Behind schedule Step2: On schedule Step3: On schedule Step3: Behind schedule |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula: Return last entry in column
Send me (my private email) a sample file and outline what is needed
best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Suzanne" wrote in message ... Me again. Hi. Using your first formula, i'm running into an issue because the cells in the 'status' column contain a formula that calculates the status. Hence, the counta is counting the cells with formulas and no status. However, the column to the left of the status column is Task Complete Date, manual date input. I tried on my own to modify the formula to look for the last entry in the Taks Complete Date column and return the value from the same row, next column to the right, being the status. I was unsucessful. Any ideas? btw, i checked out your web site, very nice. "Bernard Liengme" wrote: On Sheet3 I inserted a new row. A1 has Proj 1, B1 has Proj 2, C1 has Proj 3 On Sheet 1 in A7 I enter: Proj N (N being 1,2 or 3) I use =IF(MATCH(A7,Sheet3!A1:C1,0)=1,OFFSET(Sheet3!A:A,C OUNTA(Sheet3!A:A)-1,0,1),IF(MATCH(A7,Sheet3!A1:C1,0)=2,OFFSET(Sheet3 !B:B,COUNTA(Sheet3!B:B)-1,0,1),IF(MATCH(A7,Sheet3!A1:C1,0)=3,OFFSET(Sheet3 !C:C,COUNTA(Sheet3!C:C)-1,0,1)))) to get the last item in the appropriate column best wsihes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Suzanne" wrote in message ... Bernard, anyway to incorporate an hlookup type function as i will not know which columns proj1 and its status will be in? The projects haven't been input into the sheet yet. I'd like to set it up so that wherever the project happens to be on the project tab, the summary tab finds the appropriate status... "Bernard Liengme" wrote: On Sheet three starting in A1 I have three columns of text apple dog blue pear cat green peach bird yellow plum red prune On Sheet1 I have these formulas =OFFSET(Sheet3!A:A,COUNTA(Sheet3!A:A)-1,0,1) =OFFSET(Sheet3!B:B,COUNTA(Sheet3!B:B)-1,0,1) =OFFSET(Sheet3!C:C,COUNTA(Sheet3!C:C)-1,0,1) and they return, respectively prune bird red Hope you can adapt to your use best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Suzanne" wrote in message ... Greetings. On one tab i have several projects (taking approx 6 columns each), each having several rows beneath with project steps. The project steps each have a corresponging Status colum (column to the right of the step). I've constructed a summary tab that returns certain hi level data elements for each project (using hlookup). However, i'd like to include a current status column in the summary tab, for each project. Assuming the current status is the last status listed in the status column, how to i return that value to the summary tab? Hope someone out there can help. Many thanks. Formula for summary tab this column? project tab Start Status Proj1 Proj2 Proj1 2/1/07 On schedule Start: 2/1/07 Start: 3/3/07 Proj2 3/3/07 Behind schedule Step1: On schedule Step1: Behind schedule Step2: Behind schedule Step2: On schedule Step3: On schedule Step3: Behind schedule |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula follows the most recent entry in a column??? | Excel Discussion (Misc queries) | |||
| Formula to Return Column Heading | Excel Discussion (Misc queries) | |||
| Return entry in column above/below cell | Excel Worksheet Functions | |||
| Function to return last entry in a column series? | Excel Worksheet Functions | |||
| How do i make a formula reference the last entry of a column | Excel Worksheet Functions | |||
Hybrid Mode
