Drawing Revisions (aslo for Specifications)

[Frank]

Drawing Revisions are tracked first "in house" with a Letter. Then
when issued to a client they are issued with a Number. For Example:


blank line
blank line etc.
2 Issued for Bids
1 Issued for Client Approval
C Issued for Departmental Check
B Issued for Review
A Issued for Comment

Please bear in mind the Revision list may have several blank lines
first. (For future revisions). The latest issue is always at the top
of the list.

The Problem
There are Revision cells on other Excel worksheets. What formula do I
put in the other worksheet Revision cell to pick up the latest Revision
from the list automatically.

I am familiar with VBA but I don't believe there is an "xlStart" like
"xldown" type command to choose the top of a list.

 

[Roger Govier]

Hi Frank

If you are looking for the Row number where the first non blank occurs
then
=MATCH(TRUE,INDEX(A1:A1000<>"",0,1),0)
To find what the value is in that first non-blank cell
=INDEX(A1:A1000,MATCH(TRUE,INDEX(A1:A1000<>"",0,1),0))

Change the ranges to suit

 

[Frank]

Hey that solution was just amazing. However when I tried it on the
next column, Date of Revision, it did not work. Can you suggest
something that will work?

 

[Roger Govier]

Hi Frank

What data do you have in the next column? What is the formula you used
for that column?
What didn't work, what was the result?

 

[Frank]

The column next to the revision letter/number column contains the date.
I am not sure how the cell is formated, but most likely the cell is
formated as a date, day-mon-year, as this is a middle east, metric job.
I think I entered 9/30/2006, for instance, and let Excel transform the
date to the form required.
When I used your excellent formula on that column I got 0-Jan-00 or an
odd number, not the date number as I might have expected.

Thanks for the help! It will save a bunch of engineers time on
revisions, etc.

 

[Roger Govier]

Hi Frank

Try Format>Cell>General
It is probably returning a row number which is being translated by Excel
into a date as that number of days after 00 Jan 1900.

--
Regards

Roger Govier

The column next to the revision letter/number column contains the
date.
I am not sure how the cell is formated, but most likely the cell is
formated as a date, day-mon-year, as this is a middle east, metric
job.
I think I entered 9/30/2006, for instance, and let Excel transform the
date to the form required.
When I used your excellent formula on that column I got 0-Jan-00 or an
odd number, not the date number as I might have expected.

Thanks for the help! It will save a bunch of engineers time on
revisions, etc.