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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
thanks!
"Biff" wrote:
Ok, no problem!
I'll be away from the computer for a few hours so check back later.
Biff
"Sunny" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Would it still be practical if I had 250 rows of
data
total
and
needed to pull in 10 columns for only about 20-40
rows?
That should be Ok but you'll have to see how it
affects
performance
and
then make that determination for yourself.
I'm going to break for dinner but I'll put together a
sample
file
that
demonstrates this (unless Max beats me to it!<g) and
post
a
link
to
the
file.
Biff
"Rikki-Handgards"
wrote
in
message
...
Thanks Biff. Would it still be practical if I had
250
rows
of
data
total
and
needed to pull in 10 columns for only about 20-40
rows?
"Biff" wrote:
Is there a way to do this with a formula and not a
pivot
table?
Yes, but whether or not it's practical depends on
how
many
cells
from
the
row you need returned and in general, how big is
the
table
of
data
this
data
is being extracted from.
For example, if your table is 50,000 rows by 150
columns
and
you
need
all
150 columns of data extracted for each instance of
"value",
then
a
formula
approach is not practical.
Biff
"Rikki-Handgards"
wrote
in
message
...
Hi. Is there a way to list, on a separate sheet,
only
rows
of
data
with a