once I saved the file to my computer the formula worked.

Yeah, that's how that formula works. I had assumed the file already existed.
A file doesn't exist until it's saved with a file name and the formula
parses the file name to get the sheet name. If there's no file name then the
formula errors.

Ok......

We can probably get this to work based on either a store number or a project
number. I'd need to when/how a project number is assigned a store number.
For example, if column A is for the store # and column ?? contains the
project number, I assume the store number cell is empty until the project is
completed then the store # is assigned. So, does that mean there is a sheet
for that project number? If so, then I also assume that once the store # is
assigned you then change the sheet name from the project number to the store
# ? Once the project is completed and a store # is assigned does that mean
you go back to all the empty cells in column A for that project and then
fill in a store # ?

Let me know!

Biff

"Rob" wrote in message
...
Thanks BIFF !
Disregad my last message - once I saved the file to my computer the
formula
worked.

Last question - would it be possible to return values if searching for two
pieces of criteria instead of one ? In our example, we were looking at a
particular store (#2885) - can we incorporate this number, and another
number
?

When we are creating new stores, we assign a project number at inception.
Once the store opens, its assigned a store number - therefore we can have
data in both the project number and store number.

Can the formula you provided me with incorporate two numbers ?

=IF(ROWS($1:1)<=$B$1,INDEX(Dump!B$2:B$5,SMALL(IF(D ump!$A$2:$A$5=$A$1&A2+0,ROW(Dump!B$2:B$5)-ROW(Dump!B$2)+1),ROWS($1:1))),"")

THANKS!
Rob

"Dave Peterson" wrote:

You may want to look at the way Ron de Bruin and Debra Dalgleish
approached it:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list;
macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

Rob wrote:

Hello,

Heres my issue - I have a single piece of criteria (ie - Store #), and
given
this, I need to look at a large dump of data, and return all the values
for
this particular store number.

Example :
Here is the data dump :
A B C D
Store # Account # Date Amount
1 2885 120000 05/17/06 100.00
2 2950 130405 06/17/06 50.00
3 2885 130402 04/20/04 200.00
4 2950 126210 08/17/05 50.00

Now, If I am running a report on Store #2885 - what I need the formula
to do
is give me all pieces of information that relate to that store. Here
is what
the end result of the formula should be :

Store # Account # Date Amount
2885 120000 05/17/06 100.00
2885 130402 04/20/04 200.00

Can anybody help me ?

Thanks!
Rob

--

Dave Peterson