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!
>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" <[email protected]> wrote in
message news:[email protected]...
> 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!
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" <[email protected]> wrote in
> message news:[email protected]...
> > 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!
>
>
>
>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" <[email protected]> wrote in
message news:[email protected]...
> 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" <[email protected]> wrote in
>> message news:[email protected]...
>> > 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 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
"Rikki-Handgards" wrote:
> 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" <[email protected]> wrote in message
news:[email protected]...
> >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" <[email protected]> wrote in
> message news:[email protected]...
>> 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" <[email protected]> wrote in
>>> message news:[email protected]...
>>> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > >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" <[email protected]> wrote in
> > message news:[email protected]...
> >> 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" <[email protected]> wrote in
> >>> message news:[email protected]...
> >>> > 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" <[email protected]> wrote in
message news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > >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" <[email protected]> wrote in
>> > message news:[email protected]...
>> >> 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" <[email protected]> wrote
>> >>> in
>> >>> message news:[email protected]...
>> >>> > 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 courseI 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" <[email protected]> wrote in
> message news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > >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" <[email protected]> wrote in
> >> > message news:[email protected]...
> >> >> 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" <[email protected]> wrote
> >> >>> in
> >> >>> message news:[email protected]...
> >> >>> > 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!
> >> >>>
> >> >>>
> >> >>>
> >> >
> >> >
> >>
> >>
> >>
>
>
>
This is exactly what I would liek to do! However, the link no longer works.
Would you please send it again?
Thanks-
sunny
"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" <[email protected]> wrote in message
> news:[email protected]...
> > >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" <[email protected]> wrote in
> > message news:[email protected]...
> >> 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" <[email protected]> wrote in
> >>> message news:[email protected]...
> >>> > 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!
How about explaining exactly what you want to do (include the DETAILS!) and
if I can figure it out I'll post a fresh link with a sample.
Biff
"Sunny" <[email protected]> wrote in message
news:[email protected]...
> This is exactly what I would liek to do! However, the link no longer
> works.
> Would you please send it again?
>
> Thanks-
> sunny
>
> "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" <[email protected]> wrote in message
>> news:[email protected]...
>> > >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" <[email protected]> wrote in
>> > message news:[email protected]...
>> >> 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" <[email protected]> wrote
>> >>> in
>> >>> message news:[email protected]...
>> >>> > 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!
>> >>>
>> >>>
>> >>>
>> >
>> >
>>
>>
>>
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
"Biff" wrote:
> Hi!
>
> How about explaining exactly what you want to do (include the DETAILS!) and
> if I can figure it out I'll post a fresh link with a sample.
>
> Biff
>
> "Sunny" <[email protected]> wrote in message
> news:[email protected]...
> > This is exactly what I would liek to do! However, the link no longer
> > works.
> > Would you please send it again?
> >
> > Thanks-
> > sunny
> >
> > "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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > >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" <[email protected]> wrote in
> >> > message news:[email protected]...
> >> >> 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" <[email protected]> wrote
> >> >>> in
> >> >>> message news:[email protected]...
> >> >>> > 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!
> >> >>>
> >> >>>
> >> >>>
> >> >
> >> >
> >>
> >>
> >>
>
>
>
Ok.....
So you want to extract data where there is a value greater than 0 in *BOTH*
Sales and Tax?
Is the size of the table always the same? Always 4c x 316r ?
What is the cell address where the table starts? For example, based on you
sample Smith appears in cell A2. The reason I "need" to know this is because
the formula will contain an expression based on this starting cell. A lot of
times a poster will copy the formula from a reply and change this expression
incorrectly and that ends up causing the formula to not work properly
leading to a bunch of follow-up questions.
This should be no problem.
Biff
"Sunny" <[email protected]> wrote in message
news:[email protected]...
>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
>
> "Biff" wrote:
>
>> Hi!
>>
>> How about explaining exactly what you want to do (include the DETAILS!)
>> and
>> if I can figure it out I'll post a fresh link with a sample.
>>
>> Biff
>>
>> "Sunny" <[email protected]> wrote in message
>> news:[email protected]...
>> > This is exactly what I would liek to do! However, the link no longer
>> > works.
>> > Would you please send it again?
>> >
>> > Thanks-
>> > sunny
>> >
>> > "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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > >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" <[email protected]> wrote
>> >> > in
>> >> > message news:[email protected]...
>> >> >> 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" <[email protected]>
>> >> >>> wrote
>> >> >>> in
>> >> >>> message news:[email protected]...
>> >> >>> > 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!
>> >> >>>
>> >> >>>
>> >> >>>
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>
316 is a constant number.
It starts in A4 and can land anyplace right of W.
"Biff" wrote:
> Ok.....
>
> So you want to extract data where there is a value greater than 0 in *BOTH*
> Sales and Tax?
>
> Is the size of the table always the same? Always 4c x 316r ?
>
> What is the cell address where the table starts? For example, based on you
> sample Smith appears in cell A2. The reason I "need" to know this is because
> the formula will contain an expression based on this starting cell. A lot of
> times a poster will copy the formula from a reply and change this expression
> incorrectly and that ends up causing the formula to not work properly
> leading to a bunch of follow-up questions.
>
> This should be no problem.
>
> Biff
>
> "Sunny" <[email protected]> wrote in message
> news:[email protected]...
> >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
> >
> > "Biff" wrote:
> >
> >> Hi!
> >>
> >> How about explaining exactly what you want to do (include the DETAILS!)
> >> and
> >> if I can figure it out I'll post a fresh link with a sample.
> >>
> >> Biff
> >>
> >> "Sunny" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > This is exactly what I would liek to do! However, the link no longer
> >> > works.
> >> > Would you please send it again?
> >> >
> >> > Thanks-
> >> > sunny
> >> >
> >> > "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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > >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" <[email protected]> wrote
> >> >> > in
> >> >> > message news:[email protected]...
> >> >> >> 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" <[email protected]>
> >> >> >>> wrote
> >> >> >>> in
> >> >> >>> message news:[email protected]...
> >> >> >>> > 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 land anyplace right of W
What does that mean? You want the extracted data to be placed anywhere to
the right of column W?
Biff
"Sunny" <[email protected]> wrote in message
news:[email protected]...
> 316 is a constant number.
> It starts in A4 and can land anyplace right of W.
>
> "Biff" wrote:
>
>> Ok.....
>>
>> So you want to extract data where there is a value greater than 0 in
>> *BOTH*
>> Sales and Tax?
>>
>> Is the size of the table always the same? Always 4c x 316r ?
>>
>> What is the cell address where the table starts? For example, based on
>> you
>> sample Smith appears in cell A2. The reason I "need" to know this is
>> because
>> the formula will contain an expression based on this starting cell. A lot
>> of
>> times a poster will copy the formula from a reply and change this
>> expression
>> incorrectly and that ends up causing the formula to not work properly
>> leading to a bunch of follow-up questions.
>>
>> This should be no problem.
>>
>> Biff
>>
>> "Sunny" <[email protected]> wrote in message
>> news:[email protected]...
>> >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
>> >
>> > "Biff" wrote:
>> >
>> >> Hi!
>> >>
>> >> How about explaining exactly what you want to do (include the
>> >> DETAILS!)
>> >> and
>> >> if I can figure it out I'll post a fresh link with a sample.
>> >>
>> >> Biff
>> >>
>> >> "Sunny" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > This is exactly what I would liek to do! However, the link no
>> >> > longer
>> >> > works.
>> >> > Would you please send it again?
>> >> >
>> >> > Thanks-
>> >> > sunny
>> >> >
>> >> > "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" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > >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" <[email protected]>
>> >> >> > wrote
>> >> >> > in
>> >> >> > message
>> >> >> > news:[email protected]...
>> >> >> >> 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" <[email protected]>
>> >> >> >>> wrote
>> >> >> >>> in
>> >> >> >>> message
>> >> >> >>> news:[email protected]...
>> >> >> >>> > 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!
>> >> >> >>>
>> >> >> >>>
>> >> >> >>>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
yes and only the sales column needs to be checked for 0
"Biff" wrote:
> >can land anyplace right of W
>
> What does that mean? You want the extracted data to be placed anywhere to
> the right of column W?
>
> Biff
>
> "Sunny" <[email protected]> wrote in message
> news:[email protected]...
> > 316 is a constant number.
> > It starts in A4 and can land anyplace right of W.
> >
> > "Biff" wrote:
> >
> >> Ok.....
> >>
> >> So you want to extract data where there is a value greater than 0 in
> >> *BOTH*
> >> Sales and Tax?
> >>
> >> Is the size of the table always the same? Always 4c x 316r ?
> >>
> >> What is the cell address where the table starts? For example, based on
> >> you
> >> sample Smith appears in cell A2. The reason I "need" to know this is
> >> because
> >> the formula will contain an expression based on this starting cell. A lot
> >> of
> >> times a poster will copy the formula from a reply and change this
> >> expression
> >> incorrectly and that ends up causing the formula to not work properly
> >> leading to a bunch of follow-up questions.
> >>
> >> This should be no problem.
> >>
> >> Biff
> >>
> >> "Sunny" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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
> >> >
> >> > "Biff" wrote:
> >> >
> >> >> Hi!
> >> >>
> >> >> How about explaining exactly what you want to do (include the
> >> >> DETAILS!)
> >> >> and
> >> >> if I can figure it out I'll post a fresh link with a sample.
> >> >>
> >> >> Biff
> >> >>
> >> >> "Sunny" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > This is exactly what I would liek to do! However, the link no
> >> >> > longer
> >> >> > works.
> >> >> > Would you please send it again?
> >> >> >
> >> >> > Thanks-
> >> >> > sunny
> >> >> >
> >> >> > "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" <[email protected]> wrote in message
> >> >> >> news:[email protected]...
> >> >> >> > >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" <[email protected]>
> >> >> >> > wrote
> >> >> >> > in
> >> >> >> > message
> >> >> >> > news:[email protected]...
> >> >> >> >> 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" <[email protected]>
> >> >> >> >>> wrote
> >> >> >> >>> in
> >> >> >> >>> message
> >> >> >> >>> news:[email protected]...
> >> >> >> >>> > 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!
> >> >> >> >>>
> >> >> >> >>>
> >> >> >> >>>
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
Ok, no problem!
I'll be away from the computer for a few hours so check back later.
Biff
"Sunny" <[email protected]> wrote in message
news:[email protected]...
> yes and only the sales column needs to be checked for 0
>
> "Biff" wrote:
>
>> >can land anyplace right of W
>>
>> What does that mean? You want the extracted data to be placed anywhere to
>> the right of column W?
>>
>> Biff
>>
>> "Sunny" <[email protected]> wrote in message
>> news:[email protected]...
>> > 316 is a constant number.
>> > It starts in A4 and can land anyplace right of W.
>> >
>> > "Biff" wrote:
>> >
>> >> Ok.....
>> >>
>> >> So you want to extract data where there is a value greater than 0 in
>> >> *BOTH*
>> >> Sales and Tax?
>> >>
>> >> Is the size of the table always the same? Always 4c x 316r ?
>> >>
>> >> What is the cell address where the table starts? For example, based on
>> >> you
>> >> sample Smith appears in cell A2. The reason I "need" to know this is
>> >> because
>> >> the formula will contain an expression based on this starting cell. A
>> >> lot
>> >> of
>> >> times a poster will copy the formula from a reply and change this
>> >> expression
>> >> incorrectly and that ends up causing the formula to not work properly
>> >> leading to a bunch of follow-up questions.
>> >>
>> >> This should be no problem.
>> >>
>> >> Biff
>> >>
>> >> "Sunny" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >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
>> >> >
>> >> > "Biff" wrote:
>> >> >
>> >> >> Hi!
>> >> >>
>> >> >> How about explaining exactly what you want to do (include the
>> >> >> DETAILS!)
>> >> >> and
>> >> >> if I can figure it out I'll post a fresh link with a sample.
>> >> >>
>> >> >> Biff
>> >> >>
>> >> >> "Sunny" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > This is exactly what I would liek to do! However, the link no
>> >> >> > longer
>> >> >> > works.
>> >> >> > Would you please send it again?
>> >> >> >
>> >> >> > Thanks-
>> >> >> > sunny
>> >> >> >
>> >> >> > "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" <[email protected]> wrote in message
>> >> >> >> news:[email protected]...
>> >> >> >> > >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" <[email protected]>
>> >> >> >> > wrote
>> >> >> >> > in
>> >> >> >> > message
>> >> >> >> > news:[email protected]...
>> >> >> >> >> 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"
>> >> >> >> >>> <[email protected]>
>> >> >> >> >>> wrote
>> >> >> >> >>> in
>> >> >> >> >>> message
>> >> >> >> >>> news:[email protected]...
>> >> >> >> >>> > 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!
>> >> >> >> >>>
>> >> >> >> >>>
>> >> >> >> >>>
>> >> >> >> >
>> >> >> >> >
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
thanks!
"Biff" wrote:
> Ok, no problem!
>
> I'll be away from the computer for a few hours so check back later.
>
> Biff
>
> "Sunny" <[email protected]> wrote in message
> news:[email protected]...
> > yes and only the sales column needs to be checked for 0
> >
> > "Biff" wrote:
> >
> >> >can land anyplace right of W
> >>
> >> What does that mean? You want the extracted data to be placed anywhere to
> >> the right of column W?
> >>
> >> Biff
> >>
> >> "Sunny" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 316 is a constant number.
> >> > It starts in A4 and can land anyplace right of W.
> >> >
> >> > "Biff" wrote:
> >> >
> >> >> Ok.....
> >> >>
> >> >> So you want to extract data where there is a value greater than 0 in
> >> >> *BOTH*
> >> >> Sales and Tax?
> >> >>
> >> >> Is the size of the table always the same? Always 4c x 316r ?
> >> >>
> >> >> What is the cell address where the table starts? For example, based on
> >> >> you
> >> >> sample Smith appears in cell A2. The reason I "need" to know this is
> >> >> because
> >> >> the formula will contain an expression based on this starting cell. A
> >> >> lot
> >> >> of
> >> >> times a poster will copy the formula from a reply and change this
> >> >> expression
> >> >> incorrectly and that ends up causing the formula to not work properly
> >> >> leading to a bunch of follow-up questions.
> >> >>
> >> >> This should be no problem.
> >> >>
> >> >> Biff
> >> >>
> >> >> "Sunny" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> >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
> >> >> >
> >> >> > "Biff" wrote:
> >> >> >
> >> >> >> Hi!
> >> >> >>
> >> >> >> How about explaining exactly what you want to do (include the
> >> >> >> DETAILS!)
> >> >> >> and
> >> >> >> if I can figure it out I'll post a fresh link with a sample.
> >> >> >>
> >> >> >> Biff
> >> >> >>
> >> >> >> "Sunny" <[email protected]> wrote in message
> >> >> >> news:[email protected]...
> >> >> >> > This is exactly what I would liek to do! However, the link no
> >> >> >> > longer
> >> >> >> > works.
> >> >> >> > Would you please send it again?
> >> >> >> >
> >> >> >> > Thanks-
> >> >> >> > sunny
> >> >> >> >
> >> >> >> > "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" <[email protected]> wrote in message
> >> >> >> >> news:[email protected]...
> >> >> >> >> > >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" <[email protected]>
> >> >> >> >> > wrote
> >> >> >> >> > in
> >> >> >> >> > message
> >> >> >> >> > news:[email protected]...
> >> >> >> >> >> 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"
> >> >> >> >> >>> <[email protected]>
> >> >> >> >> >>> wrote
> >> >> >> >> >>> in
> >> >> >> >> >>> message
> >> >> >> >> >>> news:[email protected]...
> >> >> >> >> >>> > 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!
> >> >> >> >> >>>
> >> >> >> >> >>>
> >> >> >> >> >>>
> >> >> >> >> >
> >> >> >> >> >
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
Ok, here you go:
Sample file:
Sample extract.xls 18.5kb
http://cjoint.com/?ipe25QHXQb
I took a little bit of a different approach on this one. I saw your other
post!!!!!
If you have 50 sheets and they average 300 rows one array formula copied to
that many cells would really slow things down!
So, I used just one array formula in column X and then simple Vlookups in
columns Y and Z and then a simple Index/Match in column AA. Also, another
reason for the different formula is that you want the extracted data in a
slightly different sequence. I'm assuming that the "code" is unique to the
county? That's what I based the lookup formulas on.
So, all you should have to do is change the references to the actual end of
range of your data. You'll have to copy the formulas down to enough rows so
that they pick up all the related data. Typically, how many entries will
have 0 sales? 10? 20? 200? That's how many rows you would need to copy the
formulas to.
Are you familar with array formulas?
How to enter an array formula:
http://cpearson.com/excel/array.htm
Biff
"Sunny" <[email protected]> wrote in message
news:[email protected]...
> thanks!
>
> "Biff" wrote:
>
>> Ok, no problem!
>>
>> I'll be away from the computer for a few hours so check back later.
>>
>> Biff
>>
>> "Sunny" <[email protected]> wrote in message
>> news:[email protected]...
>> > yes and only the sales column needs to be checked for 0
>> >
>> > "Biff" wrote:
>> >
>> >> >can land anyplace right of W
>> >>
>> >> What does that mean? You want the extracted data to be placed anywhere
>> >> to
>> >> the right of column W?
>> >>
>> >> Biff
>> >>
>> >> "Sunny" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 316 is a constant number.
>> >> > It starts in A4 and can land anyplace right of W.
>> >> >
>> >> > "Biff" wrote:
>> >> >
>> >> >> Ok.....
>> >> >>
>> >> >> So you want to extract data where there is a value greater than 0
>> >> >> in
>> >> >> *BOTH*
>> >> >> Sales and Tax?
>> >> >>
>> >> >> Is the size of the table always the same? Always 4c x 316r ?
>> >> >>
>> >> >> What is the cell address where the table starts? For example, based
>> >> >> on
>> >> >> you
>> >> >> sample Smith appears in cell A2. The reason I "need" to know this
>> >> >> is
>> >> >> because
>> >> >> the formula will contain an expression based on this starting cell.
>> >> >> A
>> >> >> lot
>> >> >> of
>> >> >> times a poster will copy the formula from a reply and change this
>> >> >> expression
>> >> >> incorrectly and that ends up causing the formula to not work
>> >> >> properly
>> >> >> leading to a bunch of follow-up questions.
>> >> >>
>> >> >> This should be no problem.
>> >> >>
>> >> >> Biff
>> >> >>
>> >> >> "Sunny" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> >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
>> >> >> >
>> >> >> > "Biff" wrote:
>> >> >> >
>> >> >> >> Hi!
>> >> >> >>
>> >> >> >> How about explaining exactly what you want to do (include the
>> >> >> >> DETAILS!)
>> >> >> >> and
>> >> >> >> if I can figure it out I'll post a fresh link with a sample.
>> >> >> >>
>> >> >> >> Biff
>> >> >> >>
>> >> >> >> "Sunny" <[email protected]> wrote in message
>> >> >> >> news:[email protected]...
>> >> >> >> > This is exactly what I would liek to do! However, the link no
>> >> >> >> > longer
>> >> >> >> > works.
>> >> >> >> > Would you please send it again?
>> >> >> >> >
>> >> >> >> > Thanks-
>> >> >> >> > sunny
>> >> >> >> >
>> >> >> >> > "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" <[email protected]> wrote in message
>> >> >> >> >> news:[email protected]...
>> >> >> >> >> > >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"
>> >> >> >> >> > <[email protected]>
>> >> >> >> >> > wrote
>> >> >> >> >> > in
>> >> >> >> >> > message
>> >> >> >> >> > news:[email protected]...
>> >> >> >> >> >> 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"
>> >> >> >> >> >>> <[email protected]>
>> >> >> >> >> >>> wrote
>> >> >> >> >> >>> in
>> >> >> >> >> >>> message
>> >> >> >> >> >>> news:[email protected]...
>> >> >> >> >> >>> > 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!
>> >> >> >> >> >>>
>> >> >> >> >> >>>
>> >> >> >> >> >>>
>> >> >> >> >> >
>> >> >> >> >> >
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
Sorry it took so long . . . insane week.
This works exactly like I want it to on the lines I got from your example.
I can't seem to make the copy to other lines work. Is there a trick?
"Biff" wrote:
> Ok, here you go:
>
> Sample file:
>
> Sample extract.xls 18.5kb
>
> http://cjoint.com/?ipe25QHXQb
>
> I took a little bit of a different approach on this one. I saw your other
> post!!!!!
>
> If you have 50 sheets and they average 300 rows one array formula copied to
> that many cells would really slow things down!
>
> So, I used just one array formula in column X and then simple Vlookups in
> columns Y and Z and then a simple Index/Match in column AA. Also, another
> reason for the different formula is that you want the extracted data in a
> slightly different sequence. I'm assuming that the "code" is unique to the
> county? That's what I based the lookup formulas on.
>
> So, all you should have to do is change the references to the actual end of
> range of your data. You'll have to copy the formulas down to enough rows so
> that they pick up all the related data. Typically, how many entries will
> have 0 sales? 10? 20? 200? That's how many rows you would need to copy the
> formulas to.
>
> Are you familar with array formulas?
>
> How to enter an array formula:
>
> http://cpearson.com/excel/array.htm
>
> Biff
>
> "Sunny" <[email protected]> wrote in message
> news:[email protected]...
> > thanks!
> >
> > "Biff" wrote:
> >
> >> Ok, no problem!
> >>
> >> I'll be away from the computer for a few hours so check back later.
> >>
> >> Biff
> >>
> >> "Sunny" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > yes and only the sales column needs to be checked for 0
> >> >
> >> > "Biff" wrote:
> >> >
> >> >> >can land anyplace right of W
> >> >>
> >> >> What does that mean? You want the extracted data to be placed anywhere
> >> >> to
> >> >> the right of column W?
> >> >>
> >> >> Biff
> >> >>
> >> >> "Sunny" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 316 is a constant number.
> >> >> > It starts in A4 and can land anyplace right of W.
> >> >> >
> >> >> > "Biff" wrote:
> >> >> >
> >> >> >> Ok.....
> >> >> >>
> >> >> >> So you want to extract data where there is a value greater than 0
> >> >> >> in
> >> >> >> *BOTH*
> >> >> >> Sales and Tax?
> >> >> >>
> >> >> >> Is the size of the table always the same? Always 4c x 316r ?
> >> >> >>
> >> >> >> What is the cell address where the table starts? For example, based
> >> >> >> on
> >> >> >> you
> >> >> >> sample Smith appears in cell A2. The reason I "need" to know this
> >> >> >> is
> >> >> >> because
> >> >> >> the formula will contain an expression based on this starting cell.
> >> >> >> A
> >> >> >> lot
> >> >> >> of
> >> >> >> times a poster will copy the formula from a reply and change this
> >> >> >> expression
> >> >> >> incorrectly and that ends up causing the formula to not work
> >> >> >> properly
> >> >> >> leading to a bunch of follow-up questions.
> >> >> >>
> >> >> >> This should be no problem.
> >> >> >>
> >> >> >> Biff
> >> >> >>
> >> >> >> "Sunny" <[email protected]> wrote in message
> >> >> >> news:[email protected]...
> >> >> >> >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
> >> >> >> >
> >> >> >> > "Biff" wrote:
> >> >> >> >
> >> >> >> >> Hi!
> >> >> >> >>
> >> >> >> >> How about explaining exactly what you want to do (include the
> >> >> >> >> DETAILS!)
> >> >> >> >> and
> >> >> >> >> if I can figure it out I'll post a fresh link with a sample.
> >> >> >> >>
> >> >> >> >> Biff
> >> >> >> >>
> >> >> >> >> "Sunny" <[email protected]> wrote in message
> >> >> >> >> news:[email protected]...
> >> >> >> >> > This is exactly what I would liek to do! However, the link no
> >> >> >> >> > longer
> >> >> >> >> > works.
> >> >> >> >> > Would you please send it again?
> >> >> >> >> >
> >> >> >> >> > Thanks-
> >> >> >> >> > sunny
> >> >> >> >> >
> >> >> >> >> > "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" <[email protected]> wrote in message
> >> >> >> >> >> news:[email protected]...
> >> >> >> >> >> > >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"
> >> >> >> >> >> > <[email protected]>
> >> >> >> >> >> > wrote
> >> >> >> >> >> > in
> >> >> >> >> >> > message
> >> >> >> >> >> > news:[email protected]...
> >> >> >> >> >> >> 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"
> >> >> >> >> >> >>> <[email protected]>
> >> >> >> >> >> >>> wrote
> >> >> >> >> >> >>> in
> >> >> >> >> >> >>> message
> >> >> >> >> >> >>> news:[email protected]...
> >> >> >> >> >> >>> > 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!
> >> >> >> >> >> >>>
> >> >> >> >> >> >>>
> >> >> >> >> >> >>>
> >> >> >> >> >> >
> >> >> >> >> >> >
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
Not sure I understand what you mean?
Biff
"Sunny" <[email protected]> wrote in message
news:[email protected]...
> Sorry it took so long . . . insane week.
> This works exactly like I want it to on the lines I got from your example.
> I can't seem to make the copy to other lines work. Is there a trick?
>
> "Biff" wrote:
>
>> Ok, here you go:
>>
>> Sample file:
>>
>> Sample extract.xls 18.5kb
>>
>> http://cjoint.com/?ipe25QHXQb
>>
>> I took a little bit of a different approach on this one. I saw your other
>> post!!!!!
>>
>> If you have 50 sheets and they average 300 rows one array formula copied
>> to
>> that many cells would really slow things down!
>>
>> So, I used just one array formula in column X and then simple Vlookups in
>> columns Y and Z and then a simple Index/Match in column AA. Also, another
>> reason for the different formula is that you want the extracted data in a
>> slightly different sequence. I'm assuming that the "code" is unique to
>> the
>> county? That's what I based the lookup formulas on.
>>
>> So, all you should have to do is change the references to the actual end
>> of
>> range of your data. You'll have to copy the formulas down to enough rows
>> so
>> that they pick up all the related data. Typically, how many entries will
>> have 0 sales? 10? 20? 200? That's how many rows you would need to copy
>> the
>> formulas to.
>>
>> Are you familar with array formulas?
>>
>> How to enter an array formula:
>>
>> http://cpearson.com/excel/array.htm
>>
>> Biff
>>
>> "Sunny" <[email protected]> wrote in message
>> news:[email protected]...
>> > thanks!
>> >
>> > "Biff" wrote:
>> >
>> >> Ok, no problem!
>> >>
>> >> I'll be away from the computer for a few hours so check back later.
>> >>
>> >> Biff
>> >>
>> >> "Sunny" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > yes and only the sales column needs to be checked for 0
>> >> >
>> >> > "Biff" wrote:
>> >> >
>> >> >> >can land anyplace right of W
>> >> >>
>> >> >> What does that mean? You want the extracted data to be placed
>> >> >> anywhere
>> >> >> to
>> >> >> the right of column W?
>> >> >>
>> >> >> Biff
>> >> >>
>> >> >> "Sunny" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > 316 is a constant number.
>> >> >> > It starts in A4 and can land anyplace right of W.
>> >> >> >
>> >> >> > "Biff" wrote:
>> >> >> >
>> >> >> >> Ok.....
>> >> >> >>
>> >> >> >> So you want to extract data where there is a value greater than
>> >> >> >> 0
>> >> >> >> in
>> >> >> >> *BOTH*
>> >> >> >> Sales and Tax?
>> >> >> >>
>> >> >> >> Is the size of the table always the same? Always 4c x 316r ?
>> >> >> >>
>> >> >> >> What is the cell address where the table starts? For example,
>> >> >> >> based
>> >> >> >> on
>> >> >> >> you
>> >> >> >> sample Smith appears in cell A2. The reason I "need" to know
>> >> >> >> this
>> >> >> >> is
>> >> >> >> because
>> >> >> >> the formula will contain an expression based on this starting
>> >> >> >> cell.
>> >> >> >> A
>> >> >> >> lot
>> >> >> >> of
>> >> >> >> times a poster will copy the formula from a reply and change
>> >> >> >> this
>> >> >> >> expression
>> >> >> >> incorrectly and that ends up causing the formula to not work
>> >> >> >> properly
>> >> >> >> leading to a bunch of follow-up questions.
>> >> >> >>
>> >> >> >> This should be no problem.
>> >> >> >>
>> >> >> >> Biff
>> >> >> >>
>> >> >> >> "Sunny" <[email protected]> wrote in message
>> >> >> >> news:[email protected]...
>> >> >> >> >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
>> >> >> >> >
>> >> >> >> > "Biff" wrote:
>> >> >> >> >
>> >> >> >> >> Hi!
>> >> >> >> >>
>> >> >> >> >> How about explaining exactly what you want to do (include the
>> >> >> >> >> DETAILS!)
>> >> >> >> >> and
>> >> >> >> >> if I can figure it out I'll post a fresh link with a sample.
>> >> >> >> >>
>> >> >> >> >> Biff
>> >> >> >> >>
>> >> >> >> >> "Sunny" <[email protected]> wrote in message
>> >> >> >> >> news:[email protected]...
>> >> >> >> >> > This is exactly what I would liek to do! However, the link
>> >> >> >> >> > no
>> >> >> >> >> > longer
>> >> >> >> >> > works.
>> >> >> >> >> > Would you please send it again?
>> >> >> >> >> >
>> >> >> >> >> > Thanks-
>> >> >> >> >> > sunny
>> >> >> >> >> >
>> >> >> >> >> > "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" <[email protected]> wrote in message
>> >> >> >> >> >> news:[email protected]...
>> >> >> >> >> >> > >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"
>> >> >> >> >> >> > <[email protected]>
>> >> >> >> >> >> > wrote
>> >> >> >> >> >> > in
>> >> >> >> >> >> > message
>> >> >> >> >> >> > news:[email protected]...
>> >> >> >> >> >> >> 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"
>> >> >> >> >> >> >>> <[email protected]>
>> >> >> >> >> >> >>> wrote
>> >> >> >> >> >> >>> in
>> >> >> >> >> >> >>> message
>> >> >> >> >> >> >>> news:[email protected]...
>> >> >> >> >> >> >>> > 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!
>> >> >> >> >> >> >>>
>> >> >> >> >> >> >>>
>> >> >> >> >> >> >>>
>> >> >> >> >> >> >
>> >> >> >> >> >> >
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >>
Normally I just drag down to other cells. When I do that with this formula,
I get #REF in the destination cells. I even tried copy and paste. What
other way is there to get a perfect formula into subsequent rows?
sunny
"Biff" wrote:
> Not sure I understand what you mean?
>
> Biff
>
> "Sunny" <[email protected]> wrote in message
> news:[email protected]...
> > Sorry it took so long . . . insane week.
> > This works exactly like I want it to on the lines I got from your example.
> > I can't seem to make the copy to other lines work. Is there a trick?
> >
> > "Biff" wrote:
> >
> >> Ok, here you go:
> >>
> >> Sample file:
> >>
> >> Sample extract.xls 18.5kb
> >>
> >> http://cjoint.com/?ipe25QHXQb
> >>
> >> I took a little bit of a different approach on this one. I saw your other
> >> post!!!!!
> >>
> >> If you have 50 sheets and they average 300 rows one array formula copied
> >> to
> >> that many cells would really slow things down!
> >>
> >> So, I used just one array formula in column X and then simple Vlookups in
> >> columns Y and Z and then a simple Index/Match in column AA. Also, another
> >> reason for the different formula is that you want the extracted data in a
> >> slightly different sequence. I'm assuming that the "code" is unique to
> >> the
> >> county? That's what I based the lookup formulas on.
> >>
> >> So, all you should have to do is change the references to the actual end
> >> of
> >> range of your data. You'll have to copy the formulas down to enough rows
> >> so
> >> that they pick up all the related data. Typically, how many entries will
> >> have 0 sales? 10? 20? 200? That's how many rows you would need to copy
> >> the
> >> formulas to.
> >>
> >> Are you familar with array formulas?
> >>
> >> How to enter an array formula:
> >>
> >> http://cpearson.com/excel/array.htm
> >>
> >> Biff
> >>
> >> "Sunny" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > thanks!
> >> >
> >> > "Biff" wrote:
> >> >
> >> >> Ok, no problem!
> >> >>
> >> >> I'll be away from the computer for a few hours so check back later.
> >> >>
> >> >> Biff
> >> >>
> >> >> "Sunny" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > yes and only the sales column needs to be checked for 0
> >> >> >
> >> >> > "Biff" wrote:
> >> >> >
> >> >> >> >can land anyplace right of W
> >> >> >>
> >> >> >> What does that mean? You want the extracted data to be placed
> >> >> >> anywhere
> >> >> >> to
> >> >> >> the right of column W?
> >> >> >>
> >> >> >> Biff
> >> >> >>
> >> >> >> "Sunny" <[email protected]> wrote in message
> >> >> >> news:[email protected]...
> >> >> >> > 316 is a constant number.
> >> >> >> > It starts in A4 and can land anyplace right of W.
> >> >> >> >
> >> >> >> > "Biff" wrote:
> >> >> >> >
> >> >> >> >> Ok.....
> >> >> >> >>
> >> >> >> >> So you want to extract data where there is a value greater than
> >> >> >> >> 0
> >> >> >> >> in
> >> >> >> >> *BOTH*
> >> >> >> >> Sales and Tax?
> >> >> >> >>
> >> >> >> >> Is the size of the table always the same? Always 4c x 316r ?
> >> >> >> >>
> >> >> >> >> What is the cell address where the table starts? For example,
> >> >> >> >> based
> >> >> >> >> on
> >> >> >> >> you
> >> >> >> >> sample Smith appears in cell A2. The reason I "need" to know
> >> >> >> >> this
> >> >> >> >> is
> >> >> >> >> because
> >> >> >> >> the formula will contain an expression based on this starting
> >> >> >> >> cell.
> >> >> >> >> A
> >> >> >> >> lot
> >> >> >> >> of
> >> >> >> >> times a poster will copy the formula from a reply and change
> >> >> >> >> this
> >> >> >> >> expression
> >> >> >> >> incorrectly and that ends up causing the formula to not work
> >> >> >> >> properly
> >> >> >> >> leading to a bunch of follow-up questions.
> >> >> >> >>
> >> >> >> >> This should be no problem.
> >> >> >> >>
> >> >> >> >> Biff
> >> >> >> >>
> >> >> >> >> "Sunny" <[email protected]> wrote in message
> >> >> >> >> news:[email protected]...
> >> >> >> >> >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
> >> >> >> >> >
> >> >> >> >> > "Biff" wrote:
> >> >> >> >> >
> >> >> >> >> >> Hi!
> >> >> >> >> >>
> >> >> >> >> >> How about explaining exactly what you want to do (include the
> >> >> >> >> >> DETAILS!)
> >> >> >> >> >> and
> >> >> >> >> >> if I can figure it out I'll post a fresh link with a sample.
> >> >> >> >> >>
> >> >> >> >> >> Biff
> >> >> >> >> >>
> >> >> >> >> >> "Sunny" <[email protected]> wrote in message
> >> >> >> >> >> news:[email protected]...
> >> >> >> >> >> > This is exactly what I would liek to do! However, the link
> >> >> >> >> >> > no
> >> >> >> >> >> > longer
> >> >> >> >> >> > works.
> >> >> >> >> >> > Would you please send it again?
> >> >> >> >> >> >
> >> >> >> >> >> > Thanks-
> >> >> >> >> >> > sunny
> >> >> >> >> >> >
> >> >> >> >> >> > "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" <[email protected]> wrote in message
> >> >> >> >> >> >> news:[email protected]...
> >> >> >> >> >> >> > >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"
> >> >> >> >> >> >> > <[email protected]>
> >> >> >> >> >> >> > wrote
> >> >> >> >> >> >> > in
> >> >> >> >> >> >> > message
> >> >> >> >> >> >> > news:[email protected]...
> >> >> >> >> >> >> >> 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"
> >> >> >> >> >> >> >>> <[email protected]>
> >> >> >> >> >> >> >>> wrote
> >> >> >> >> >> >> >>> in
> >> >> >> >> >> >> >>> message
> >> >> >> >> >> >> >>> news:[email protected]...
> >> >> >> >> >> >> >>> > Hi. Is there a way to list, on a separate sheet,
> >> >> >> >> >> >> >>> > only
> >> >> >> >> >> >> >>> > rows
> >> >> >> >> >> >> >>> > of
> >> >> >> >> >> >> >>> > data
> >> >> >> >> >> >> >>> > with a
Can you send me a copy of your file? I don't need the whole thing if it's a
huge file, just the sheet where you're getting these errors. The only thing
that I can think of that would cause #REF! errors is if you were somehow
returning references that were outside of the range in the INDEX portion of
the formula. If you can send me the file I'm at:
xl can help at comcast period net
Remove "can" and change the obvious.
Biff
"Sunny" <[email protected]> wrote in message
news:[email protected]...
> Normally I just drag down to other cells. When I do that with this
> formula,
> I get #REF in the destination cells. I even tried copy and paste. What
> other way is there to get a perfect formula into subsequent rows?
>
> sunny
>
> "Biff" wrote:
>
>> Not sure I understand what you mean?
>>
>> Biff
>>
>> "Sunny" <[email protected]> wrote in message
>> news:[email protected]...
>> > Sorry it took so long . . . insane week.
>> > This works exactly like I want it to on the lines I got from your
>> > example.
>> > I can't seem to make the copy to other lines work. Is there a trick?
>> >
>> > "Biff" wrote:
>> >
>> >> Ok, here you go:
>> >>
>> >> Sample file:
>> >>
>> >> Sample extract.xls 18.5kb
>> >>
>> >> http://cjoint.com/?ipe25QHXQb
>> >>
>> >> I took a little bit of a different approach on this one. I saw your
>> >> other
>> >> post!!!!!
>> >>
>> >> If you have 50 sheets and they average 300 rows one array formula
>> >> copied
>> >> to
>> >> that many cells would really slow things down!
>> >>
>> >> So, I used just one array formula in column X and then simple Vlookups
>> >> in
>> >> columns Y and Z and then a simple Index/Match in column AA. Also,
>> >> another
>> >> reason for the different formula is that you want the extracted data
>> >> in a
>> >> slightly different sequence. I'm assuming that the "code" is unique to
>> >> the
>> >> county? That's what I based the lookup formulas on.
>> >>
>> >> So, all you should have to do is change the references to the actual
>> >> end
>> >> of
>> >> range of your data. You'll have to copy the formulas down to enough
>> >> rows
>> >> so
>> >> that they pick up all the related data. Typically, how many entries
>> >> will
>> >> have 0 sales? 10? 20? 200? That's how many rows you would need to copy
>> >> the
>> >> formulas to.
>> >>
>> >> Are you familar with array formulas?
>> >>
>> >> How to enter an array formula:
>> >>
>> >> http://cpearson.com/excel/array.htm
>> >>
>> >> Biff
>> >>
>> >> "Sunny" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > thanks!
>> >> >
>> >> > "Biff" wrote:
>> >> >
>> >> >> Ok, no problem!
>> >> >>
>> >> >> I'll be away from the computer for a few hours so check back later.
>> >> >>
>> >> >> Biff
>> >> >>
>> >> >> "Sunny" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > yes and only the sales column needs to be checked for 0
>> >> >> >
>> >> >> > "Biff" wrote:
>> >> >> >
>> >> >> >> >can land anyplace right of W
>> >> >> >>
>> >> >> >> What does that mean? You want the extracted data to be placed
>> >> >> >> anywhere
>> >> >> >> to
>> >> >> >> the right of column W?
>> >> >> >>
>> >> >> >> Biff
>> >> >> >>
>> >> >> >> "Sunny" <[email protected]> wrote in message
>> >> >> >> news:[email protected]...
>> >> >> >> > 316 is a constant number.
>> >> >> >> > It starts in A4 and can land anyplace right of W.
>> >> >> >> >
>> >> >> >> > "Biff" wrote:
>> >> >> >> >
>> >> >> >> >> Ok.....
>> >> >> >> >>
>> >> >> >> >> So you want to extract data where there is a value greater
>> >> >> >> >> than
>> >> >> >> >> 0
>> >> >> >> >> in
>> >> >> >> >> *BOTH*
>> >> >> >> >> Sales and Tax?
>> >> >> >> >>
>> >> >> >> >> Is the size of the table always the same? Always 4c x 316r ?
>> >> >> >> >>
>> >> >> >> >> What is the cell address where the table starts? For example,
>> >> >> >> >> based
>> >> >> >> >> on
>> >> >> >> >> you
>> >> >> >> >> sample Smith appears in cell A2. The reason I "need" to know
>> >> >> >> >> this
>> >> >> >> >> is
>> >> >> >> >> because
>> >> >> >> >> the formula will contain an expression based on this starting
>> >> >> >> >> cell.
>> >> >> >> >> A
>> >> >> >> >> lot
>> >> >> >> >> of
>> >> >> >> >> times a poster will copy the formula from a reply and change
>> >> >> >> >> this
>> >> >> >> >> expression
>> >> >> >> >> incorrectly and that ends up causing the formula to not work
>> >> >> >> >> properly
>> >> >> >> >> leading to a bunch of follow-up questions.
>> >> >> >> >>
>> >> >> >> >> This should be no problem.
>> >> >> >> >>
>> >> >> >> >> Biff
>> >> >> >> >>
>> >> >> >> >> "Sunny" <[email protected]> wrote in message
>> >> >> >> >> news:[email protected]...
>> >> >> >> >> >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
>> >> >> >> >> >
>> >> >> >> >> > "Biff" wrote:
>> >> >> >> >> >
>> >> >> >> >> >> Hi!
>> >> >> >> >> >>
>> >> >> >> >> >> How about explaining exactly what you want to do (include
>> >> >> >> >> >> the
>> >> >> >> >> >> DETAILS!)
>> >> >> >> >> >> and
>> >> >> >> >> >> if I can figure it out I'll post a fresh link with a
>> >> >> >> >> >> sample.
>> >> >> >> >> >>
>> >> >> >> >> >> Biff
>> >> >> >> >> >>
>> >> >> >> >> >> "Sunny" <[email protected]> wrote in message
>> >> >> >> >> >> news:[email protected]...
>> >> >> >> >> >> > This is exactly what I would liek to do! However, the
>> >> >> >> >> >> > link
>> >> >> >> >> >> > no
>> >> >> >> >> >> > longer
>> >> >> >> >> >> > works.
>> >> >> >> >> >> > Would you please send it again?
>> >> >> >> >> >> >
>> >> >> >> >> >> > Thanks-
>> >> >> >> >> >> > sunny
>> >> >> >> >> >> >
>> >> >> >> >> >> > "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" <[email protected]> wrote in message
>> >> >> >> >> >> >> news:[email protected]...
>> >> >> >> >> >> >> > >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"
>> >> >> >> >> >> >> > <[email protected]>
>> >> >> >> >> >> >> > wrote
>> >> >> >> >> >> >> > in
>> >> >> >> >> >> >> > message
>> >> >> >> >> >> >> > news:[email protected]...
>> >> >> >> >> >> >> >> 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"
>> >> >> >> >> >> >> >>> <[email protected]>
>> >> >> >> >> >> >> >>> wrote
>> >> >> >> >> >> >> >>> in
>> >> >> >> >> >> >> >>> message
>> >> >> >> >> >> >> >>> news:[email protected]...
>> >> >> >> >> >> >> >>> > Hi. Is there a way to list, on a separate sheet,
>> >> >> >> >> >> >> >>> > only
>> >> >> >> >> >> >> >>> > rows
>> >> >> >> >> >> >> >>> > of
>> >> >> >> >> >> >> >>> > data
>> >> >> >> >> >> >> >>> > with a
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks