software

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!

gt;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 quot;valuequot;, then a formula
approach is not practical.

Biff

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

quot;Biffquot; wrote:

gt; gt;Is there a way to do this with a formula and not a pivot table?
gt;
gt; Yes, but whether or not it's practical depends on how many cells from the
gt; row you need returned and in general, how big is the table of data this data
gt; is being extracted from.
gt;
gt; For example, if your table is 50,000 rows by 150 columns and you need all
gt; 150 columns of data extracted for each instance of quot;valuequot;, then a formula
gt; approach is not practical.
gt;
gt; Biff
gt;
gt; quot;Rikki-Handgardsquot; gt; wrote in
gt; message news
gt; gt; Hi. Is there a way to list, on a separate sheet, only rows of data with a
gt; gt; value in a specific column? I want to list inventory items (and all data
gt; gt; in
gt; gt; that row) as long as there is a value in column F. If there is no value
gt; gt; in
gt; gt; column F for that inventory item, I don't want it to be the separate
gt; gt; summary
gt; gt; sheet. Is there a way to do this with a formula and not a pivot table?
gt; gt; Thanks!
gt;
gt;
gt;

gt;Would it still be practical if I had 250 rows of data total and
gt;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!lt;ggt;) and post a link to the
file.

Biff

quot;Rikki-Handgardsquot; gt; wrote in
message ...
gt; Thanks Biff. Would it still be practical if I had 250 rows of data total
gt; and
gt; needed to pull in 10 columns for only about 20-40 rows?
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; gt;Is there a way to do this with a formula and not a pivot table?
gt;gt;
gt;gt; Yes, but whether or not it's practical depends on how many cells from the
gt;gt; row you need returned and in general, how big is the table of data this
gt;gt; data
gt;gt; is being extracted from.
gt;gt;
gt;gt; For example, if your table is 50,000 rows by 150 columns and you need all
gt;gt; 150 columns of data extracted for each instance of quot;valuequot;, then a
gt;gt; formula
gt;gt; approach is not practical.
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;Rikki-Handgardsquot; gt; wrote in
gt;gt; message news
gt;gt; gt; Hi. Is there a way to list, on a separate sheet, only rows of data
gt;gt; gt; with a
gt;gt; gt; value in a specific column? I want to list inventory items (and all
gt;gt; gt; data
gt;gt; gt; in
gt;gt; gt; that row) as long as there is a value in column F. If there is no
gt;gt; gt; value
gt;gt; gt; in
gt;gt; gt; column F for that inventory item, I don't want it to be the separate
gt;gt; gt; summary
gt;gt; gt; sheet. Is there a way to do this with a formula and not a pivot table?
gt;gt; gt; Thanks!
gt;gt;
gt;gt;
gt;gt;
Here's what I would do.

I would copy my 10 columns then paste it into the other worksheet (paste as
link) then use a filter.....filter by values greater than 0 in column F.

HTH
Jean-Guy

quot;Rikki-Handgardsquot; wrote:

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

Here's a link to a sample file:

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

quot;Biffquot; gt; wrote in message
...
gt; gt;Would it still be practical if I had 250 rows of data total and
gt;gt;needed to pull in 10 columns for only about 20-40 rows?
gt;
gt; That should be Ok but you'll have to see how it affects performance and
gt; then make that determination for yourself.
gt;
gt; I'm going to break for dinner but I'll put together a sample file that
gt; demonstrates this (unless Max beats me to it!lt;ggt;) and post a link to the
gt; file.
gt;
gt; Biff
gt;
gt; quot;Rikki-Handgardsquot; gt; wrote in
gt; message ...
gt;gt; Thanks Biff. Would it still be practical if I had 250 rows of data total
gt;gt; and
gt;gt; needed to pull in 10 columns for only about 20-40 rows?
gt;gt;
gt;gt; quot;Biffquot; wrote:
gt;gt;
gt;gt;gt; gt;Is there a way to do this with a formula and not a pivot table?
gt;gt;gt;
gt;gt;gt; Yes, but whether or not it's practical depends on how many cells from
gt;gt;gt; the
gt;gt;gt; row you need returned and in general, how big is the table of data this
gt;gt;gt; data
gt;gt;gt; is being extracted from.
gt;gt;gt;
gt;gt;gt; For example, if your table is 50,000 rows by 150 columns and you need
gt;gt;gt; all
gt;gt;gt; 150 columns of data extracted for each instance of quot;valuequot;, then a
gt;gt;gt; formula
gt;gt;gt; approach is not practical.
gt;gt;gt;
gt;gt;gt; Biff
gt;gt;gt;
gt;gt;gt; quot;Rikki-Handgardsquot; gt; wrote in
gt;gt;gt; message news
gt;gt;gt; gt; Hi. Is there a way to list, on a separate sheet, only rows of data
gt;gt;gt; gt; with a
gt;gt;gt; gt; value in a specific column? I want to list inventory items (and all
gt;gt;gt; gt; data
gt;gt;gt; gt; in
gt;gt;gt; gt; that row) as long as there is a value in column F. If there is no
gt;gt;gt; gt; value
gt;gt;gt; gt; in
gt;gt;gt; gt; column F for that inventory item, I don't want it to be the separate
gt;gt;gt; gt; summary
gt;gt;gt; gt; sheet. Is there a way to do this with a formula and not a pivot
gt;gt;gt; gt; table?
gt;gt;gt; gt; Thanks!
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;
gt;
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

