Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects performance and then
make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file that
demonstrates this (unless Max beats me to it!<g) and post a link to the
file.

Biff

"Rikki-Handgards" wrote in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells from the
row you need returned and in general, how big is the table of data this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you need all
150 columns of data extracted for each instance of "value", then a
formula
approach is not practical.

Biff

"Rikki-Handgards" wrote in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of data
with a
value in a specific column? I want to list inventory items (and all
data
in
that row) as long as there is a value in column F. If there is no
value
in
column F for that inventory item, I don't want it to be the separate
summary
sheet. Is there a way to do this with a formula and not a pivot table?
Thanks!

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the cells. It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects performance and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file that
demonstrates this (unless Max beats me to it!<g) and post a link to the
file.

Biff

"Rikki-Handgards" wrote in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells from
the
row you need returned and in general, how big is the table of data this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you need
all
150 columns of data extracted for each instance of "value", then a
formula
approach is not practical.

Biff

"Rikki-Handgards" wrote in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of data
with a
value in a specific column? I want to list inventory items (and all
data
in
that row) as long as there is a value in column F. If there is no
value
in
column F for that inventory item, I don't want it to be the separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!

Thank you very much Biff, the formula works exactly like I needed it to.
However, I am having problems entering the array formula. I have the normal
formula in a cell and then I select the rows beneath it and hit F2 and press
Ctrl+Shift+Enter but the formulas don't copy correctly. I've tried it
several different ways, but I always get
=IF(ROWS('Sheet1'!$1:1)... in every row instead of
....('Sheet1'!$1:2),...('Sheet1'!$1:3), etc. If I copy the formula down the
column the formulas change like they are supposed to. Then, when I select
them all and enter it as an array formula, they all change back to
('Sheet1'!$1:1) again. Can you tell me what I am doing wrong? Thank you.
-Rikki

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the cells. It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects performance and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file that
demonstrates this (unless Max beats me to it!<g) and post a link to the
file.

Biff

"Rikki-Handgards" wrote in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells from
the
row you need returned and in general, how big is the table of data this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you need
all
150 columns of data extracted for each instance of "value", then a
formula
approach is not practical.

Biff

"Rikki-Handgards" wrote in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of data
with a
value in a specific column? I want to list inventory items (and all
data
in
that row) as long as there is a value in column F. If there is no
value
in
column F for that inventory item, I don't want it to be the separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!

Hi!

Just enter the formula in one cell, the top left cell of the "grid", and
enter it as an array using the key combo of CTRL,SHIFT,ENTER. With the
formula typed into that one top left cell move the cursor (mouse) to the end
of the formula in the formula bar. Then enter it as an array.

Then, just drag copy across the row to the right. Then drag copy down the
columns as needed.

Each cell formula is an array. You don't want to make the entire grid a
single block array.

Biff

"Rikki-Handgards" wrote in
message ...
Thank you very much Biff, the formula works exactly like I needed it to.
However, I am having problems entering the array formula. I have the
normal
formula in a cell and then I select the rows beneath it and hit F2 and
press
Ctrl+Shift+Enter but the formulas don't copy correctly. I've tried it
several different ways, but I always get
=IF(ROWS('Sheet1'!$1:1)... in every row instead of
...('Sheet1'!$1:2),...('Sheet1'!$1:3), etc. If I copy the formula down
the
column the formulas change like they are supposed to. Then, when I select
them all and enter it as an array formula, they all change back to
('Sheet1'!$1:1) again. Can you tell me what I am doing wrong? Thank you.
-Rikki

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the cells.
It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet
the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects performance and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file that
demonstrates this (unless Max beats me to it!<g) and post a link to
the
file.

Biff

"Rikki-Handgards" wrote in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells from
the
row you need returned and in general, how big is the table of data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you need
all
150 columns of data extracted for each instance of "value", then a
formula
approach is not practical.

Biff

"Rikki-Handgards" wrote
in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of data
with a
value in a specific column? I want to list inventory items (and
all
data
in
that row) as long as there is a value in column F. If there is no
value
in
column F for that inventory item, I don't want it to be the
separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!

Hello. Thanks so much, it worked! of course :) I appreciate your time Biff.

"Biff" wrote:

Hi!

Just enter the formula in one cell, the top left cell of the "grid", and
enter it as an array using the key combo of CTRL,SHIFT,ENTER. With the
formula typed into that one top left cell move the cursor (mouse) to the end
of the formula in the formula bar. Then enter it as an array.

Then, just drag copy across the row to the right. Then drag copy down the
columns as needed.

Each cell formula is an array. You don't want to make the entire grid a
single block array.

Biff

"Rikki-Handgards" wrote in
message ...
Thank you very much Biff, the formula works exactly like I needed it to.
However, I am having problems entering the array formula. I have the
normal
formula in a cell and then I select the rows beneath it and hit F2 and
press
Ctrl+Shift+Enter but the formulas don't copy correctly. I've tried it
several different ways, but I always get
=IF(ROWS('Sheet1'!$1:1)... in every row instead of
...('Sheet1'!$1:2),...('Sheet1'!$1:3), etc. If I copy the formula down
the
column the formulas change like they are supposed to. Then, when I select
them all and enter it as an array formula, they all change back to
('Sheet1'!$1:1) again. Can you tell me what I am doing wrong? Thank you.
-Rikki

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the cells.
It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet
the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects performance and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file that
demonstrates this (unless Max beats me to it!<g) and post a link to
the
file.

Biff

"Rikki-Handgards" wrote in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells from
the
row you need returned and in general, how big is the table of data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you need
all
150 columns of data extracted for each instance of "value", then a
formula
approach is not practical.

Biff

"Rikki-Handgards" wrote
in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of data
with a
value in a specific column? I want to list inventory items (and
all
data
in
that row) as long as there is a value in column F. If there is no
value
in
column F for that inventory item, I don't want it to be the
separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!

Hi,

I am trying to do something similar. I want to call values from another
worksheet but ignore the values that are "n/a". The link you posted does not
have the formula anymore.

Thanks for your time, UT

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the cells. It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects performance and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file that
demonstrates this (unless Max beats me to it!<g) and post a link to the
file.

Biff

"Rikki-Handgards" wrote in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells from
the
row you need returned and in general, how big is the table of data this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you need
all
150 columns of data extracted for each instance of "value", then a
formula
approach is not practical.

Biff

"Rikki-Handgards" wrote in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of data
with a
value in a specific column? I want to list inventory items (and all
data
in
that row) as long as there is a value in column F. If there is no
value
in
column F for that inventory item, I don't want it to be the separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!

Can you provide some more specific details?

Is "n/a" a TEXT entry or is it the Excel "error" value #N/A ?

Biff

"UT" wrote in message
...
Hi,

I am trying to do something similar. I want to call values from another
worksheet but ignore the values that are "n/a". The link you posted does
not
have the formula anymore.

Thanks for your time, UT

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the cells.
It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet
the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects performance and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file that
demonstrates this (unless Max beats me to it!<g) and post a link to
the
file.

Biff

"Rikki-Handgards" wrote in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells from
the
row you need returned and in general, how big is the table of data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you need
all
150 columns of data extracted for each instance of "value", then a
formula
approach is not practical.

Biff

"Rikki-Handgards" wrote
in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of data
with a
value in a specific column? I want to list inventory items (and
all
data
in
that row) as long as there is a value in column F. If there is no
value
in
column F for that inventory item, I don't want it to be the
separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!

Hi,

The "n/a" values are valid text entry. However in another worksheet I want
to ignore the "n/a" text and only link to other cell values in that column. I
have read about the formatting the not required values in white font in the
conditional formatting. But that does not serve my purpose. I only want to
link to the text values other than "n/a". Is there a way to do this? Thanks.

"T. Valko" wrote:

Can you provide some more specific details?

Is "n/a" a TEXT entry or is it the Excel "error" value #N/A ?

Biff

"UT" wrote in message
...
Hi,

I am trying to do something similar. I want to call values from another
worksheet but ignore the values that are "n/a". The link you posted does
not
have the formula anymore.

Thanks for your time, UT

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the cells.
It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet
the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects performance and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file that
demonstrates this (unless Max beats me to it!<g) and post a link to
the
file.

Biff

"Rikki-Handgards" wrote in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells from
the
row you need returned and in general, how big is the table of data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you need
all
150 columns of data extracted for each instance of "value", then a
formula
approach is not practical.

Biff

"Rikki-Handgards" wrote
in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of data
with a
value in a specific column? I want to list inventory items (and
all
data
in
that row) as long as there is a value in column F. If there is no
value
in
column F for that inventory item, I don't want it to be the
separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!

Let's assume you have this data in A1:A5 -

n/a
blue
n/a
n/a
red

You want to extract all entries that are not n/a to another location.

If there are no empty/blank cells as above try this array** formula:

=IF(ROWS($1:1)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"")

Copy down until you get blanks.

If there might be empty/blank cells try this array** formula:

=IF(ROWS($1:1)<=SUMPRODUCT(--(rng<""),--(rng<"n/a")),INDEX(rng,SMALL(IF((rng<"n/a")*(rng<""),ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"")

Copy down until you get blanks.

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

Biff

"UT" wrote in message
...
Hi,

The "n/a" values are valid text entry. However in another worksheet I want
to ignore the "n/a" text and only link to other cell values in that
column. I
have read about the formatting the not required values in white font in
the
conditional formatting. But that does not serve my purpose. I only want to
link to the text values other than "n/a". Is there a way to do this?
Thanks.

"T. Valko" wrote:

Can you provide some more specific details?

Is "n/a" a TEXT entry or is it the Excel "error" value #N/A ?

Biff

"UT" wrote in message
...
Hi,

I am trying to do something similar. I want to call values from another
worksheet but ignore the values that are "n/a". The link you posted
does
not
have the formula anymore.

Thanks for your time, UT

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the
cells.
It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet
the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In
the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects performance
and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file
that
demonstrates this (unless Max beats me to it!<g) and post a link to
the
file.

Biff

"Rikki-Handgards" wrote
in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells
from
the
row you need returned and in general, how big is the table of data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you
need
all
150 columns of data extracted for each instance of "value", then
a
formula
approach is not practical.

Biff

"Rikki-Handgards"
wrote
in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of
data
with a
value in a specific column? I want to list inventory items (and
all
data
in
that row) as long as there is a value in column F. If there is
no
value
in
column F for that inventory item, I don't want it to be the
separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!

Hi Biff:

I tried the following formula but it didn't work. It give "name" error. What
am I doing wrong? Thanks

=IF(ROWS(GISData!H$8:H$500)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS(GISData!H$8:H$500))),"")

"T. Valko" wrote:

Let's assume you have this data in A1:A5 -

n/a
blue
n/a
n/a
red

You want to extract all entries that are not n/a to another location.

If there are no empty/blank cells as above try this array** formula:

=IF(ROWS($1:1)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"")

Copy down until you get blanks.

If there might be empty/blank cells try this array** formula:

=IF(ROWS($1:1)<=SUMPRODUCT(--(rng<""),--(rng<"n/a")),INDEX(rng,SMALL(IF((rng<"n/a")*(rng<""),ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"")

Copy down until you get blanks.

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

Biff

"UT" wrote in message
...
Hi,

The "n/a" values are valid text entry. However in another worksheet I want
to ignore the "n/a" text and only link to other cell values in that
column. I
have read about the formatting the not required values in white font in
the
conditional formatting. But that does not serve my purpose. I only want to
link to the text values other than "n/a". Is there a way to do this?
Thanks.

"T. Valko" wrote:

Can you provide some more specific details?

Is "n/a" a TEXT entry or is it the Excel "error" value #N/A ?

Biff

"UT" wrote in message
...
Hi,

I am trying to do something similar. I want to call values from another
worksheet but ignore the values that are "n/a". The link you posted
does
not
have the formula anymore.

Thanks for your time, UT

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the
cells.
It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet
the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In
the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects performance
and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file
that
demonstrates this (unless Max beats me to it!<g) and post a link to
the
file.

Biff

"Rikki-Handgards" wrote
in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells
from
the
row you need returned and in general, how big is the table of data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you
need
all
150 columns of data extracted for each instance of "value", then
a
formula
approach is not practical.

Biff

"Rikki-Handgards"
wrote
in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of
data
with a
value in a specific column? I want to list inventory items (and
all
data
in
that row) as long as there is a value in column F. If there is
no
value
in
column F for that inventory item, I don't want it to be the
separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!