Adam here again... I am still working with my custom reports, linking price databases, etc. Right now I am working with the Extron Library and I am having the excel system attributes spreadsheet reference a table (in the same sheet) with all of our extron pricing. I am doing this with the VLOOKUP function. This worked wonderfully until I realized that more than half of the Stardraw symbols for Extron do not go by their XX-XXX-XX Part number system... Now, I am willing to go through and update all the model numbers on each symbol if that is the only way to correct this, but I am afraid that when I do an update of the attributes file it will write over these changes... I really don't get why the model/part numbers are inconsistant. Is there a reason for this?? Would it be possible, in the future, for the stardraw symbols to use the same consistant and appropriate model numbers? This goes for any other manufacturer part numbers as well.

come to think about it... is it even possible for me to change the part numbers for the offline symbols?? (I'll just keep rattling off ideas as they come to me).

My newest thought is that I could have the VLOOKUP function in excel search for each specific part number in the price list if it cannot find it based on the symbol attribute entry. This would be a quick fix until there is some standardization with the Stardraw symbol part numbers.

Hi Adam

Your knowledge of the Extron range far exceeds my own, but I understand that the apparent naming inconsistency derives from the way in which Extron products were referred to historically.  They have a Model Number that is 'friendly' (e.g.  RGB324) and a Part Number of the format xx-xxx-xx.  In the early days we would use the Model Number, but where the same Model Number had variants we would create a folder with the Model Number and show the Part Numbers beneath it, for example, variants of the RGB324 have part numbers 60-234-01, 60-234-02 and 60-234-03 which in Stardraw are included in the RGB324 folder.

I think your plan to use VLOOKUP is a good one.  You are right that you can't change the model number that is stored in the offline libraries so the missing part of the jigsaw, in my opinion, is a column in the external datasource spreadsheet, lets call it "EPN",  which gives standardized Extron Part Numbers.  Where we reference Extron Part Numbers the entry in the EPN column would be the same as the Stardraw Model Number.  Where we use Extron Model Numbers you could replace them with Part Numbers.  The VLOOKUP function can then use the EPN column to extract the data from your pricing database.

I note that some Stardraw Model Numbers are Extron Part Numbers but without hyphens.  You could use the following formula to tidy this up  (albeit rather brutally) in the EPN column. This example would work in row 2.

=IF(AND(MID(A2,3,1)<>"-",LEN(A2)=7),CONCATENATE(LEFT(A2,2),"-",MID(A2,3,3),"-",RIGHT(A2,2)),A2)

The remaining Extron Model Numbers could be changed by hand, and you would only need to do this once.

If you went through this exercise and established a fully standardized EPN column we would be very grateful if you would share the information with us; we could then use it to apply a standardized naming schema to the entire Extron product range. 

I hope this is helpful.

Rob

Kind regards,
Rob Robinson
Stardraw.com

Excellent idea regarding a column in the System Attributes file completely devoted to exact manufacturer part numbers... I didn't realize that Stardraw wouldn't have a problem if the attributes columns got pushed around, out of order. I guess it only cares about the column with the heading "cost price" or "sale price", etc. In that case, I will definitely go through and type out all the correct extron part numbers and pass along the result.

Also, thank you for that formula regarding the non-hyphened part numbers. I haven't tried it yet, but I'll let you know when I do. Thank you again for the more than speedy result. Definitely above and beyond. talk to you soon...

Oh, one more question... actually two... (while I have you here)...

#1. Is there any way to lock the table array into place on the spread sheet next to or below the Stardraw columns?? If I put it to the right, new columns will get inserted each time an update occurs and it will put the array all out of whack... If I put it below, no problems with the inserted rows, but it will... oh wait... it should push the formula numbers down as well... I thought the "$" place holders would hold the array, but I guess not... I talked myself down from that question...

#2. What are your plans regarding outdated (retired,discontinued) parts for a specific manufacturer. I appreciate the idea of keeping them in the library for back compatibility (in case you want to view an old design), but it definitely clutters things up. This is especially true if there is an updated replacement. In going through the extron product library I found a lot of parts that had been discontinued... the other manufacturer that stands out in my memory is Marantz. Once I get to the point where I have all that I can update, well, updated, I'll try to concentrate on creating new symbols for parts that are not in the libraries yet. Is there any list online that stipulates which symbol was created when? or perhaps what symbols are up and coming?? I think this would be a very helpful resource. Thats all for now.

While going through finding the individual part numbers, I find that the model name (ex. MAV 2412) really does help. So if it is possible (in a future update) to have both of these properties as attributes, it would really work well. Then you could reference either one.

Hi Adam,

You've lost me with your question about the 'table array'.  I assume this is the data range from which you get the pricing information.  I would not have expected this to be held in the System Attributes spreadsheet, but rather it should be part of your regular pricing file.  I attach a very basic example of how you could do it - unzip the attachments into "C:\Program Files\Stardraw AV".

In the example, prices are held in ExtronPricing.xls which is totally independent.  In "Stardraw AV System Attributes(Test).xls" Cost Price and Sale Price are looked up based on the Part Number in the EPN column.  The advantages of doing it this way are:

1) You can manage your pricing in a separate file, or look up directly from another existing spreadsheet
2) Existing values and look-up data will not be affected when you Synchronize Manufacturers
3) When new products are added you just need to copy the formulae in 'Cost Price', 'Sale Price' and 'EPN' for the new products and the new price attribute values will just magically appear.

All the best

Rob 

Kind regards,
Rob Robinson
Stardraw.com

ahhhh.... see, i didn't know you could look up information in other spreadsheets. That's why I was using the VLOOKUP function to check a table of data within the sheet. This will make a world of difference and save a lot of time. I shall get to work on this right away! Any thoughts about the discontinued products question?