I would guess that you're now a victim of "word wrap".

Try this:
Copy the formula,
Click in A3,
Click in the formula bar,
Right click in the formula bar,
Choose "Paste",

If you now see the expanded formula bar with the formula displayed on
multiple lines, *AND* you see that each line of the formula *doesn't* appear
to fill all of the lines, with a lot of space at the end of each line, you
know you've got wrapping.

You can try to click at the end of a line and hit <Delete, but you've got
to make sure that you don't delete characters *OR* spaces.

You might have to go the manual route to enter the formula.
THEN, try <F2 and the CSE.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"DakotaNJ" wrote in message
...
Ok, RD. That stopped the error. Problem is, I can not seem to get curly
brackets anymore.

I copy the formula from this message, paste it into the A3 cell, perform
CSE
and I get #VALUE as a result, but no curly brackets.

When I was copying it in prior to this latest formula, I could get curly
brackets from CSE. Of course I also got File Not Found too.

Am I just an idiot and I'm missing a step somewhere?

This seems to have moved from a curiosity to a passion for me. I'm
determined to get this thing right so all your wonderful help is not
wasted!

Regards,
Dominick
--
Learning and growing everyday.


"Ragdyer" wrote:

Try this:

=IF(COUNTIF('Summary FNb
2005'!$F$5:$F$154,$A$1)=ROWS($1:1),INDEX('Summary
FNb 2005'!A$5:A$154,SMALL(IF('Summary FNb
2005'!$F$5:$F$154=$A$1,ROW($1:$150)),ROW(1:1))),"" )

Don't forget to use CSE to enter it.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"DakotaNJ" wrote in message
...
OK, sorry about that RD.

The EXACT names a

Incident statistics 2006.xls (The Workbook)
Summary FNb 2005 (Main spreadsheet)
Summary FNbG (sub sheet)
Summary FNb-C (sub sheet)
Summary FNbH-Range (sub sheet)
Summary FNbW (sub sheet)
Summary HAP (sub sheet)

The F-column (Divission name) values a
FNbG
FNbH-C
FNbH-R
FNbW
HAP

Each of these values are the name of the respective sub-sheet, located
in
cell A1 of each sheet.

Yes, the names of the sub sheets exactly match what I enter in the
F-column
of the "Summary FNb 2005" sheet.

I use cell A3 of each sub sheet to enter the formula.

I can't think of any other variables. I see the methodology pretty
clearly,
just need to tweak the formula to make it work.

As always, thanks so much for your excellent help!

Regards,
Dominick






--
Learning and growing everyday.


"RagDyer" wrote:

It seems that each new post of yours divulges *new* information or
*changes*
information previously stated.
OR ... you're making a bunch of typos!

What is the *exact* name of your MAIN sheet, including spaces?
Are you *sure* that there are *no* spaces at the beginning and/or the
end of
the name?

The "File Not Found" window is coming up because the sheet name in
the
formula *does not* match ANY sheet in the workbook.
That can also give you a #VALUE! error when you by-pass it.

Are you entering the name of the sub sheet in *A1* or *A3* of each
sub
sheet? ? ?

Do some of the names of the sub sheets contain dashes (Summary
FNb-C),
and
some don't (Summary FNbW)?
And if they are mixed formats, do they *exactly* match what you have
entered
on the Main sheet in Column F?

--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!

--------------------------------------------------------------------------
-

"DakotaNJ" wrote in message
...
AARRGGGHHHH!!!!!!

I'm somehow missing something.

I was able to copy the formula into the A:3 cells of each
spreadsheet,
I
CSE'd them, got a #VALUE response in each of those cells. I copied
that
cell
into a row across the spreadsheet, then copied that row down
through
all
rows. I get a #VALUE in each of those cells.

I go to the main FNb spreadsheet, enter a row of information and
nothing.

Now, my "main" (the source of the data) is actually called: Summary
FNb
2006, each of the sub-spreadsheets is called Summary FNbW, or
Summary
FNb-C
(divisions within the FNb organization), etc. These are the names
of
each
sub-spreadsheet and the exact name that is located in the A:3 cell
of
each
sub-sheet.

I tried to modify the formula, changing "FNb" to "Summary FNb 2006"
in
all
instances where necessary. Problem is, when I CSE it it is looking
for a
file and a window opens "File Not Found", so I try to route to the
file
location, and the file is not there!?! In fact it won't list out
any
of
the
Excel files. So, I saved the file to Desktop and tried again,
still
won't
list it even though it is located there and I am looking for the
correct
file
extension. Very frustrating! What am I doing wrong here?

I surmise, without the file look up location it has no idea where
it
is
looking?

This would be so much easier if there was a way to show you the
spreadsheet.
Then perhaps we would be discussing the same exact thing.

How you even answer these questions purely based on text messages
is
beyond
me. I bow to your superior intellect. Heck, I get confused just
trying
to
explain it to you. LOL

Please, pardon my ignorance and see if you can figure out what I am
doing
wrong.

With much appreciation.
Dominick
--
Learning and growing everyday...errr, staggering and falling down
everday
right now!


"RagDyeR" wrote:

Here's your revised formula to use.



=IF(COUNTIF(FNb!$F$5:$F$154,$A$1)=ROWS($1:1),INDE X(FNb!A$5:A$154,SMALL(IF(F
Nb!$F$5:$F$154=$A$1,ROW($1:$150)),ROW(1:1))),"")

Don't forget to use CSE!

"=ROWS($1:1)"
Is just a counter that increments as it's copied down.
It's part of the COUNTIF() error trap, so that you won't receive
#NUM!
errors when your formula runs out of matching rows on the FNb
sheet.

You see that I changed:
"ROW($1:$100)"
TO
"ROW($1:$150)"
That is the total number of rows in the datalist - (F5:F154)


You can enter the formula anywhere you wish on each of the "sub"
sheets.
You must then copy that formula (*after* a CSE entry), from it's
original
cell location, across 11 columns,
so that you have a total of 12 columns of formula, covering the
12
columns
of the original datalist on Sheet FNb.

You then select *all* 12 columns of formula,
And copy down as many rows as you want (need).

Not to confuse you, but you can do this all in "one shot".

You can group all your "sub" sheets together,
so that you paste, and CSE enter, and drag and copy your formula
only
once,
and it'll be duplicated on all the sheets in the group.

Select the first "sub" sheet tab, hold <Ctrl, and click in each
sheet
tab
that you wish to have in the "group", (*not* the main FNb sheet).
These tabs now are colored white, and the word "Group" is
appended
to
the
name in the Title Bar.

Now, whatever you do to one, is automatically done to all the
others
in
the
group.

When you're finished, to "Ungroup", just click in the tab of your
FNb
sheet.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may
benefit!
================================================== ===

"DakotaNJ" wrote in message
...
Thanks RD, I am beginning to "see" the solution, but admit this
is
all
very
foreign to me. So, using the array formula you provided, I was
able
to
break
it down and modify it as necessary, but I still cannot understand
a
few
things. Please bear with me here.

I have modified the formula to fit specific cell values as
follows:

YOUR VERSION:


=IF(COUNTIF(Main!$F$1:$F$100,$A$1)=ROWS($1:1),IND EX(Main!A$1:A$100,SMALL(IF
(Main!$F$1:$F$100=$A$1,ROW($1:$100)),ROW(1:1))),"" )

MY VERSION:


=IF(COUNTIF(FNb!$F$5:$F$154,$A$1)=ROWS($1:1),INDE X(FNb!A$5:A$154,SMALL(IF(F
Nb!$F$5:$F$154=$A$1,ROW($1:$100)),ROW(1:1))),"")

OK, so "Main" now equals my main spreadsheet name "FNb"

"F1:F100" now equals the range of cells to evaluate for a match,
that is
F5:F154 (this is where the DIV # is stored on the main
spreadsheet).

A1 is the "label" of the lower spreadsheets. In this case the
spreadsheet
is called FNbW and that value is located in A1 of the lower
spreadsheet
(as
it is for all spreadsheets)

I'm not sure what "=ROWS($1:1)" is?? Can you explain this so I
can
be
sure
the formula here is correct?

As I understand this, using my specific labels, when I enter my
information
in a row on the FNb spreadsheet, where the F-column entry equals
"FNbW",
then
the FNbW spreadsheet will recognize it and copy the entire row
from
the
FNb
spreadsheet onto the FNbW spreadsheet.

Is that the methodology?