Formula Question

[teemcorp]

I am trying to create a database to be used in our order taking program. We have over 200 products that can be purchased in over 10 configurations. I would like to know if there is a way to combine these items without typing out all of the formulas? For instance I would have an item # 101 and I need to combine that with wp, wg, bp, bg, vb, vf, nr, & pr. I then need to do the same thing with items 102-290. I started doing it manually and it is very time consuming. Also if we should ad new configurations or items I would like a quick way to add these to inventory without typing 10 or 20 new items.

 

[martinee]

Try an array approach.

Book1
	A	B	C	D	E	F	G	H	I
1		wp	wg	bp	bg	vb	vf	nr	pr
2	101	101wp	101wg	101bp	101bg	101vb	101vf	101nr	101pr
3	102	102wp	102wg	102bp	102bg	102vb	102vf	102nr	102pr
4	103	103wp	103wg	103bp	103bg	103vb	103vf	103nr	103pr
5	104	104wp	104wg	104bp	104bg	104vb	104vf	104nr	104pr
6	105	105wp	105wg	105bp	105bg	105vb	105vf	105nr	105pr
7	106	106wp	106wg	106bp	106bg	106vb	106vf	106nr	106pr
8	107	107wp	107wg	107bp	107bg	107vb	107vf	107nr	107pr
9	108	108wp	108wg	108bp	108bg	108vb	108vf	108nr	108pr
Sheet1

Formula is: (In Cell B2)
=$A2 & B$1
This is then copied to the rest of the array. HTH.

 

[chiello]

Hi teemcorp,

try the following:

• - place wp, wg, bp, bg, vb, vf, nr in first row starting from B1

• - place #101,...,#290 in first column starting from A2

• - insert in B2 the formula =CONCATENATE($A2,B$1) and copy down-right

Does it work?

 

[teemcorp]

Thank you for your help. I now have 2 more questions... of course. Is there a quick way to copy down and right? The only way I know to do it is to ctrl-d & ctrl-r in each cell.

I will also have to take each of these item #s and place them in an excel sheet with each item being in column A, that is the only way I can import it into the order program. Any suggestions for that.

Thank you again for your help.

 

[martinee]

Select cell B1, and hit Ctrl-C, then highlight the entire array (cell B1 CAN be included in this, but NOT the header row/column). Then hit Ctrl-V.

Then select the entire array again, and copy it, then go to EDIT|PASTESPECIAL|VALUES to convert the formulas to a static value. Then you can copy them all into a single column.

HTH.

 

[OdinsDream]

If you're using a mac, put the formula in the cell in the upper left corner. Copy it, then select the area where you want to paste the formulas. Edit>Paste Special> Formulas.

If you're not using a mac, you can highlight the cell with the formula, then click and hold down the little black dot in the lower right corner of the cell. Drag to where you want your formulas extended, and let go.