BOM item number customization? 4

[diamondcat]

I was wondering if anyone knows if there is a way to customize the numbers in the BOM.
I want to have my make items as 1-x and my purchase items as $1-$x. What I am doing right now is editing the BOM and manually typing in detail numbers. The problem with this is that they are then not connected to my balloons (have to type the numbers in those manually also) and when I make any change to my details, my manual inputs for that detail disappear from the BOM and I have to put them back in.
I would appreciate any input.
Thanks

 

[StarrRider]

TheTick

I don’t mean to criticize - but I do have to say - That is the most long-winded method for writing a Concatenate Statement that I have ever seen

I looked at it for a couple of minutes and then started taking it apart
Indirect – Returns the reference specified by a text string
Index – Returns a value or the reference to a cell from within a table or range
Address – Creates a cell reference as text, given a specified row and column numbers
& – This is Not listed in Fx - BUT

Adding =B2&C2 to cell A2 does exactly the same thing as Concatenate
You can add as much junk as you like =B2&” – “&B3 creates B2Junk – B3Junk
I Like that – That is a 100% Pure GOLD Tip (another Star from me) - I didn’t know the VB “&” worked in a cell’s formula – It make me wonder what else does

But I would like to know – Why are you using Indirect, Index, and Address?
Address is normally used when converting an unknown number to a cell reference like BC3
Indirect and Index are normally used when the range being combined is unknown to the programmer

Lee

Consciousness: That annoying time between naps.

 

[TheTick]

The INDIRECT method allows one to combine any two properties, regardless of the location in the Excel file. If I were to insert a new column in my Excel BOM template, the INDIRECT would automatically adjust for that.

Another possible scenario is having more than one set of pairs that needs to be combined. INDIRECT only looks at the contents of the neighboring cells, not at the properties in the SW file. We do have multiple properties that are broken into pairs like our material-related properties.

Actually, I like Bradley's recommendation. I think I will toy with it and see what comes.

Good and evil: wrap them up and disguise it as people.

 

[StarrRider]

Bradley

I have to agree with TheTick – You managed to slipped your post in there while I was busy changing colors a dozen times and I missed it. – A really Good Trick – worthy of a Star

TheTick

Excuse me - Indirect does not combine anything – the “&” that you are using in your formula does the combining
Indirect returns a Cell’s Address from a text string – An address like B2 – All that the three statements do when combined like this is to make that address relative to the current cell

You don’t need to use Indirect to guarantee that your formulas stay valid - A simple formula like =B2&C2 is automatically converted to =B2&D2 when a column is inserted between them

In the second instance – I don’t understand what you are driving at. It is possible to write a macro and store it in Excel that can access the properties in the SW file – but a formula cannot do that. A formula can only access a cell or a range of cells. SW dumps the data into the spreadsheet and then Excel work on it.

I don’t mean to offend - but I don’t see how
=INDIRECT(INDEX(ADDRESS(ROW(),COLUMN()-2,4),1,1))&" "&INDIRECT(INDEX(ADDRESS(ROW(),COLUMN()-1,4),1,1))
is any better than =B4&” “&C4
To me at least – Because everything is relative in your formula – it is harder to understand. If you place your formula in Cell D4 and the formula =B4&” “&C4 in Cell E4 – They would both produce the same result but later formula makes fewer function calls and would execute faster

Lee

Consciousness: That annoying time between naps.

 

[TheTick]

Lee:
The "BOM_2COL" property method (forgive me if calling it the "INDIRECT" method was confusing) allows for a custom property carried within SW to be used to combine any two property strings with the use of another single property. This allows for the BOM to come in intact with zero editing by the user.

As Kerry Livgren would say, "One of many possible musicks."

I think this is one of the best threads I've seen in a long time. Many people contributed many useful items w.r.t. how Excel behaves with SW.

Good and evil: wrap them up and disguise it as people.

 

[StarrRider]

TheTic

What? I am missing something big time here. I checked the SW-API help file (SW 2004 Beta) for Bom_2Col and it came up empty.

Oh yes – Who is Kerry Livgren? I am terrible with names – they slide out of my head faster than water flows off a ducks back.

Lee

Consciousness: That annoying time between naps.

 

[TheTick]

Reread my posts. "BOM_2COL" is the name for the custom property to which the value =INDIRECT(INDEX(ADDRESS(ROW(),COLUMN()-2,4),1,1))&" "&INDIRECT(INDEX(ADDRESS(ROW(),COLUMN()-1,4),1,1)) is assigned.

I don't think SW is so enamored with my work that they would put it in their help files.

Who is Kerry Livgren?

http://www.numavox.com

http://www.kansasband.com

Good and evil: wrap them up and disguise it as people.

 

[StarrRider]

TheTick

Ahhhh – The Light of day

Lee

Consciousness: That annoying time between naps.

 

[Standing]

Lee,
Thank you so very much for your help. I have finally completed our automatically generated BOM. Our goal is not to manually text edit the excel BOM. I did rework the template like you suggested. We do not hide the Header in Row 1 anymore. That has helped us 10 fold. We have had problems putting in zero qty, at item zero. Not anymore, it works great. We are now checking for EA 1st. Once Documentation found out what could be done, they went wild with the requests. I do not agree with some of them. Here is the formula in qty column.
=IF(H2="EA",B2,IF(H2="FT",TEXT(I2/12,".0 FT&quot,IF(H2="NA",TEXT(,"0&quot,IF(H2="QTY",TEXT(J2,"0&quot,IF(H2="2PL",TEXT(J2,".00&quot,B2)))))
A synopsis of what we have now.
1. FT Feet - Wire, tubing, hose, tape and etc.
2. EA Each - Purchased as “each” e.g. nuts, fuses, computers and etc.
3. 0 Zero - The number zero for referenced items e.g. Do Not Use.
4. QTY Quantity - Put 1 part in Assembly, QTY can be 437 parts.
5. 2PL % - Percentage of Each e.g. Ducting.


Bradley