Good morning

I have been given a spreadsheet and, while I can see what the formula does, I don't understand how it is working. Can somebody please explain, step by step?

=(IF(C139=0,,C139&"
")&IF(J139=0,,J139&"
")&IF(K139=0,,K139&"
")&IF(L139=0,,IF(L139=M139,,L139&"
"))&IF(M139=0,,M139&"
")&IF(N139=0,,N139))

C = Name of store
J = Address1
K = Address2
L = Town
M = County
N = Post code

Many thanks

It might help others to help you if you post the spreadsheet so that people can see the layout and understand what you are referring to.

Here is a line from the spreadsheet

Attached Files

Book2.xlsx (8.9 KB, 11 views)

Seems gibberish to me. If 139 refers to a row number then that row is not populated so errors should be flagged up. Also the six strings are all in separate cells and don't look connected.

Maybe some expert will help you. Good luck.

Oh dear! The final formula is in one cell but I couldn't work out how to copy and paste it. This cell is linked to a mail merge in order to process an invoice. I think it is saying if a cell is blank, don't print anything on that address line (no zeros or blanks). For example, if there is no Address2, print the contents of the next cell (Town).

As you data are really in row 2 in your example table, I replaced all references to row 139 with references to row 2;

As obviously you don't want to send a mail to non-existing store, I added a condition, that store name must exist to address string to be composed at all. It would be a real pain to check for every step in formula the previous part of string to be not empty;

In your attempt, in case it would work, you'd get a string like "Disco Drug StoreMeadow LaneBrightonSussexME2 6RL" - looks like a gibberish to me! To avoid this, I added delimiters (a comma followed with space) between all non-empty components of result string. You can change those delimiters in any way you want. The example where delimiter for Postal Code is changed to ", Postal Code ", and the code is between apostrophes, is presented with formula in Q3.

Attached Files

FormulaExample.xlsx (9.6 KB, 9 views)

I am afraid I haven't been able to explain myself well enough. The spreadsheet is used to produce invoices. The invoices have two boxes - the first contains the invoicing name and address(the owner or a managing company). The second contains the store location.

The "Invoice To" box picks up the details from the cell containing the formula I sent. In cases where there is a management company or owner, I overtype the formula with the different name & address. The "Location" box picks up the details from the individual cells C to N.

It works - my invoices are correct. My question was why does it work? I don't understand how to read the formula and wondered if someone could explain the steps to me.

Never mind - as I said, it works so I'll just go with it.