What I'd like to do is find all instances of the letter A in row 2

What columns are these As in?

each Client has 3 columns of info, (eg
EFG, HIJ, etc). The client name in Row 1 is located in the centre cell of
each merged range (eg F1).

When you merge cells the top left-most cell is the one that contains the
data. If you merge E1, F1 and G1 then cell E1 is the cell that contains the
data. If you say the client name is centered and appears in cell F1 I'm
guessing that you used the Merge and Center format. So, cell E1 is still the
cell that contains the data.

So, if merged cell E1:G1 contains Client1, which column in row 2 contains
the A?



--
Biff
Microsoft Excel MVP


"Twishlist" wrote in message
...
HELP.Would welcome help from experience formula users. I'm attempting to
raise a list of orders required, dependent on data from primary worksheet.
Info follows:
(BOOK 1) Row 1 is headers - Col A: Site Ref, Col B: Site Id, Col C:
Project, Col D: Date, then a list of 50 client names, currently comprised
of
3 merged cells each. That's because each Client has 3 columns of info,
(eg
EFG, HIJ, etc). The client name in Row 1 is located in the centre cell of
each merged range (eg F1).
What I'd like to do is find all instances of the letter A in row 2 (there
could be between 1 and 7 instances) and return the name of the client into
columns D2:J2 in a separate sheet called Projects in a workbook called PO
Summary. Each result will go into the next consecutive column, that is,
the
next available in the range D2:J2 which doesn't already have a client name
in
it.
I've copied the data A1:D99 from the primary worksheet into the Projects
worksheet, so if the above is achievable, I'll run the formulas down the
page
and this will give me a list of client orders required for each project.