need help for my drawing list

[PhilH]

Hi,

I have an excel workbook with two tabs.

Each tab is an inventory of all my drawings. One tab has only the most recent revision of every drawing, while the second tab has all revisions.

for example the first tab would have

name -- -- - rev -- date
drawing a -- 000 -- july 10th
drawing b -- 002 -- august 12th
drawing c -- 000 -- july 10th
drawing d -- 000 -- july 10th

the second tab would have

name -- -- - rev -- date
drawing a -- 000 -- july 10th
drawing b -- 000 -- july 10th
drawing b -- 001 -- august 1st
drawing b -- 002 -- august 12th
drawing c -- 000 -- july 10th
drawing d -- 000 -- july 10th

What I am trying to do is set it up so that when I update with a new revision in the first tab in the first tab, it automatically adds a new line with the updated revision in the 2nd.

For example, if I were to make the following change to the first tab:

name -- -- - rev -- date
drawing a -- 000 -- july 10th
drawing b -- 002 -- august 12th
drawing c -- 001 -- sept 4th
drawing d -- 000 -- july 10th

the second tab would automatically become

name -- -- - rev -- date
drawing a -- 000 -- july 10th
drawing b -- 000 -- july 10th
drawing b -- 001 -- august 1st
drawing b -- 002 -- august 12th
drawing c -- 000 -- july 10th
drawing c -- 001 -- sept 4th
drawing d -- 000 -- july 10th

Can anyone help me with this?

Thanks

 

[PhilH]

tried a lot of stuff and searched google but can't figure it out

is this even possible?

 

[Taul]

May I ask if you are using AutoCAD? (LT or Full version)
and are you entering the drawing information manually onto the spreadsheet or are you utilising the AutoCAD attribute extraction utility?
If you are using attribute extraction, there is an easier way of maintaining a drawing record in a spreadsheet along with the filter buttons.
It would be useful to understand a bit more about the your system/process as a whole, to know which way to jump.

 

[PhilH]

May I ask if you are using AutoCAD? (LT or Full version)
and are you entering the drawing information manually onto the spreadsheet or are you utilising the AutoCAD attribute extraction utility?
If you are using attribute extraction, there is an easier way of maintaining a drawing record in a spreadsheet along with the filter buttons.
It would be useful to understand a bit more about the your system/process as a whole, to know which way to jump.

I'm receiving the drawings from an engineering firm in PDF format and keeping record of the drawing name, the revision #, and the transmittal date in an excel spreadsheet. Basically I'd like one tab to show only the most recent revision, and the 2nd tab to show all revisions. However I'd like for the 2nd tab to update automatically when I manually update the first tab.

 

[Taul]

Using your page tab names of “FirstTab” for the latest drawing list and “SecondTab” for the master list containing all the drawing info.

On the FirstTab in cell B2 use

=MAX((SecondTab!A$2:A$100=A2)*(SecondTab!B$2:B$100))

Use Ctrl + Shift + Enter, not just the Enter key

 

[Taul]

In cell C2 on the "FirstTab" use

=INDEX(SecondTab!$C$2:$C$20,MATCH(1,(A2=SecondTab!$A$2:$A$20)*(B2=SecondTab!$B$2:$B$20),0))

use Ctrl + Shift + Enter

 

[PhilH]

In cell C2 on the "FirstTab" use

=INDEX(SecondTab!$C$2:$C$20,MATCH(1,(A2=SecondTab!$A$2:$A$20)*(B2=SecondTab!$B$2:$B$20),0))

use Ctrl + Shift + Enter

thanks for the solution Paul. Now my only issue is that sometimes the revisions are in letters rather than numbers, also, numbers come after letters.

For example drawing_a rev0 would come after drawing_a revB