quot;Biffquot; wrote:

gt; Here's a link to a sample file:
gt;
gt; s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU
gt;
gt; There are a few different formula methods that could be used. This
gt; demonstrates the method I prefer. Just one formula copied to the cells. It's
gt; an array formula, though, and array formulas take longer to calculate.
gt; Depending on the size of your file and other factors, you might not notice
gt; any difference.
gt;
gt; Try deleting some data in column F then look at sheet 2 to see how it
gt; updates.
gt;
gt; If in your situation you expect that there may be 20-40 rows that meet the
gt; criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the
gt; sample I copied the formula to 10 rows by 10 columns.
gt;
gt; Biff
gt;
gt; quot;Biffquot; gt; wrote in message
gt; ...
gt; gt; gt;Would it still be practical if I had 250 rows of data total and
gt; gt;gt;needed to pull in 10 columns for only about 20-40 rows?
gt; gt;
gt; gt; That should be Ok but you'll have to see how it affects performance and
gt; gt; then make that determination for yourself.
gt; gt;
gt; gt; I'm going to break for dinner but I'll put together a sample file that
gt; gt; demonstrates this (unless Max beats me to it!lt;ggt;) and post a link to the
gt; gt; file.
gt; gt;
gt; gt; Biff
gt; gt;
gt; gt; quot;Rikki-Handgardsquot; gt; wrote in
gt; gt; message ...
gt; gt;gt; Thanks Biff. Would it still be practical if I had 250 rows of data total
gt; gt;gt; and
gt; gt;gt; needed to pull in 10 columns for only about 20-40 rows?
gt; gt;gt;
gt; gt;gt; quot;Biffquot; wrote:
gt; gt;gt;
gt; gt;gt;gt; gt;Is there a way to do this with a formula and not a pivot table?
gt; gt;gt;gt;
gt; gt;gt;gt; Yes, but whether or not it's practical depends on how many cells from
gt; gt;gt;gt; the
gt; gt;gt;gt; row you need returned and in general, how big is the table of data this
gt; gt;gt;gt; data
gt; gt;gt;gt; is being extracted from.
gt; gt;gt;gt;
gt; gt;gt;gt; For example, if your table is 50,000 rows by 150 columns and you need
gt; gt;gt;gt; all
gt; gt;gt;gt; 150 columns of data extracted for each instance of quot;valuequot;, then a
gt; gt;gt;gt; formula
gt; gt;gt;gt; approach is not practical.
gt; gt;gt;gt;
gt; gt;gt;gt; Biff
gt; gt;gt;gt;
gt; gt;gt;gt; quot;Rikki-Handgardsquot; gt; wrote in
gt; gt;gt;gt; message news
gt; gt;gt;gt; gt; Hi. Is there a way to list, on a separate sheet, only rows of data
gt; gt;gt;gt; gt; with a
gt; gt;gt;gt; gt; value in a specific column? I want to list inventory items (and all
gt; gt;gt;gt; gt; data
gt; gt;gt;gt; gt; in
gt; gt;gt;gt; gt; that row) as long as there is a value in column F. If there is no
gt; gt;gt;gt; gt; value
gt; gt;gt;gt; gt; in
gt; gt;gt;gt; gt; column F for that inventory item, I don't want it to be the separate
gt; gt;gt;gt; gt; summary
gt; gt;gt;gt; gt; sheet. Is there a way to do this with a formula and not a pivot
gt; gt;gt;gt; gt; table?
gt; gt;gt;gt; gt; Thanks!
gt; gt;gt;gt;
gt; gt;gt;gt;
gt; gt;gt;gt;
gt; gt;
gt; gt;
gt;
gt;
gt;

