Vlookup plus other function/s combinations required?

[Twishlist]

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 A199 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.

 

[T. Valko]

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?

 

[Twishlist]

E2 to BF2

What columns are these As in?


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?

 

[T. Valko]

Here's the basic formula using an abbreviated range. Enter this array
formula** in D2 and copy across to J2:

=INDEX('C:\TV\[Book 1.xls]Primary'!$E$1:$P$1,,SMALL(IF('C:\TV\[Book
1.xls]Primary'!$E$2:$P$2="a",COLUMN('C:\TV\[Book
1.xls]Primary'!$E$1:$P$1)-MIN(COLUMN('C:\TV\[Book
1.xls]Primary'!$E$1:$P$1))+1),COLUMNS($D22)))

Replace "my" path with your actual path. When there is less then 7 As the
balance of the cells will return a #NUM! error. We can build an error trap
into the formula to prevent those errors but that will make the formula
longer and more complex and since this formula is already fairly "long and
ugly" I would not use an error trap. Instead, I would use conditional
formatting to hide the errors. They'll still be there, you just won't see
them.

To hide the errors:
Select the range D2:J2
Goto the menu Format>Conditional Formatting
Formula Is: =ISERROR(D2)
Click the Format button
Select the Font tab
Set the font color to be the same as the cell fill color
OK out

 

[T. Valko]

Ooops! Forgot something:

Enter this array formula**

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP

Here's the basic formula using an abbreviated range. Enter this array
formula** in D2 and copy across to J2:

=INDEX('C:\TV\[Book 1.xls]Primary'!$E$1:$P$1,,SMALL(IF('C:\TV\[Book
1.xls]Primary'!$E$2:$P$2="a",COLUMN('C:\TV\[Book
1.xls]Primary'!$E$1:$P$1)-MIN(COLUMN('C:\TV\[Book
1.xls]Primary'!$E$1:$P$1))+1),COLUMNS($D22)))

Replace "my" path with your actual path. When there is less then 7 As the
balance of the cells will return a #NUM! error. We can build an error trap
into the formula to prevent those errors but that will make the formula
longer and more complex and since this formula is already fairly "long and
ugly" I would not use an error trap. Instead, I would use conditional
formatting to hide the errors. They'll still be there, you just won't see
them.

To hide the errors:
Select the range D2:J2
Goto the menu Format>Conditional Formatting
Formula Is: =ISERROR(D2)
Click the Format button
Select the Font tab
Set the font color to be the same as the cell fill color
OK out


--
Biff
Microsoft Excel MVP

E2 to BF2

 

[Twishlist]

I don't want you to think I don't appreciate your response. Beyond a gulp, I
haven't quite got my head around it yet. It's not working YET, but I'm sure
that's because of the path I've input or some such. I'll keep at it and will
post another entry if I can't work it through.

Ooops! Forgot something:

Enter this array formula**

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP

Here's the basic formula using an abbreviated range. Enter this array
formula** in D2 and copy across to J2:

=INDEX('C:\TV\[Book 1.xls]Primary'!$E$1:$P$1,,SMALL(IF('C:\TV\[Book
1.xls]Primary'!$E$2:$P$2="a",COLUMN('C:\TV\[Book
1.xls]Primary'!$E$1:$P$1)-MIN(COLUMN('C:\TV\[Book
1.xls]Primary'!$E$1:$P$1))+1),COLUMNS($D22)))

Replace "my" path with your actual path. When there is less then 7 As the
balance of the cells will return a #NUM! error. We can build an error trap
into the formula to prevent those errors but that will make the formula
longer and more complex and since this formula is already fairly "long and
ugly" I would not use an error trap. Instead, I would use conditional
formatting to hide the errors. They'll still be there, you just won't see
them.

To hide the errors:
Select the range D2:J2
Goto the menu Format>Conditional Formatting
Formula Is: =ISERROR(D2)
Click the Format button
Select the Font tab
Set the font color to be the same as the cell fill color
OK out


--
Biff
Microsoft Excel MVP

E2 to BF2

:

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


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 A199 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.