Formula: Return last entry in column

[Guest]

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

 

[Bernard Liengme]

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

 

[Guest]

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...

 

[Guest]

Worked like a charm! It's a keeper. Again, thanks!

 

[Guest]

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]

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,COUNTA(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

 

[Guest]

Amazing. Thanks so much.

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,COUNTA(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

 

[Guest]

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]

Send me (my private email) a sample file and outline what is needed
best wishes