Excel

Verb

Hi
I hope someone can help me, I will try and explain best I can as I can visual what 'm after its just putting it into words.


In Excel
I have tab 1 which is a list of information. Contains 10 cells of data on each line, and each line is specific to only that line. There is about 150 lines of data so far and growing.


I want to take the data on each line and turn it into a label. Of the 10 cells with information some will be more dominant than others (some will be bigger occupying more space on the sheet)


How can I tell the sheet which line to take the data from so I can print the label.


I hope I have explained this well enough


Are we taking macros here??

cookie365

Use mailmerge in Word. I assume Libreoffice and similar free equivalents also have mailmerge.

Raggie

Not a 100% on what exactly you need, but mail merge comes to mind.

https://support.office.com/en-gb/article/Create-and-print-mailing-labels-for-an-address-list-in-Excel-d9ae0b60-9fd0-4a90-9da9-0ec3a4b011b2

You can of course take the output to the printer or "print" to a file

Verb

It's to make pallet labels I just want to pull the data through to make them

I_have_spoken

Of the 10 cells with information some will be more dominant than others (some will be bigger occupying more space on the sheet)

Not sure I 100% get what you want, but assume Row A has 4 entries in cells A1 to D1 of

tiny big enormous bigger

then the formula -

=INDEX(A1:D1,0,MATCH(MAX(LEN(A1:D1)),LEN(A1:D1),0))

will return 'enormous' as the longest string in cells A1 to D1. The formula will always return the longest string in the 4 cells

NB you need to use CTRL-SHIFT-ENTER to finish the formula as it's an array entry, hence the {} appearing in formula bar

System

Hi

My suggestion is to create the Mail Merge as described above, from all 150 addresses.

Then choose to print only pages 7 and 123 if you just want those 2 pallets labelled. Discard the file.

Ainsley1

How can I tell the sheet which line to take the data from so I can print the label.

I agree that mail merge might be an easy solution but does not really answer the specific question (which might be redundant now?)

It is easy for us readers to presume your application of the data but as you mention pallet labels this sounds like commercial work? Perhaps, if that is the case you might best consider a bit more and things such as barcoding and specific programmes?

I guess that (and do clarify if wrong) that you wish to select one particular row /line of information and use the cells on that row to print a sheet of paper or sheet that has a few labels on it. Mail merge will collate the information together so you can format the whole collection to allow for different fonts/size/formatting thus you can make specific bits stand out or include barcodes, item identifiers, order numbers etc.

To automate things (select a specific line from the worksheet tab) requires a bit of setting up or, as suggested can be done manually -ok for a small number but if you run into the thousands........

It is quite possible to setup Excel to do easy selection of data either matching data as you type, selecting from a drop down list or finding from data in another cell........then returning the results to the sheet to be printed.

Key to some of this is holding on each row of information a unique identifier (that need not be printed) such as an increasing number.

First question I would ask : how many lines/rows/records do you envisage?
If a smallish number then getting going with mail merge will probably be quickest;
If a few thousand then Excel might be a good choice purely on the ease to select the one you want from the database of records (for your case a record is a row of cells with information)

If however you extend to several thousand records upwards in the long term this is possibly better handled by a program designed for the task such as an Access database or labelling programming/add on.

All these programmes have tools to make some of the work easier such as spinners to make list selection easier, for drop down menus or filters and sorting.

If you are linking to order numbers or barcodes already provided then barcode linking/scanning would seem a possibility to reduce routine work- but perhaps a little more involved initially setting up.

Post more if you need people to advise further on any of the above (posts and content), many on here will help with more information.

Ainsley1

Oh and you might be talking macros but there are many ready made one for tasks like this (add.ons) but that question really cannot be answered until the scale of the final database is known. It might be best initially, if it is small especially or cost is a big consideration to do without, just use free ones rather than sold solutions or non at all if it is going to be a simple job.