Counting cells in two separate files

[Virgule]

Hi,

I currently have an excel file containing parts with a few characteristics :


-----A----------B-----------C--------------D-------------E
Part type----1rst letter----2nd letter----1rst number-----Name
--Valve---------D------------V-------------0-----------DV0001
---Pipe---------D------------L-------------0-----------DL0001
---Valve-------D-------------V-------------0-----------DV0002
---Valve--------D------------V-------------1-----------DV1001
---Pipe---------F-------------L-------------0-----------FL0001
...and so on...

Here's the logic for naming the parts. The name consists of 6 characters : the first letter, the second letter, the first number and a three digit number. The first letter is logically chosen depending on the type of liquid that flows through the part. The second letter is automatically chosen depending on the part type (another worksheet contains an array with all part types and associated 2nd letters). The 1rst number is also chosen by the user.

The name cells have the following formula :
E2=B2&C2&D2&TEXT(SUMPRODUCT(- - ($B$2:B2=B2),- - ($C$2:C2=C2),- - ($D$2:D2=D2)),"000")
E3=B3&C3&D3&TEXT(SUMPRODUCT(- - ($B$2:B3=B3),- - ($C$2:C3=C3),- - ($D$2:D3=D3)),"000")
...and so on...
So the part name is a concatenation of the first 3 characters followed by an incrementing 3 digit number counting all parts with the same first 3 characters.

Here's my problem :
My HUGE list of parts now must be separated on 2 files. I want that the part names in the 2nd file take the 1rst file's name list into consideration when counting.

Here's an example :

1rst file
--A------------B-------------C-------------D----------E
Part type----1rst letter---2nd letter---1rst number----Name
--Valve----------D-----------V-----------0---------DV0001
---Pipe----------D-----------L------------0---------DL0001

2nd file
--A------------B------------C--------------D-----------E
Part type---1rst letter----2nd letter---1rst number-----Name
---Valve-------D------------V-------------0---------DV0002
---Valve-------D------------V-------------1---------DV1001
---Pipe--------F-------------L-------------0---------FL0001
...

Is that possible? If yes, what is the limitation of the solution? Can it still work if both files are in two separate folders and if both are on a server and the user opens only the 2nd file?

Thank you in advance.

 

[Saagar]

Virgule - Here is one suggestion.

In your first file, instead of using the original formula:
E2=B2&C2&D2&TEXT(SUMPRODUCT(- - ($B$2:B2=B2),- - ($C$2:C2=C2),- - ($D$2:D2=D2)),"000")
Use this approach:
F2=B2&C2&D2
G2=F2&TEXT(COUNTIF($F$2:F2,F2),"000")

If this formula works to your satisfaction in the first file, you may extend it easily to the second workbook. In the second workbook, the formulae will be:
F2=B2&C2&D2
G2=F2&TEXT(COUNTIF(='C:\Filepath\'[File1name.xls]SheetName!F:F,F2)+COUNTIF($F$2:F2,F2),"000")

Let me know if this helped.

 

[Saagar]

Just realized there is a typo in the second formula for G2. It should be
G2=F2&TEXT(COUNTIF('C:\Filepath\'[File1name.xls]SheetName!F:F,F2)+COUNTIF($F$2:F2,F2),"000")

 

[Virgule]

Thanks, I'll try it