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

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

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

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

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

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

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

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

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