Good morning,

I am working on a formula that is giving me more trouble than I can
imagine and I would appreciate someone's help.

I have a spreadsheet that has information similar to this:

A B C D E
F
No. Question Y N N/A Recommendation
1 1.1 What is it? x
2 2.1 Who is it? x Figure out
who it is.
3 3.1 Where is it? x
4 4.1 When is it? x Figure out
when it is.

What I need is a function that will search each row and when it
encounters an 'X' in column D, I want to print the following fields on
a separate sheet: A, B, and F.

The Report would look like this:

A B C
No. Question Recommendation
1 2.1 Who is it? Figure out who it is.
2 4.1 When is it? Figure out when it is.

Can anyone shed some light on this? I have found other respondes on the
boards about doing a LOOKUP on rows and returning certain values, but I
haven't found anything that helps me with the above problem.

Any help would be appreciated!
~H

Hi HBuck,

VLOOKUP will certainly not work in this case as VLOOKUP is ment to
LOOKUP towards the right side of any range whereas in your case it is
LOOKUPing both right and left sides of your data range.
So in your case you need INDEX & MATCH functions as below

=INDEX($A$1:$F$5,MATCH("X",$D$1:$D$5,0),1)

The " 1 " at the end is the column number, change it according to the
need of your columns, say if you need the values from leftmost column
of your range then enter 1, if 2nd column from Left, then enter 2, so
on.

Hope this works for you.


thanks

Shail



HBuck wrote:
Good morning,

I am working on a formula that is giving me more trouble than I can
imagine and I would appreciate someone's help.

I have a spreadsheet that has information similar to this:

A B C D E
F
No. Question Y N N/A Recommendation
1 1.1 What is it? x
2 2.1 Who is it? x Figure out
who it is.
3 3.1 Where is it? x
4 4.1 When is it? x Figure out
when it is.

What I need is a function that will search each row and when it
encounters an 'X' in column D, I want to print the following fields on
a separate sheet: A, B, and F.

The Report would look like this:

A B C
No. Question Recommendation
1 2.1 Who is it? Figure out who it is.
2 4.1 When is it? Figure out when it is.

Can anyone shed some light on this? I have found other respondes on the
boards about doing a LOOKUP on rows and returning certain values, but I
haven't found anything that helps me with the above problem.

Any help would be appreciated!
~H

Thanks so much for your response, Shail. I tried the modified formula
and it worked for me -- on the first cell.

