Is it possible?
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?
Feels like I am relatively close to getting this to work. I was
able to
enter the array into cell A:3 (is this cell specific or just for
example?).
I was able to CSE the array and get curly brackets.
I was not able to understand the instruction from there. Where do I
copy it
to? And what range of cells do I use? I need to copy a row into a
row
of
the other spreadsheet. So, as you can see, I'm still confused here.
Thanks for your time. You should get an award for tolerating these
questions!
Regards,
Dominick
--
Learning and growing everyday.
"Ragdyer" wrote:
Assume ... your "Main" sheet datalist starts with labels in Row1,
And goes from A1 to L100.
*All other* sheets have the Div # in A1,
So, labels in Row2, *exactly* matching the column labels in
"Main".
Enter this *array* formula in A3 of *all* your other sheets:
=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))),"" )
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter,
instead
of
the regular <Enter, which will *automatically* enclose the
formula in
curly
brackets, which *cannot* be done manually.
Now, after the CSE entry, copy this formula across to L3.
Then, select A3 to L3, and drag down to copy as far as needed.
--
HTH,
RD
--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit !
--------------------------------------------------------------------------
-
"DakotaNJ" wrote in message
...
OK, let me see if I can make this clear enough:
On the Main sheet it looks like this. These are in-plant
accidents
that
I
investigate and track.
Acc#: Carr: Invest: Name: Dept: Div: Date: FDO: LT:
RD:
Injury:
Part:
1 XXX Yes Sam AM 104 1-3 1-3 0
0
C X
The "other" tabs are specific to building (Div.). So, when I
enter
a
line
item on the main spreadsheet, I need Excel to identify it using
the
DIV
cell
(in this case 104), then copy the entire line of info to the
"104"
spreadsheet (or, obviously whichever on is applicable), which is
exactly
the
same set-up as the main spreadsheet.
Right now, when I enter a line on the Master sheet, I simply
copy/paste
it
to the "other" sheet.
You folks are getting me pretty excited! I'm starting to think
this
may
be
possible.
This whole workbook is really coming along nicely. I have
figured
out
|