Function to skil blank cells/lookup specific value

[HBuck]

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

 

[shail]

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]

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]

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

 

[Roger Govier]

Hi

Try marking your header row, then Data>Filter>Autofilter
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

 

[HBuck]

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

 

[Roger Govier]

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.

 

[HBuck]

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

 

[shail]

Hi Holli,

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

Thanks to you too,

Shail

 

[Roger Govier]

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.