Here is what I modified it to:
=INDEX('1 of 20'!$A$11:'1 of 20'!$F$30,MATCH("X",'1 of 20'!$F$11:'1 of
20'!$F$30,0),1)

1 of 20 - worksheet with data
A11-F30 - range with data
F11-F30 - row with data that is being evaluated or Value X

For the first cell, it worked. But how do I get it to work in a report?
It only printed the first cell and wouldn't search any further. (C&Ping
gets me the same result as the first.)

Is there something that I am doing wrong in my modification of the
formula?

Holli



shail wrote:
Hi HBuck,

VLOOKUP will certainly not work in this case as VLOOKUP is ment to
LOOKUP towards the right side of any range whereas in your case it is
LOOKUPing both right and left sides of your data range.
So in your case you need INDEX & MATCH functions as below

=INDEX($A$1:$F$5,MATCH("X",$D$1:$D$5,0),1)

The " 1 " at the end is the column number, change it according to the
need of your columns, say if you need the values from leftmost column
of your range then enter 1, if 2nd column from Left, then enter 2, so
on.

Hope this works for you.


thanks

Shail



HBuck wrote:
Good morning,

I am working on a formula that is giving me more trouble than I can
imagine and I would appreciate someone's help.

I have a spreadsheet that has information similar to this:

A B C D E
F
No. Question Y N N/A Recommendation
1 1.1 What is it? x
2 2.1 Who is it? x Figure out
who it is.
3 3.1 Where is it? x
4 4.1 When is it? x Figure out
when it is.

What I need is a function that will search each row and when it
encounters an 'X' in column D, I want to print the following fields on
a separate sheet: A, B, and F.

The Report would look like this:

A B C
No. Question Recommendation
1 2.1 Who is it? Figure out who it is.
2 4.1 When is it? Figure out when it is.

Can anyone shed some light on this? I have found other respondes on the
boards about doing a LOOKUP on rows and returning certain values, but I
haven't found anything that helps me with the above problem.

Any help would be appreciated!
~H

Hi Holli
Sorry, it is more complex than I thought. Let me rephrase the formulae.
Give me a little more time. I will be back with the new formulae soon.

Sorry again,

Shail


HBuck wrote:
Thanks so much for your response, Shail. I tried the modified formula
and it worked for me -- on the first cell.

Here is what I modified it to:
=INDEX('1 of 20'!$A$11:'1 of 20'!$F$30,MATCH("X",'1 of 20'!$F$11:'1 of
20'!$F$30,0),1)

1 of 20 - worksheet with data
A11-F30 - range with data
F11-F30 - row with data that is being evaluated or Value X

For the first cell, it worked. But how do I get it to work in a report?
It only printed the first cell and wouldn't search any further. (C&Ping
gets me the same result as the first.)

Is there something that I am doing wrong in my modification of the
formula?

Holli



shail wrote:
Hi HBuck,

VLOOKUP will certainly not work in this case as VLOOKUP is ment to
LOOKUP towards the right side of any range whereas in your case it is
LOOKUPing both right and left sides of your data range.
So in your case you need INDEX & MATCH functions as below

=INDEX($A$1:$F$5,MATCH("X",$D$1:$D$5,0),1)

The " 1 " at the end is the column number, change it according to the
need of your columns, say if you need the values from leftmost column
of your range then enter 1, if 2nd column from Left, then enter 2, so
on.

Hope this works for you.


thanks

Shail



HBuck wrote:
Good morning,

I am working on a formula that is giving me more trouble than I can
imagine and I would appreciate someone's help.

I have a spreadsheet that has information similar to this:

A B C D E
F
No. Question Y N N/A Recommendation
1 1.1 What is it? x
2 2.1 Who is it? x Figure out
who it is.
3 3.1 Where is it? x
4 4.1 When is it? x Figure out
when it is.

What I need is a function that will search each row and when it
encounters an 'X' in column D, I want to print the following fields on
a separate sheet: A, B, and F.

The Report would look like this:

A B C
No. Question Recommendation
1 2.1 Who is it? Figure out who it is.
2 4.1 When is it? Figure out when it is.

Can anyone shed some light on this? I have found other respondes on the
boards about doing a LOOKUP on rows and returning certain values, but I
haven't found anything that helps me with the above problem.

Any help would be appreciated!
~H

Hi

Try marking your header row, then DataFilterAutofilter
Use the dropdown on column D and select "x"
Copy the filtered rows to Sheet2
On sheet 2, delete the unwanted columns C, D and E

--
Regards

Roger Govier


"HBuck" wrote in message
oups.com...
Good morning,

I am working on a formula that is giving me more trouble than I can
imagine and I would appreciate someone's help.

I have a spreadsheet that has information similar to this:

A B C D E
F
No. Question Y N N/A Recommendation
1 1.1 What is it? x
2 2.1 Who is it? x Figure out
who it is.
3 3.1 Where is it? x
4 4.1 When is it? x Figure out
when it is.

What I need is a function that will search each row and when it
encounters an 'X' in column D, I want to print the following fields on
a separate sheet: A, B, and F.

The Report would look like this:

A B C
No. Question Recommendation
1 2.1 Who is it? Figure out who it is.
2 4.1 When is it? Figure out when it is.

Can anyone shed some light on this? I have found other respondes on
the
boards about doing a LOOKUP on rows and returning certain values, but
I
haven't found anything that helps me with the above problem.

Any help would be appreciated!
~H

Thank you Roger and Shail.

In doing some research on my end, I discovered that there could be
issues when it comes to pasting arrays in Office 2000 (which I am
working on at the moment). Would that change the formula that I would
use?

~H

Hi

I didn't suggest using any formulae, arrays or otherwise.
Just apply the filter, then mark the range of visible rows and carry out
the Copy and Paste etc. as described.

--
Regards

Roger Govier


"HBuck" wrote in message
s.com...
Thank you Roger and Shail.

In doing some research on my end, I discovered that there could be
issues when it comes to pasting arrays in Office 2000 (which I am
working on at the moment). Would that change the formula that I would
use?

~H

Hello Roger,

Thanks for your help. The information about the formulas was more of a
response to Shail's solution. I need to have something automatically
created, that will update dynamically. I am working with a sheet that
has 21 'forms' where the information will be filled out by various
people, so I need to have a separate sheet with a report that
references certain fields within a range on all of the sheets. I'm
getting tired just thinking about it.

There may be the chance that what I am looking for can't be done. I
have to see.

I'm actually going to use your solution for a different problem that I
had, so thanks again!

Holli



Roger Govier wrote:
Hi

I didn't suggest using any formulae, arrays or otherwise.
Just apply the filter, then mark the range of visible rows and carry out
the Copy and Paste etc. as described.

--
Regards

Roger Govier


"HBuck" wrote in message
s.com...
Thank you Roger and Shail.

In doing some research on my end, I discovered that there could be
issues when it comes to pasting arrays in Office 2000 (which I am
working on at the moment). Would that change the formula that I would
use?

~H

Hi Holli,

Yes, Roger's answer was actually fit into your query. Thanks to Roger
for helping you out.

Thanks to you too,

Shail

HBuck wrote:
Hello Roger,

Thanks for your help. The information about the formulas was more of a
response to Shail's solution. I need to have something automatically
created, that will update dynamically. I am working with a sheet that
has 21 'forms' where the information will be filled out by various
people, so I need to have a separate sheet with a report that
references certain fields within a range on all of the sheets. I'm
getting tired just thinking about it.

There may be the chance that what I am looking for can't be done. I
have to see.

I'm actually going to use your solution for a different problem that I
had, so thanks again!

Holli



Roger Govier wrote:
Hi

I didn't suggest using any formulae, arrays or otherwise.
Just apply the filter, then mark the range of visible rows and carry out
the Copy and Paste etc. as described.

--
Regards

Roger Govier


"HBuck" wrote in message
s.com...
Thank you Roger and Shail.

In doing some research on my end, I discovered that there could be
issues when it comes to pasting arrays in Office 2000 (which I am
working on at the moment). Would that change the formula that I would
use?

~H

Hi Holli

We could probably achieve the same thing with Advanced Filter, to
extract the data automatically to a second sheet.
This could be combined with a macro, to carry out the extraction and
either Hide or Delete the unwanted columns.
If you want to mail me a complete sheet with all of the 21 sets of data
you talk about, I would be happy to take a look for you.
To mail direct, remove NOSPAM from my email address.

--
Regards

Roger Govier


"HBuck" wrote in message
oups.com...
Hello Roger,

Thanks for your help. The information about the formulas was more of a
response to Shail's solution. I need to have something automatically
created, that will update dynamically. I am working with a sheet that
has 21 'forms' where the information will be filled out by various
people, so I need to have a separate sheet with a report that
references certain fields within a range on all of the sheets. I'm
getting tired just thinking about it.

There may be the chance that what I am looking for can't be done. I
have to see.

I'm actually going to use your solution for a different problem that I
had, so thanks again!

Holli



Roger Govier wrote:
Hi

I didn't suggest using any formulae, arrays or otherwise.
Just apply the filter, then mark the range of visible rows and carry
out
the Copy and Paste etc. as described.

--
Regards

Roger Govier


"HBuck" wrote in message
s.com...
Thank you Roger and Shail.

In doing some research on my end, I discovered that there could be
issues when it comes to pasting arrays in Office 2000 (which I am
working on at the moment). Would that change the formula that I
would
use?

~H