How do I create an automated label system that prints the correct background colour etc.

[rich2764]

I am creating labels using 4 fields for each label A1 to A4, B1 to B4 etc. A1 uses Vlookup from another part of the spreadsheet and with that enters the relevant text in that box. I do the same for A2 etc. I want all the cells A1 to A4 to be the same colour. The colour is dependent on the info in A1. I therefore use conditional formatting using the text search for that cell and then format with the approriate colour. What I want to happen is the cells A2, A3 and A4 to change to the same colour as what A1 is showing. A1 can be any colour depending on the data that A1 shows. How can I achieve this?

[liam_bettinson]

Do some research on VLOOKUP tables for relating the part code to the products and also conditional formatting for the tag/text colours, then design a workbook with various sheets using these types of formula. Plenty of info in these forums relating to these.

[rich2764]

Many thanks, however I am a complete novice at this and if possible could you give some examples of formulas?

[liam_bettinson]

Basically what you will need is a table, minimum of 2 columns, one column with part numbers listed and adjacent to this in a seperate column are the products these part numbers relate to. You formula would then search for the part number and return the adjacent product into your pre-designed label.

It is not 100% clear what you need to do from your original post and in order to give specific formulas, you need to give a lot more detail.

I too was a novice one day, but just play around with the logics I said above and you'll soon learn. Bit of headscratching involved, but you'll get there.

[rich2764]

ok, i'll try to make it clear what I want to do.
First, I will be given a list of parts which will be ordered.
E.g.
AE01 1103 Hot Roller x 1 (for AF 2022/2027)
B223 CDRU Drum x 2 (for MPC 2000/2500/3000)
D029 2252U Drum x 4 (for MPC 2800/3300)
AE02 0171 Pressure Roller x 3(for MPC 4000/5000)

I then want to be able to enter these numbers somewhere i.e. on a spreadsheet with the quantity etc.
This then automatically creates a different spreadsheet with the correct amount of stickers for each part with the correct colour.
I would then print the labels on a colour photocopier. The labels would be white and the copier would print the colour and text description onto it.
There are 24 labels per sheet 4 rows by 6 columns.
I would simply adjust the width of the cells so they would align with the label sheet.
So for my example,

----------- ------------ ------------ -------------
AF2022/27 MPC2000,2500, MPC2000,2500 MPC 2800
MPC3000 MPC3000
AE01 1103 B223 CDRU B223 CDRU D029 2252

Hot Roller DRUM DRUM DRUM

----------- ------------ ------------ -------------
------------- ------------ ------------ -------------
MPC 2800, MPC 2800 MPC 2800 MPC4000

D029 2252 D029 2252 D029 2252 AE02 0171

DRUM DRUM DRUM Pressure Roll
------------- ------------ ------------ --------------

etc


The AF 2022/2027 parts I want the stickers to be yellow
The MPC 2000/2500/3000 parts = Pink
The MPC 2800/3300 = Light Green
The MPC 4000/5000 = Light Blue.

---------- Post added at 12:54 AM ---------- Previous post was at 12:53 AM ----------

Sorry the layout for my stickers has changed when I posted!

ok, i'll try to make it clear what I want to do.
First, I will be given a list of parts which will be ordered.
E.g.
AE01 1103 Hot Roller x 1 (for AF 2022/2027)
B223 CDRU Drum x 2 (for MPC 2000/2500/3000)
D029 2252U Drum x 4 (for MPC 2800/3300)
AE02 0171 Pressure Roller x 3(for MPC 4000/5000)

I then want to be able to enter these numbers somewhere i.e. on a spreadsheet with the quantity etc.
This then automatically creates a different spreadsheet with the correct amount of stickers for each part with the correct colour.
I would then print the labels on a colour photocopier. The labels would be white and the copier would print the colour and text description onto it.
There are 24 labels per sheet 4 rows by 6 columns.
I would simply adjust the width of the cells so they would align with the label sheet.
So for my example,

----------- ------------ ------------ -------------
AF2022/27 MPC2000,2500, MPC2000,2500 MPC 2800
MPC3000 MPC3000
AE01 1103 B223 CDRU B223 CDRU D029 2252

Hot Roller DRUM DRUM DRUM

----------- ------------ ------------ -------------
------------- ------------ ------------ -------------
MPC 2800, MPC 2800 MPC 2800 MPC4000

D029 2252 D029 2252 D029 2252 AE02 0171

DRUM DRUM DRUM Pressure Roll
------------- ------------ ------------ --------------

etc


The AF 2022/2027 parts I want the stickers to be yellow
The MPC 2000/2500/3000 parts = Pink
The MPC 2800/3300 = Light Green
The MPC 4000/5000 = Light Blue.

[rich2764]

labels.JPG

This is a snip of what I want it to look like

[rich2764]

Hi, I now know how to do Vlookup and conditonal formatting however I am a little stuck.

I want to be able to create colour labels from part numbers with barcodes. Each label will be using 5 cells in a column, e.g. A1 to A5 in Sheet 3 etc. A1 will show machine model, A2 will be blank, A3 will show the part number, A4 will be the part description and A5 the barcode. At the moment I have managed to use Vlookup to the data which I have created in Sheet 2. A1 to A200 are the part numbers, B1 to B200 is the part description, C1 to C200 is the machine model and D1 to D200 are the barcodes. When I want to create my labels, I start by simply entering the part numbers in column A on sheet1. When I enter a part number in A1 on Sheet1, the part number shows in A3 on Sheet 3 which is correct as my labels are all going to be on sheet 3. I have then performed conditional formatting on A3 sheet 3 to recognise the text to change the background colour e.g. yellow. I am now stuck as I don't know how to make all the other cells in the label change colour and for the cells to show the part description and barcode etc. Does anyone know what formula i need to do this. Thanks.