Hi!

Just enter the formula in one cell, the top left cell of the quot;gridquot;, 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

quot;Rikki-Handgardsquot; gt; wrote in
message ...
gt; Thank you very much Biff, the formula works exactly like I needed it to.
gt; However, I am having problems entering the array formula. I have the
gt; normal
gt; formula in a cell and then I select the rows beneath it and hit F2 and
gt; press
gt; Ctrl Shift Enter but the formulas don't copy correctly. I've tried it
gt; several different ways, but I always get
gt; =IF(ROWS('Sheet1'!$1:1)... in every row instead of
gt; ...('Sheet1'!$1:2),...('Sheet1'!$1:3), etc. If I copy the formula down
gt; the
gt; column the formulas change like they are supposed to. Then, when I select
gt; them all and enter it as an array formula, they all change back to
gt; ('Sheet1'!$1:1) again. Can you tell me what I am doing wrong? Thank you.
gt; -Rikki
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; Here's a link to a sample file:
gt;gt;
gt;gt; s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU
gt;gt;
gt;gt; There are a few different formula methods that could be used. This
gt;gt; demonstrates the method I prefer. Just one formula copied to the cells.
gt;gt; It's
gt;gt; an array formula, though, and array formulas take longer to calculate.
gt;gt; Depending on the size of your file and other factors, you might not
gt;gt; notice
gt;gt; any difference.
gt;gt;
gt;gt; Try deleting some data in column F then look at sheet 2 to see how it
gt;gt; updates.
gt;gt;
gt;gt; If in your situation you expect that there may be 20-40 rows that meet
gt;gt; the
gt;gt; criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the
gt;gt; sample I copied the formula to 10 rows by 10 columns.
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;Biffquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; gt;Would it still be practical if I had 250 rows of data total and
gt;gt; gt;gt;needed to pull in 10 columns for only about 20-40 rows?
gt;gt; gt;
gt;gt; gt; That should be Ok but you'll have to see how it affects performance and
gt;gt; gt; then make that determination for yourself.
gt;gt; gt;
gt;gt; gt; I'm going to break for dinner but I'll put together a sample file that
gt;gt; gt; demonstrates this (unless Max beats me to it!lt;ggt;) and post a link to
gt;gt; gt; the
gt;gt; gt; file.
gt;gt; gt;
gt;gt; gt; Biff
gt;gt; gt;
gt;gt; gt; quot;Rikki-Handgardsquot; gt; wrote in
gt;gt; gt; message ...
gt;gt; gt;gt; Thanks Biff. Would it still be practical if I had 250 rows of data
gt;gt; gt;gt; total
gt;gt; gt;gt; and
gt;gt; gt;gt; needed to pull in 10 columns for only about 20-40 rows?
gt;gt; gt;gt;
gt;gt; gt;gt; quot;Biffquot; wrote:
gt;gt; gt;gt;
gt;gt; gt;gt;gt; gt;Is there a way to do this with a formula and not a pivot table?
gt;gt; gt;gt;gt;
gt;gt; gt;gt;gt; Yes, but whether or not it's practical depends on how many cells from
gt;gt; gt;gt;gt; the
gt;gt; gt;gt;gt; row you need returned and in general, how big is the table of data
gt;gt; gt;gt;gt; this
gt;gt; gt;gt;gt; data
gt;gt; gt;gt;gt; is being extracted from.
gt;gt; gt;gt;gt;
gt;gt; gt;gt;gt; For example, if your table is 50,000 rows by 150 columns and you need
gt;gt; gt;gt;gt; all
gt;gt; gt;gt;gt; 150 columns of data extracted for each instance of quot;valuequot;, then a
gt;gt; gt;gt;gt; formula
gt;gt; gt;gt;gt; approach is not practical.
gt;gt; gt;gt;gt;
gt;gt; gt;gt;gt; Biff
gt;gt; gt;gt;gt;
gt;gt; gt;gt;gt; quot;Rikki-Handgardsquot; gt; wrote
gt;gt; gt;gt;gt; in
gt;gt; gt;gt;gt; message news
gt;gt; gt;gt;gt; gt; Hi. Is there a way to list, on a separate sheet, only rows of data
gt;gt; gt;gt;gt; gt; with a
gt;gt; gt;gt;gt; gt; value in a specific column? I want to list inventory items (and
gt;gt; gt;gt;gt; gt; all
gt;gt; gt;gt;gt; gt; data
gt;gt; gt;gt;gt; gt; in
gt;gt; gt;gt;gt; gt; that row) as long as there is a value in column F. If there is no
gt;gt; gt;gt;gt; gt; value
gt;gt; gt;gt;gt; gt; in
gt;gt; gt;gt;gt; gt; column F for that inventory item, I don't want it to be the
gt;gt; gt;gt;gt; gt; separate
gt;gt; gt;gt;gt; gt; summary
gt;gt; gt;gt;gt; gt; sheet. Is there a way to do this with a formula and not a pivot
gt;gt; gt;gt;gt; gt; table?
gt;gt; gt;gt;gt; gt; Thanks!
gt;gt; gt;gt;gt;
gt;gt; gt;gt;gt;
gt;gt; gt;gt;gt;
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;
Hello. Thanks so much, it worked! of course I appreciate your time Biff.

quot;Biffquot; wrote:

gt; Hi!
gt;
gt; Just enter the formula in one cell, the top left cell of the quot;gridquot;, and
gt; enter it as an array using the key combo of CTRL,SHIFT,ENTER. With the
gt; formula typed into that one top left cell move the cursor (mouse) to the end
gt; of the formula in the formula bar. Then enter it as an array.
gt;
gt; Then, just drag copy across the row to the right. Then drag copy down the
gt; columns as needed.
gt;
gt; Each cell formula is an array. You don't want to make the entire grid a
gt; single block array.
gt;
gt; Biff
gt;
gt; quot;Rikki-Handgardsquot; gt; wrote in
gt; message ...
gt; gt; Thank you very much Biff, the formula works exactly like I needed it to.
gt; gt; However, I am having problems entering the array formula. I have the
gt; gt; normal
gt; gt; formula in a cell and then I select the rows beneath it and hit F2 and
gt; gt; press
gt; gt; Ctrl Shift Enter but the formulas don't copy correctly. I've tried it
gt; gt; several different ways, but I always get
gt; gt; =IF(ROWS('Sheet1'!$1:1)... in every row instead of
gt; gt; ...('Sheet1'!$1:2),...('Sheet1'!$1:3), etc. If I copy the formula down
gt; gt; the
gt; gt; column the formulas change like they are supposed to. Then, when I select
gt; gt; them all and enter it as an array formula, they all change back to
gt; gt; ('Sheet1'!$1:1) again. Can you tell me what I am doing wrong? Thank you.
gt; gt; -Rikki
gt; gt;
gt; gt; quot;Biffquot; wrote:
gt; gt;
gt; gt;gt; Here's a link to a sample file:
gt; gt;gt;
gt; gt;gt; s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU
gt; gt;gt;
gt; gt;gt; There are a few different formula methods that could be used. This
gt; gt;gt; demonstrates the method I prefer. Just one formula copied to the cells.
gt; gt;gt; It's
gt; gt;gt; an array formula, though, and array formulas take longer to calculate.
gt; gt;gt; Depending on the size of your file and other factors, you might not
gt; gt;gt; notice
gt; gt;gt; any difference.
gt; gt;gt;
gt; gt;gt; Try deleting some data in column F then look at sheet 2 to see how it
gt; gt;gt; updates.
gt; gt;gt;
gt; gt;gt; If in your situation you expect that there may be 20-40 rows that meet
gt; gt;gt; the
gt; gt;gt; criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the
gt; gt;gt; sample I copied the formula to 10 rows by 10 columns.
gt; gt;gt;
gt; gt;gt; Biff
gt; gt;gt;
gt; gt;gt; quot;Biffquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; gt;Would it still be practical if I had 250 rows of data total and
gt; gt;gt; gt;gt;needed to pull in 10 columns for only about 20-40 rows?
gt; gt;gt; gt;
gt; gt;gt; gt; That should be Ok but you'll have to see how it affects performance and
gt; gt;gt; gt; then make that determination for yourself.
gt; gt;gt; gt;
gt; gt;gt; gt; I'm going to break for dinner but I'll put together a sample file that
gt; gt;gt; gt; demonstrates this (unless Max beats me to it!lt;ggt;) and post a link to
gt; gt;gt; gt; the
gt; gt;gt; gt; file.
gt; gt;gt; gt;
gt; gt;gt; gt; Biff
gt; gt;gt; gt;
gt; gt;gt; gt; quot;Rikki-Handgardsquot; gt; wrote in
gt; gt;gt; gt; message ...
gt; gt;gt; gt;gt; Thanks Biff. Would it still be practical if I had 250 rows of data
gt; gt;gt; gt;gt; total
gt; gt;gt; gt;gt; and
gt; gt;gt; gt;gt; needed to pull in 10 columns for only about 20-40 rows?
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; quot;Biffquot; wrote:
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;gt; gt;Is there a way to do this with a formula and not a pivot table?
gt; gt;gt; gt;gt;gt;
gt; gt;gt; gt;gt;gt; Yes, but whether or not it's practical depends on how many cells from
gt; gt;gt; gt;gt;gt; the
gt; gt;gt; gt;gt;gt; row you need returned and in general, how big is the table of data
gt; gt;gt; gt;gt;gt; this
gt; gt;gt; gt;gt;gt; data
gt; gt;gt; gt;gt;gt; is being extracted from.
gt; gt;gt; gt;gt;gt;
gt; gt;gt; gt;gt;gt; For example, if your table is 50,000 rows by 150 columns and you need
gt; gt;gt; gt;gt;gt; all
gt; gt;gt; gt;gt;gt; 150 columns of data extracted for each instance of quot;valuequot;, then a
gt; gt;gt; gt;gt;gt; formula
gt; gt;gt; gt;gt;gt; approach is not practical.
gt; gt;gt; gt;gt;gt;
gt; gt;gt; gt;gt;gt; Biff
gt; gt;gt; gt;gt;gt;
gt; gt;gt; gt;gt;gt; quot;Rikki-Handgardsquot; gt; wrote
gt; gt;gt; gt;gt;gt; in
gt; gt;gt; gt;gt;gt; message news
gt; gt;gt; gt;gt;gt; gt; Hi. Is there a way to list, on a separate sheet, only rows of data
gt; gt;gt; gt;gt;gt; gt; with a
gt; gt;gt; gt;gt;gt; gt; value in a specific column? I want to list inventory items (and
gt; gt;gt; gt;gt;gt; gt; all
gt; gt;gt; gt;gt;gt; gt; data
gt; gt;gt; gt;gt;gt; gt; in
gt; gt;gt; gt;gt;gt; gt; that row) as long as there is a value in column F. If there is no
gt; gt;gt; gt;gt;gt; gt; value
gt; gt;gt; gt;gt;gt; gt; in
gt; gt;gt; gt;gt;gt; gt; column F for that inventory item, I don't want it to be the
gt; gt;gt; gt;gt;gt; gt; separate
gt; gt;gt; gt;gt;gt; gt; summary
gt; gt;gt; gt;gt;gt; gt; sheet. Is there a way to do this with a formula and not a pivot
gt; gt;gt; gt;gt;gt; gt; table?
gt; gt;gt; gt;gt;gt; gt; Thanks!
gt; gt;gt; gt;gt;gt;
gt; gt;gt; gt;gt;gt;
gt; gt;gt; gt;gt;gt;
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

I have a list 4 x 316:
county code sales tax
smith 36-02 25.00 5.00
allen 14-17 14.00 1.40
caddo 72-36 .00 .00
park 55 -92 97.00 9.70

In a different part of the spread sheet I would like:

code sales tax county
36-02 25.00 5.00 smith
14-17 14.00 1.40 allen
55-92 97.00 9.70 park

I would like this list updated 'automatically' when something is changed in
the top part.
The numbers in the top are looked up on another spreadsheet and populated
upon opening of the workbook.

Do you need anything else?

Thanks for your help

quot;Biffquot; wrote:

gt; Hi!
gt;
gt; How about explaining exactly what you want to do (include the DETAILS!) and
gt; if I can figure it out I'll post a fresh link with a sample.
gt;
gt; Biff
gt;
gt; quot;Sunnyquot; gt; wrote in message
gt; ...
gt; gt; This is exactly what I would liek to do! However, the link no longer
gt; gt; works.
gt; gt; Would you please send it again?
gt; gt;
gt; gt; Thanks-
gt; gt; sunny
gt; gt;
gt; gt; quot;Biffquot; wrote:
gt; gt;
gt; gt;gt; Here's a link to a sample file:
gt; gt;gt;
gt; gt;gt; s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU
gt; gt;gt;
gt; gt;gt; There are a few different formula methods that could be used. This
gt; gt;gt; demonstrates the method I prefer. Just one formula copied to the cells.
gt; gt;gt; It's
gt; gt;gt; an array formula, though, and array formulas take longer to calculate.
gt; gt;gt; Depending on the size of your file and other factors, you might not
gt; gt;gt; notice
gt; gt;gt; any difference.
gt; gt;gt;
gt; gt;gt; Try deleting some data in column F then look at sheet 2 to see how it
gt; gt;gt; updates.
gt; gt;gt;
gt; gt;gt; If in your situation you expect that there may be 20-40 rows that meet
gt; gt;gt; the
gt; gt;gt; criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the
gt; gt;gt; sample I copied the formula to 10 rows by 10 columns.
gt; gt;gt;
gt; gt;gt; Biff
gt; gt;gt;
gt; gt;gt; quot;Biffquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; gt;Would it still be practical if I had 250 rows of data total and
gt; gt;gt; gt;gt;needed to pull in 10 columns for only about 20-40 rows?
gt; gt;gt; gt;
gt; gt;gt; gt; That should be Ok but you'll have to see how it affects performance and
gt; gt;gt; gt; then make that determination for yourself.
gt; gt;gt; gt;
gt; gt;gt; gt; I'm going to break for dinner but I'll put together a sample file that
gt; gt;gt; gt; demonstrates this (unless Max beats me to it!lt;ggt;) and post a link to
gt; gt;gt; gt; the
gt; gt;gt; gt; file.
gt; gt;gt; gt;
gt; gt;gt; gt; Biff
gt; gt;gt; gt;
gt; gt;gt; gt; quot;Rikki-Handgardsquot; gt; wrote in
gt; gt;gt; gt; message ...
gt; gt;gt; gt;gt; Thanks Biff. Would it still be practical if I had 250 rows of data
gt; gt;gt; gt;gt; total
gt; gt;gt; gt;gt; and
gt; gt;gt; gt;gt; needed to pull in 10 columns for only about 20-40 rows?
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; quot;Biffquot; wrote:
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;gt; gt;Is there a way to do this with a formula and not a pivot table?
gt; gt;gt; gt;gt;gt;
gt; gt;gt; gt;gt;gt; Yes, but whether or not it's practical depends on how many cells from
gt; gt;gt; gt;gt;gt; the
gt; gt;gt; gt;gt;gt; row you need returned and in general, how big is the table of data
gt; gt;gt; gt;gt;gt; this
gt; gt;gt; gt;gt;gt; data
gt; gt;gt; gt;gt;gt; is being extracted from.
gt; gt;gt; gt;gt;gt;
gt; gt;gt; gt;gt;gt; For example, if your table is 50,000 rows by 150 columns and you need
gt; gt;gt; gt;gt;gt; all
gt; gt;gt; gt;gt;gt; 150 columns of data extracted for each instance of quot;valuequot;, then a
gt; gt;gt; gt;gt;gt; formula
gt; gt;gt; gt;gt;gt; approach is not practical.
gt; gt;gt; gt;gt;gt;
gt; gt;gt; gt;gt;gt; Biff
gt; gt;gt; gt;gt;gt;
gt; gt;gt; gt;gt;gt; quot;Rikki-Handgardsquot; gt; wrote
gt; gt;gt; gt;gt;gt; in
gt; gt;gt; gt;gt;gt; message news
gt; gt;gt; gt;gt;gt; gt; Hi. Is there a way to list, on a separate sheet, only rows of data
gt; gt;gt; gt;gt;gt; gt; with a
gt; gt;gt; gt;gt;gt; gt; value in a specific column? I want to list inventory items (and
gt; gt;gt; gt;gt;gt; gt; all
gt; gt;gt; gt;gt;gt; gt; data
gt; gt;gt; gt;gt;gt; gt; in
gt; gt;gt; gt;gt;gt; gt; that row) as long as there is a value in column F. If there is no
gt; gt;gt; gt;gt;gt; gt; value
gt; gt;gt; gt;gt;gt; gt; in
gt; gt;gt; gt;gt;gt; gt; column F for that inventory item, I don't want it to be the
gt; gt;gt; gt;gt;gt; gt; separate
gt; gt;gt; gt;gt;gt; gt; summary
gt; gt;gt; gt;gt;gt; gt; sheet. Is there a way to do this with a formula and not a pivot
gt; gt;gt; gt;gt;gt; gt; table?
gt; gt;gt; gt;gt;gt; gt; Thanks!
gt; gt;gt; gt;gt;gt;
gt; gt;gt; gt;gt;gt;
gt; gt;gt; gt;gt;gt;
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;