#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DakotaNJ
 
Posts: n/a
Default Is it possible?

I have a workbook with several tabs. The top tab is the main spreadsheet
were I enter all my gathered data. The other tabs are the same spreadsheet,
the same data, but broken out by specific building.

Right now, I enter the data (alpha and numeric) into the main spreadsheet,
then manually copy/paste it to the respective sub-level spreadsheet.

I can specify the "IF" part of the line item that would identify which sheet
to copy it to, but is it possible to have a "THEN copy and paste [these
cells]" to the other spreadsheets?

Perhaps I am just dreaming here. However, looking through archives I have
found solutions to so many things, and learned so much, I figure what the
heck, I might as well ask!

Thanks for all the great info you folks pass around here. It has helped
make me a "star" employee and allowed me to do some things no one even
thought was possible.
--
Learning and growing everyday.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
PY & Associates
 
Posts: n/a
Default Is it possible?

Absolutely possible if you can define what and how you want to do it
manually.

"DakotaNJ" wrote in message
...
I have a workbook with several tabs. The top tab is the main spreadsheet
were I enter all my gathered data. The other tabs are the same

spreadsheet,
the same data, but broken out by specific building.

Right now, I enter the data (alpha and numeric) into the main spreadsheet,
then manually copy/paste it to the respective sub-level spreadsheet.

I can specify the "IF" part of the line item that would identify which

sheet
to copy it to, but is it possible to have a "THEN copy and paste [these
cells]" to the other spreadsheets?

Perhaps I am just dreaming here. However, looking through archives I have
found solutions to so many things, and learned so much, I figure what the
heck, I might as well ask!

Thanks for all the great info you folks pass around here. It has helped
make me a "star" employee and allowed me to do some things no one even
thought was possible.
--
Learning and growing everyday.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ken Johnson
 
Posts: n/a
Default Is it possible?

Hi DakotaNJ,
here's a trivial example which looks at the value in A1 on Sheet1 and
if it is greater than 10 places that value into A1 on Sheet2, otherwise
the value in A1 on Sheet2 is not changed.

Public Sub paste_If()
If Sheet1.Cells(1, 1) 10 Then
Sheet2.Cells(1, 1).Value = Sheet1.Cells(1, 1)
End If
End Sub

When trying out code make sure you have a back up copy of the workbook.
The effects of code are NOT undo-able.

Ken Johnson

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Dibben
 
Posts: n/a
Default Is it possible?

Dakota

A formula can only return values to the cell in which it resides. It cannot
"push" data to another cell.

Maybe you can link the sub sheets to the master?

On Sheet2 A1 enter =Master!G23

When you update Master G23 then Sheet2 A1 will update also.

If you truly want to "push" data from one range/sheet to another you would
have to use VBA code/macros.

Similar to...........

Sub Pu****()
Sheets("Master").Range("A1:J10").Copy _
Destination:=Sheets("Sheet2").Range("K43")
End Sub

OR same thing using values.

Sub Valueit()
Sheets("Sheet2").Range("K43").Resize(10, 10).Value = _
Worksheets("Master").Range("A1:J10").Value
End Sub


Gord Dibben Excel MVP

On Sat, 10 Dec 2005 06:12:40 +0800, "PY & Associates"
wrote:

Absolutely possible if you can define what and how you want to do it
manually.

"DakotaNJ" wrote in message
...
I have a workbook with several tabs. The top tab is the main spreadsheet
were I enter all my gathered data. The other tabs are the same

spreadsheet,
the same data, but broken out by specific building.

Right now, I enter the data (alpha and numeric) into the main spreadsheet,
then manually copy/paste it to the respective sub-level spreadsheet.

I can specify the "IF" part of the line item that would identify which

sheet
to copy it to, but is it possible to have a "THEN copy and paste [these
cells]" to the other spreadsheets?

Perhaps I am just dreaming here. However, looking through archives I have
found solutions to so many things, and learned so much, I figure what the
heck, I might as well ask!

Thanks for all the great info you folks pass around here. It has helped
make me a "star" employee and allowed me to do some things no one even
thought was possible.
--
Learning and growing everyday.


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
RagDyer
 
Posts: n/a
Default Is it possible?

If I understand what you're looking for, it should be relatively easy and
uncomplicated.

Depending on the configuration of the data on your main sheet, you could use
either a Vlookup() function or an Index & Match combination.

You would fill your "sub" sheets with these functions to pull the data from
the main sheet, where the building ID would be the main criteria determining
which sub sheet would be able to pull the data.

This assumes that one of the fields on the main sheet *does* contain the
individual building ID.

Post back with how your main sheet is set-up, and I'm sure you'll get what
you're looking for.
--
Regards,

RD

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

"DakotaNJ" wrote in message
...
I have a workbook with several tabs. The top tab is the main spreadsheet
were I enter all my gathered data. The other tabs are the same

spreadsheet,
the same data, but broken out by specific building.

Right now, I enter the data (alpha and numeric) into the main spreadsheet,
then manually copy/paste it to the respective sub-level spreadsheet.

I can specify the "IF" part of the line item that would identify which

sheet
to copy it to, but is it possible to have a "THEN copy and paste [these
cells]" to the other spreadsheets?

Perhaps I am just dreaming here. However, looking through archives I have
found solutions to so many things, and learned so much, I figure what the
heck, I might as well ask!

Thanks for all the great info you folks pass around here. It has helped
make me a "star" employee and allowed me to do some things no one even
thought was possible.
--
Learning and growing everyday.




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DakotaNJ
 
Posts: n/a
Default Is it possible?

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 how
to pull key figures from one sheet to another, then graph the figures in yet
another. I'm just having a mental block with this final function, which
would allow the workbook to do all the actual work, leaving me to simply
enter the initial information.

Thanks for all your input!

Regards,
Dominick

--
Learning and growing everyday.


"RagDyer" wrote:

If I understand what you're looking for, it should be relatively easy and
uncomplicated.

Depending on the configuration of the data on your main sheet, you could use
either a Vlookup() function or an Index & Match combination.

You would fill your "sub" sheets with these functions to pull the data from
the main sheet, where the building ID would be the main criteria determining
which sub sheet would be able to pull the data.

This assumes that one of the fields on the main sheet *does* contain the
individual building ID.

Post back with how your main sheet is set-up, and I'm sure you'll get what
you're looking for.
--
Regards,

RD

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

"DakotaNJ" wrote in message
...
I have a workbook with several tabs. The top tab is the main spreadsheet
were I enter all my gathered data. The other tabs are the same

spreadsheet,
the same data, but broken out by specific building.

Right now, I enter the data (alpha and numeric) into the main spreadsheet,
then manually copy/paste it to the respective sub-level spreadsheet.

I can specify the "IF" part of the line item that would identify which

sheet
to copy it to, but is it possible to have a "THEN copy and paste [these
cells]" to the other spreadsheets?

Perhaps I am just dreaming here. However, looking through archives I have
found solutions to so many things, and learned so much, I figure what the
heck, I might as well ask!

Thanks for all the great info you folks pass around here. It has helped
make me a "star" employee and allowed me to do some things no one even
thought was possible.
--
Learning and growing everyday.



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ragdyer
 
Posts: n/a
Default Is it possible?

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,R OW($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 how
to pull key figures from one sheet to another, then graph the figures in
yet
another. I'm just having a mental block with this final function, which
would allow the workbook to do all the actual work, leaving me to simply
enter the initial information.

Thanks for all your input!

Regards,
Dominick

--
Learning and growing everyday.


"RagDyer" wrote:

If I understand what you're looking for, it should be relatively easy and
uncomplicated.

Depending on the configuration of the data on your main sheet, you could
use
either a Vlookup() function or an Index & Match combination.

You would fill your "sub" sheets with these functions to pull the data
from
the main sheet, where the building ID would be the main criteria
determining
which sub sheet would be able to pull the data.

This assumes that one of the fields on the main sheet *does* contain the
individual building ID.

Post back with how your main sheet is set-up, and I'm sure you'll get
what
you're looking for.
--
Regards,

RD

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

"DakotaNJ" wrote in message
...
I have a workbook with several tabs. The top tab is the main
spreadsheet
were I enter all my gathered data. The other tabs are the same

spreadsheet,
the same data, but broken out by specific building.

Right now, I enter the data (alpha and numeric) into the main
spreadsheet,
then manually copy/paste it to the respective sub-level spreadsheet.

I can specify the "IF" part of the line item that would identify which

sheet
to copy it to, but is it possible to have a "THEN copy and paste [these
cells]" to the other spreadsheets?

Perhaps I am just dreaming here. However, looking through archives I
have
found solutions to so many things, and learned so much, I figure what
the
heck, I might as well ask!

Thanks for all the great info you folks pass around here. It has
helped
make me a "star" employee and allowed me to do some things no one even
thought was possible.
--
Learning and growing everyday.




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DakotaNJ
 
Posts: n/a
Default Is it possible?

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,R OW($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(FNb!$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,R OW($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 how
to pull key figures from one sheet to another, then graph the figures in
yet
another. I'm just having a mental block with this final function, which
would allow the workbook to do all the actual work, leaving me to simply
enter the initial information.

Thanks for all your input!

Regards,
Dominick

--
Learning and growing everyday.


"RagDyer" wrote:

If I understand what you're looking for, it should be relatively easy and
uncomplicated.

Depending on the configuration of the data on your main sheet, you could
use
either a Vlookup() function or an Index & Match combination.

You would fill your "sub" sheets with these functions to pull the data
from
the main sheet, where the building ID would be the main criteria
determining
which sub sheet would be able to pull the data.

This assumes that one of the fields on the main sheet *does* contain the
individual building ID.

Post back with how your main sheet is set-up, and I'm sure you'll get
what
you're looking for.
--
Regards,

RD

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

"DakotaNJ" wrote in message
...
I have a workbook with several tabs. The top tab is the main
spreadsheet
were I enter all my gathered data. The other tabs are the same
spreadsheet,
the same data, but broken out by specific building.

Right now, I enter the data (alpha and numeric) into the main
spreadsheet,
then manually copy/paste it to the respective sub-level spreadsheet.

I can specify the "IF" part of the line item that would identify which
sheet
to copy it to, but is it possible to have a "THEN copy and paste [these
cells]" to the other spreadsheets?

Perhaps I am just dreaming here. However, looking through archives I
have
found solutions to so many things, and learned so much, I figure what
the
heck, I might as well ask!

Thanks for all the great info you folks pass around here. It has
helped
make me a "star" employee and allowed me to do some things no one even
thought was possible.
--
Learning and growing everyday.




  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
RagDyeR
 
Posts: n/a
Default Is it possible?

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

how
to pull key figures from one sheet to another, then graph the figures in
yet
another. I'm just having a mental block with this final function, which
would allow the workbook to do all the actual work, leaving me to simply
enter the initial information.

Thanks for all your input!

Regards,
Dominick

--
Learning and growing everyday.


"RagDyer" wrote:

If I understand what you're looking for, it should be relatively easy

and
uncomplicated.

Depending on the configuration of the data on your main sheet, you

could
use
either a Vlookup() function or an Index & Match combination.

You would fill your "sub" sheets with these functions to pull the data
from
the main sheet, where the building ID would be the main criteria
determining
which sub sheet would be able to pull the data.

This assumes that one of the fields on the main sheet *does* contain

the
individual building ID.

Post back with how your main sheet is set-up, and I'm sure you'll get
what
you're looking for.
--
Regards,

RD


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

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

!

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

--

"DakotaNJ" wrote in message
...
I have a workbook with several tabs. The top tab is the main
spreadsheet
were I enter all my gathered data. The other tabs are the same
spreadsheet,
the same data, but broken out by specific building.

Right now, I enter the data (alpha and numeric) into the main
spreadsheet,
then manually copy/paste it to the respective sub-level spreadsheet.

I can specify the "IF" part of the line item that would identify

which
sheet
to copy it to, but is it possible to have a "THEN copy and paste

[these
cells]" to the other spreadsheets?

Perhaps I am just dreaming here. However, looking through archives I
have
found solutions to so many things, and learned so much, I figure what
the
heck, I might as well ask!

Thanks for all the great info you folks pass around here. It has
helped
make me a "star" employee and allowed me to do some things no one

even
thought was possible.
--
Learning and growing everyday.






  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DakotaNJ
 
Posts: n/a
Default Is it possible?

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

how
to pull key figures from one sheet to another, then graph the figures in
yet
another. I'm just having a mental block with this final function, which
would allow the workbook to do all the actual work, leaving me to simply
enter the initial information.

Thanks for all your input!

Regards,
Dominick

--
Learning and growing everyday.


"RagDyer" wrote:

If I understand what you're looking for, it should be relatively easy

and
uncomplicated.

Depending on the configuration of the data on your main sheet, you

could
use
either a Vlookup() function or an Index & Match combination.

You would fill your "sub" sheets with these functions to pull the data
from
the main sheet, where the building ID would be the main criteria
determining
which sub sheet would be able to pull the data.

This assumes that one of the fields on the main sheet *does* contain

the
individual building ID.

Post back with how your main sheet is set-up, and I'm sure you'll get
what
you're looking for.
--
Regards,

RD


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

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

!

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

--

"DakotaNJ" wrote in message
...
I have a workbook with several tabs. The top tab is the main
spreadsheet
were I enter all my gathered data. The other tabs are the same
spreadsheet,
the same data, but broken out by specific building.

Right now, I enter the data (alpha and numeric) into the main
spreadsheet,
then manually copy/paste it to the respective sub-level spreadsheet.

I can specify the "IF" part of the line item that would identify

which
sheet
to copy it to, but is it possible to have a "THEN copy and paste

[these
cells]" to the other spreadsheets?

Perhaps I am just dreaming here. However, looking through archives I
have
found solutions to so many things, and learned so much, I figure what
the
heck, I might as well ask!

Thanks for all the great info you folks pass around here. It has
helped
make me a "star" employee and allowed me to do some things no one

even
thought was possible.
--
Learning and growing everyday.









  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
RagDyer
 
Posts: n/a
Default Is it possible?

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
how
to pull key figures from one sheet to another, then graph the

figures in
yet
another. I'm just having a mental block with this final function,

which
would allow the workbook to do all the actual work, leaving me to

simply
enter the initial information.

Thanks for all your input!

Regards,
Dominick

--
Learning and growing everyday.


"RagDyer" wrote:

If I understand what you're looking for, it should be relatively

easy
and
uncomplicated.

Depending on the configuration of the data on your main sheet, you

could
use
either a Vlookup() function or an Index & Match combination.

You would fill your "sub" sheets with these functions to pull the

data
from
the main sheet, where the building ID would be the main criteria
determining
which sub sheet would be able to pull the data.

This assumes that one of the fields on the main sheet *does*

contain
the
individual building ID.

Post back with how your main sheet is set-up, and I'm sure you'll

get
what
you're looking for.
--
Regards,

RD



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

benefit
!


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

"DakotaNJ" wrote in message
...
I have a workbook with several tabs. The top tab is the main
spreadsheet
were I enter all my gathered data. The other tabs are the same
spreadsheet,
the same data, but broken out by specific building.

Right now, I enter the data (alpha and numeric) into the main
spreadsheet,
then manually copy/paste it to the respective sub-level

spreadsheet.

I can specify the "IF" part of the line item that would identify

which
sheet
to copy it to, but is it possible to have a "THEN copy and paste

[these
cells]" to the other spreadsheets?

Perhaps I am just dreaming here. However, looking through

archives I
have
found solutions to so many things, and learned so much, I figure

what
the
heck, I might as well ask!

Thanks for all the great info you folks pass around here. It has
helped
make me a "star" employee and allowed me to do some things no one

even
thought was possible.
--
Learning and growing everyday.








  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DakotaNJ
 
Posts: n/a
Default Is it possible?

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

  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ragdyer
 
Posts: n/a
Default 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


  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DakotaNJ
 
Posts: n/a
Default Is it possible?

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?

  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ragdyer
 
Posts: n/a
Default Is it possible?

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?




  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DakotaNJ
 
Posts: n/a
Default Is it possible?

Hello RD-

Thanks again for your continued help.

After much struggling I was able to enter the formula into the formula bar,
manually seemed to work better.

Here is the present formula (I changed 2005 to 2006 in order to play with a
copy of the original, this would be next years spreadsheet)

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

Wordwrap was indeed a problem which I was able to correct.

Although I have performed CSE, I do not see the curly brackets in the
formula and
I receive a #VALUE in the A3 cell.

Oddly, it seems all the cells in the spreadsheet are reacting to the formula
in that when I click on a cell within the spreadsheet, it seems to contain
the formula and the ROW reference changes accordingly. Have I done something
wrong?

It seems that when I click on any cell within A6:O45 it shows the formula in
the bar and the ROW reference changes. I had tried to use F2 then CSE, it
took my PC several minutes to execute this and then I received a "0" in cells
in the A column. Since this was not acceptable, I reversed this action.
Perhaps it did not reverse?

I still cannot enter data on the main sheet and have it post to the
sub-sheet. Nothing happens.

Where do I go from here?

Regards,
Dominick



--
Learning and growing everyday.


"Ragdyer" wrote:

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.

  #17   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DakotaNJ
 
Posts: n/a
Default Is it possible?

OK, I cleared the spreadsheet cell contents. Cleared the formula. Closed
out the program. Reopened and re-entered the formula. Still can't get curly
brackets though. No matter how I try (enter into cell or enter into formula
bar).

Regards,
Dominick

--
Learning and growing everyday.


"DakotaNJ" wrote:

Hello RD-

Thanks again for your continued help.

After much struggling I was able to enter the formula into the formula bar,
manually seemed to work better.

Here is the present formula (I changed 2005 to 2006 in order to play with a
copy of the original, this would be next years spreadsheet)

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

Wordwrap was indeed a problem which I was able to correct.

Although I have performed CSE, I do not see the curly brackets in the
formula and
I receive a #VALUE in the A3 cell.

Oddly, it seems all the cells in the spreadsheet are reacting to the formula
in that when I click on a cell within the spreadsheet, it seems to contain
the formula and the ROW reference changes accordingly. Have I done something
wrong?

It seems that when I click on any cell within A6:O45 it shows the formula in
the bar and the ROW reference changes. I had tried to use F2 then CSE, it
took my PC several minutes to execute this and then I received a "0" in cells
in the A column. Since this was not acceptable, I reversed this action.
Perhaps it did not reverse?

I still cannot enter data on the main sheet and have it post to the
sub-sheet. Nothing happens.

Where do I go from here?

Regards,
Dominick



--
Learning and growing everyday.


"Ragdyer" wrote:

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))),"")

  #18   Report Post  
Posted to microsoft.public.excel.worksheet.functions
RagDyeR
 
Posts: n/a
Default Is it possible?

With the formula entered into a cell, and *after* the "wrap" is eliminated,
you must be in *edit* mode for the CSE to work.

That means, you must *either* click in the formula bar,
OR
hit <F2 when the cell with the formula is selected.

You can tell when you're in "edit" mode by looking at what's displayed in
the status bar.

Click in a cell containing the formula.
The status bar displays "Ready".
Change it to "Edit" by doing either of the above,
THEN ... CSE

Does that work now?
--

Regards,

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

"DakotaNJ" wrote in message
...
OK, I cleared the spreadsheet cell contents. Cleared the formula. Closed
out the program. Reopened and re-entered the formula. Still can't get
curly
brackets though. No matter how I try (enter into cell or enter into formula
bar).

Regards,
Dominick

--
Learning and growing everyday.


"DakotaNJ" wrote:

Hello RD-

Thanks again for your continued help.

After much struggling I was able to enter the formula into the formula

bar,
manually seemed to work better.

Here is the present formula (I changed 2005 to 2006 in order to play with

a
copy of the original, this would be next years spreadsheet)

=IF(COUNTIF('Summary FNb

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

Wordwrap was indeed a problem which I was able to correct.

Although I have performed CSE, I do not see the curly brackets in the
formula and
I receive a #VALUE in the A3 cell.

Oddly, it seems all the cells in the spreadsheet are reacting to the

formula
in that when I click on a cell within the spreadsheet, it seems to contain
the formula and the ROW reference changes accordingly. Have I done

something
wrong?

It seems that when I click on any cell within A6:O45 it shows the formula

in
the bar and the ROW reference changes. I had tried to use F2 then CSE, it
took my PC several minutes to execute this and then I received a "0" in

cells
in the A column. Since this was not acceptable, I reversed this action.
Perhaps it did not reverse?

I still cannot enter data on the main sheet and have it post to the
sub-sheet. Nothing happens.

Where do I go from here?

Regards,
Dominick



--
Learning and growing everyday.


"Ragdyer" wrote:

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))),"")



  #19   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DakotaNJ
 
Posts: n/a
Default Is it possible?

Yes! We now have the formula located in the A3 cell (returned a value of
130?), and I copied that cell formula into the first row, then copied that
row down to the end of the spreadsheet. All cells are blank.

What next? I entered a line on the main spreadsheet and it didn't copy to
the sub-sheet?

Regards,
Dominick
--
Learning and growing everyday.


"RagDyeR" wrote:

With the formula entered into a cell, and *after* the "wrap" is eliminated,
you must be in *edit* mode for the CSE to work.

That means, you must *either* click in the formula bar,
OR
hit <F2 when the cell with the formula is selected.

You can tell when you're in "edit" mode by looking at what's displayed in
the status bar.

Click in a cell containing the formula.
The status bar displays "Ready".
Change it to "Edit" by doing either of the above,
THEN ... CSE

Does that work now?
--

Regards,

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

"DakotaNJ" wrote in message
...
OK, I cleared the spreadsheet cell contents. Cleared the formula. Closed
out the program. Reopened and re-entered the formula. Still can't get
curly
brackets though. No matter how I try (enter into cell or enter into formula
bar).

Regards,
Dominick

--
Learning and growing everyday.


"DakotaNJ" wrote:

Hello RD-

Thanks again for your continued help.

After much struggling I was able to enter the formula into the formula

bar,
manually seemed to work better.

Here is the present formula (I changed 2005 to 2006 in order to play with

a
copy of the original, this would be next years spreadsheet)

=IF(COUNTIF('Summary FNb

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

Wordwrap was indeed a problem which I was able to correct.

Although I have performed CSE, I do not see the curly brackets in the
formula and
I receive a #VALUE in the A3 cell.

Oddly, it seems all the cells in the spreadsheet are reacting to the

formula
in that when I click on a cell within the spreadsheet, it seems to contain
the formula and the ROW reference changes accordingly. Have I done

something
wrong?

It seems that when I click on any cell within A6:O45 it shows the formula

in
the bar and the ROW reference changes. I had tried to use F2 then CSE, it
took my PC several minutes to execute this and then I received a "0" in

cells
in the A column. Since this was not acceptable, I reversed this action.
Perhaps it did not reverse?

I still cannot enter data on the main sheet and have it post to the
sub-sheet. Nothing happens.

Where do I go from here?

Regards,
Dominick



--
Learning and growing everyday.


"Ragdyer" wrote:

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.

  #20   Report Post  
Posted to microsoft.public.excel.worksheet.functions
RagDyer
 
Posts: n/a
Default Is it possible?

I'm sending you my test WB so you can see exactly what I've been describing
to you.

Let me know if you receive it and understand it.
--
Regards,

RD

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

"DakotaNJ" wrote in message
...
Yes! We now have the formula located in the A3 cell (returned a value of
130?), and I copied that cell formula into the first row, then copied that
row down to the end of the spreadsheet. All cells are blank.

What next? I entered a line on the main spreadsheet and it didn't copy to
the sub-sheet?

Regards,
Dominick
--
Learning and growing everyday.


"RagDyeR" wrote:

With the formula entered into a cell, and *after* the "wrap" is

eliminated,
you must be in *edit* mode for the CSE to work.

That means, you must *either* click in the formula bar,
OR
hit <F2 when the cell with the formula is selected.

You can tell when you're in "edit" mode by looking at what's displayed

in
the status bar.

Click in a cell containing the formula.
The status bar displays "Ready".
Change it to "Edit" by doing either of the above,
THEN ... CSE

Does that work now?
--

Regards,

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

"DakotaNJ" wrote in message
...
OK, I cleared the spreadsheet cell contents. Cleared the formula.

Closed
out the program. Reopened and re-entered the formula. Still can't get
curly
brackets though. No matter how I try (enter into cell or enter into

formula
bar).

Regards,
Dominick

--
Learning and growing everyday.


"DakotaNJ" wrote:

Hello RD-

Thanks again for your continued help.

After much struggling I was able to enter the formula into the formula

bar,
manually seemed to work better.

Here is the present formula (I changed 2005 to 2006 in order to play

with
a
copy of the original, this would be next years spreadsheet)

=IF(COUNTIF('Summary FNb

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

Wordwrap was indeed a problem which I was able to correct.

Although I have performed CSE, I do not see the curly brackets in the
formula and
I receive a #VALUE in the A3 cell.

Oddly, it seems all the cells in the spreadsheet are reacting to the

formula
in that when I click on a cell within the spreadsheet, it seems to

contain
the formula and the ROW reference changes accordingly. Have I done

something
wrong?

It seems that when I click on any cell within A6:O45 it shows the

formula
in
the bar and the ROW reference changes. I had tried to use F2 then

CSE, it
took my PC several minutes to execute this and then I received a "0"

in
cells
in the A column. Since this was not acceptable, I reversed this

action.
Perhaps it did not reverse?

I still cannot enter data on the main sheet and have it post to the
sub-sheet. Nothing happens.

Where do I go from here?

Regards,
Dominick



--
Learning and growing everyday.


"Ragdyer" wrote:

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.




  #21   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DakotaNJ
 
Posts: n/a
Default Is it possible?

HI RD-

I'm missing something, but I don't see what it could be?

My workbook is set up much like yours, the formulas are identical in nature,
but it clearly works on your WB and not on mine. What could I be missing?

Gonna try re-entering it again and see what happens.

Thanks for your helop, have a great weekend.

Regards,
Dominick


--
Learning and growing everyday.


"RagDyer" wrote:

I'm sending you my test WB so you can see exactly what I've been describing
to you.

Let me know if you receive it and understand it.
--
Regards,

RD

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

"DakotaNJ" wrote in message
...
Yes! We now have the formula located in the A3 cell (returned a value of
130?), and I copied that cell formula into the first row, then copied that
row down to the end of the spreadsheet. All cells are blank.

What next? I entered a line on the main spreadsheet and it didn't copy to
the sub-sheet?

Regards,
Dominick
--
Learning and growing everyday.


"RagDyeR" wrote:

With the formula entered into a cell, and *after* the "wrap" is

eliminated,
you must be in *edit* mode for the CSE to work.

That means, you must *either* click in the formula bar,
OR
hit <F2 when the cell with the formula is selected.

You can tell when you're in "edit" mode by looking at what's displayed

in
the status bar.

Click in a cell containing the formula.
The status bar displays "Ready".
Change it to "Edit" by doing either of the above,
THEN ... CSE

Does that work now?
--

Regards,

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

"DakotaNJ" wrote in message
...
OK, I cleared the spreadsheet cell contents. Cleared the formula.

Closed
out the program. Reopened and re-entered the formula. Still can't get
curly
brackets though. No matter how I try (enter into cell or enter into

formula
bar).

Regards,
Dominick

--
Learning and growing everyday.


"DakotaNJ" wrote:

Hello RD-

Thanks again for your continued help.

After much struggling I was able to enter the formula into the formula
bar,
manually seemed to work better.

Here is the present formula (I changed 2005 to 2006 in order to play

with
a
copy of the original, this would be next years spreadsheet)

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

Wordwrap was indeed a problem which I was able to correct.

Although I have performed CSE, I do not see the curly brackets in the
formula and
I receive a #VALUE in the A3 cell.

Oddly, it seems all the cells in the spreadsheet are reacting to the
formula
in that when I click on a cell within the spreadsheet, it seems to

contain
the formula and the ROW reference changes accordingly. Have I done
something
wrong?

It seems that when I click on any cell within A6:O45 it shows the

formula
in
the bar and the ROW reference changes. I had tried to use F2 then

CSE, it
took my PC several minutes to execute this and then I received a "0"

in
cells
in the A column. Since this was not acceptable, I reversed this

action.
Perhaps it did not reverse?

I still cannot enter data on the main sheet and have it post to the
sub-sheet. Nothing happens.

Where do I go from here?

Regards,
Dominick



--
Learning and growing everyday.


"Ragdyer" wrote:

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

  #22   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DakotaNJ
 
Posts: n/a
Default Is it possible?

WOOOHOOOOO!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

It's working!!

The problem was I was using the A3 cell reference as the absolute location
to enter the formula. The actual location, based on what I saw on your
example, was the first cell in the entry area, in my case A5 (because of
title and headings).

So, the "130" I saw returned was actually the formula trying to pull the
information over, which began with "130" in the first cell.

I re-entered the formula in the correct cell, then copied across and viola!
Information filled the row!

RD, I cannot thank you enough for your diligence and patience here. You
should get an award!

This is a great way to end the week. Thank you! Thank you! Thank you!

RagDyer Rocks!!!

Regards,
Dominick





--
Learning and growing everyday.


"DakotaNJ" wrote:

HI RD-

I'm missing something, but I don't see what it could be?

My workbook is set up much like yours, the formulas are identical in nature,
but it clearly works on your WB and not on mine. What could I be missing?

Gonna try re-entering it again and see what happens.

Thanks for your helop, have a great weekend.

Regards,
Dominick


--
Learning and growing everyday.


"RagDyer" wrote:

I'm sending you my test WB so you can see exactly what I've been describing
to you.

Let me know if you receive it and understand it.
--
Regards,

RD

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

"DakotaNJ" wrote in message
...
Yes! We now have the formula located in the A3 cell (returned a value of
130?), and I copied that cell formula into the first row, then copied that
row down to the end of the spreadsheet. All cells are blank.

What next? I entered a line on the main spreadsheet and it didn't copy to
the sub-sheet?

Regards,
Dominick
--
Learning and growing everyday.


"RagDyeR" wrote:

With the formula entered into a cell, and *after* the "wrap" is

eliminated,
you must be in *edit* mode for the CSE to work.

That means, you must *either* click in the formula bar,
OR
hit <F2 when the cell with the formula is selected.

You can tell when you're in "edit" mode by looking at what's displayed

in
the status bar.

Click in a cell containing the formula.
The status bar displays "Ready".
Change it to "Edit" by doing either of the above,
THEN ... CSE

Does that work now?
--

Regards,

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

"DakotaNJ" wrote in message
...
OK, I cleared the spreadsheet cell contents. Cleared the formula.

Closed
out the program. Reopened and re-entered the formula. Still can't get
curly
brackets though. No matter how I try (enter into cell or enter into

formula
bar).

Regards,
Dominick

--
Learning and growing everyday.


"DakotaNJ" wrote:

Hello RD-

Thanks again for your continued help.

After much struggling I was able to enter the formula into the formula
bar,
manually seemed to work better.

Here is the present formula (I changed 2005 to 2006 in order to play

with
a
copy of the original, this would be next years spreadsheet)

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

Wordwrap was indeed a problem which I was able to correct.

Although I have performed CSE, I do not see the curly brackets in the
formula and
I receive a #VALUE in the A3 cell.

Oddly, it seems all the cells in the spreadsheet are reacting to the
formula
in that when I click on a cell within the spreadsheet, it seems to

contain
the formula and the ROW reference changes accordingly. Have I done
something
wrong?

It seems that when I click on any cell within A6:O45 it shows the

formula
in
the bar and the ROW reference changes. I had tried to use F2 then

CSE, it
took my PC several minutes to execute this and then I received a "0"

in
cells
in the A column. Since this was not acceptable, I reversed this

action.
Perhaps it did not reverse?

I still cannot enter data on the main sheet and have it post to the
sub-sheet. Nothing happens.

Where do I go from here?

Regards,
Dominick



--
Learning and growing everyday.


"Ragdyer" wrote:

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



  #23   Report Post  
Posted to microsoft.public.excel.worksheet.functions
RagDyer
 
Posts: n/a
Default Is it possible?

Whew ! ! !

Glad you got it sorted (boy, is that an understatement).<g

Just realized ... this started a week ago.

--
Regards,

RD

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

"DakotaNJ" wrote in message
...
WOOOHOOOOO!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

It's working!!

The problem was I was using the A3 cell reference as the absolute location
to enter the formula. The actual location, based on what I saw on your
example, was the first cell in the entry area, in my case A5 (because of
title and headings).

So, the "130" I saw returned was actually the formula trying to pull the
information over, which began with "130" in the first cell.

I re-entered the formula in the correct cell, then copied across and

viola!
Information filled the row!

RD, I cannot thank you enough for your diligence and patience here. You
should get an award!

This is a great way to end the week. Thank you! Thank you! Thank you!

RagDyer Rocks!!!

Regards,
Dominick





--
Learning and growing everyday.


"DakotaNJ" wrote:

HI RD-

I'm missing something, but I don't see what it could be?

My workbook is set up much like yours, the formulas are identical in

nature,
but it clearly works on your WB and not on mine. What could I be

missing?

Gonna try re-entering it again and see what happens.

Thanks for your helop, have a great weekend.

Regards,
Dominick


--
Learning and growing everyday.


"RagDyer" wrote:

I'm sending you my test WB so you can see exactly what I've been

describing
to you.

Let me know if you receive it and understand it.
--
Regards,

RD


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

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

benefit !

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

-

"DakotaNJ" wrote in message
...
Yes! We now have the formula located in the A3 cell (returned a

value of
130?), and I copied that cell formula into the first row, then

copied that
row down to the end of the spreadsheet. All cells are blank.

What next? I entered a line on the main spreadsheet and it didn't

copy to
the sub-sheet?

Regards,
Dominick
--
Learning and growing everyday.


"RagDyeR" wrote:

With the formula entered into a cell, and *after* the "wrap" is
eliminated,
you must be in *edit* mode for the CSE to work.

That means, you must *either* click in the formula bar,
OR
hit <F2 when the cell with the formula is selected.

You can tell when you're in "edit" mode by looking at what's

displayed
in
the status bar.

Click in a cell containing the formula.
The status bar displays "Ready".
Change it to "Edit" by doing either of the above,
THEN ... CSE

Does that work now?
--

Regards,

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

benefit!
================================================== ===

"DakotaNJ" wrote in message
...
OK, I cleared the spreadsheet cell contents. Cleared the formula.
Closed
out the program. Reopened and re-entered the formula. Still

can't get
curly
brackets though. No matter how I try (enter into cell or enter

into
formula
bar).

Regards,
Dominick

--
Learning and growing everyday.


"DakotaNJ" wrote:

Hello RD-

Thanks again for your continued help.

After much struggling I was able to enter the formula into the

formula
bar,
manually seemed to work better.

Here is the present formula (I changed 2005 to 2006 in order to

play
with
a
copy of the original, this would be next years spreadsheet)

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

Wordwrap was indeed a problem which I was able to correct.

Although I have performed CSE, I do not see the curly brackets

in the
formula and
I receive a #VALUE in the A3 cell.

Oddly, it seems all the cells in the spreadsheet are reacting to

the
formula
in that when I click on a cell within the spreadsheet, it seems

to
contain
the formula and the ROW reference changes accordingly. Have I

done
something
wrong?

It seems that when I click on any cell within A6:O45 it shows

the
formula
in
the bar and the ROW reference changes. I had tried to use F2

then
CSE, it
took my PC several minutes to execute this and then I received a

"0"
in
cells
in the A column. Since this was not acceptable, I reversed this
action.
Perhaps it did not reverse?

I still cannot enter data on the main sheet and have it post to

the
sub-sheet. Nothing happens.

Where do I go from here?

Regards,
Dominick



--
Learning and growing everyday.


"Ragdyer" wrote:

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




  #24   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,069
Default Is it possible?

Hi;
I reviewed over your posts helping out the gentleman DakotaNJ. I have the
same situation that I have an enormous amount of data I am receiving from
eight sources continually. This means that my data records need to be dynamic
in a sense. I have set up my initial sheet to be just a dynamic receptacle of
info from the other sources. But the other sheets I need to report the data
using the criteria of a certain column as the key.

Example:

I have eight salesmen that fill out reports on the status flow of sales from
the inquiries to open quotations to orders or lost to competition. One column
has that status data in it. The other columns have information on dates,
sales rep., estimator, amounts, notes, customer etc. I would like to set up a
series of sheets with only the data per certain criteria: lets say by
estimator or by status or by sales rep. I would still like the complete row
of information to be included with this sort per sheet.

Matt (my network admin.) and I have been looking at your formula you
proposed for DakotaNJ and have tried to apply it to our work sheets with
little results. We can get one line of data, but it is pointing at the wrong
line of data. Second lines or third lines give a host of errors including
circular errors.

I could enclose the test work book that I am using to give you a first hand
view. I do not see any way to email this to you though.
I have the simple test sheets that I have been describing and there is the
full list by quote sheet that shows what the real information looks like
after it is sorted by Quote number.

Thank you for your help.

John
Sales Manager

"RagDyer" wrote:

If I understand what you're looking for, it should be relatively easy and
uncomplicated.

Depending on the configuration of the data on your main sheet, you could use
either a Vlookup() function or an Index & Match combination.

You would fill your "sub" sheets with these functions to pull the data from
the main sheet, where the building ID would be the main criteria determining
which sub sheet would be able to pull the data.

This assumes that one of the fields on the main sheet *does* contain the
individual building ID.

Post back with how your main sheet is set-up, and I'm sure you'll get what
you're looking for.
--
Regards,

RD

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

"DakotaNJ" wrote in message
...
I have a workbook with several tabs. The top tab is the main spreadsheet
were I enter all my gathered data. The other tabs are the same

spreadsheet,
the same data, but broken out by specific building.

Right now, I enter the data (alpha and numeric) into the main spreadsheet,
then manually copy/paste it to the respective sub-level spreadsheet.

I can specify the "IF" part of the line item that would identify which

sheet
to copy it to, but is it possible to have a "THEN copy and paste [these
cells]" to the other spreadsheets?

Perhaps I am just dreaming here. However, looking through archives I have
found solutions to so many things, and learned so much, I figure what the
heck, I might as well ask!

Thanks for all the great info you folks pass around here. It has helped
make me a "star" employee and allowed me to do some things no one even
thought was possible.
--
Learning and growing everyday.



  #25   Report Post  
Posted to microsoft.public.excel.worksheet.functions
mat mat is offline
external usenet poster
 
Posts: 37
Default Is it possible?

to fill 4 cells to the right with different numbers and letters on a basic
spreadsheet using a single command code eg type L1 and the following 4 cells
fill with the selected information automatic and be able to type L2 in the
the same cell if diferent information to L1 is needed

"RagDyer" wrote:

Whew ! ! !

Glad you got it sorted (boy, is that an understatement).<g

Just realized ... this started a week ago.

--
Regards,

RD

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

"DakotaNJ" wrote in message
...
WOOOHOOOOO!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

It's working!!

The problem was I was using the A3 cell reference as the absolute location
to enter the formula. The actual location, based on what I saw on your
example, was the first cell in the entry area, in my case A5 (because of
title and headings).

So, the "130" I saw returned was actually the formula trying to pull the
information over, which began with "130" in the first cell.

I re-entered the formula in the correct cell, then copied across and

viola!
Information filled the row!

RD, I cannot thank you enough for your diligence and patience here. You
should get an award!

This is a great way to end the week. Thank you! Thank you! Thank you!

RagDyer Rocks!!!

Regards,
Dominick





--
Learning and growing everyday.


"DakotaNJ" wrote:

HI RD-

I'm missing something, but I don't see what it could be?

My workbook is set up much like yours, the formulas are identical in

nature,
but it clearly works on your WB and not on mine. What could I be

missing?

Gonna try re-entering it again and see what happens.

Thanks for your helop, have a great weekend.

Regards,
Dominick


--
Learning and growing everyday.


"RagDyer" wrote:

I'm sending you my test WB so you can see exactly what I've been

describing
to you.

Let me know if you receive it and understand it.
--
Regards,

RD


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

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

benefit !

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

-

"DakotaNJ" wrote in message
...
Yes! We now have the formula located in the A3 cell (returned a

value of
130?), and I copied that cell formula into the first row, then

copied that
row down to the end of the spreadsheet. All cells are blank.

What next? I entered a line on the main spreadsheet and it didn't

copy to
the sub-sheet?

Regards,
Dominick
--
Learning and growing everyday.


"RagDyeR" wrote:

With the formula entered into a cell, and *after* the "wrap" is
eliminated,
you must be in *edit* mode for the CSE to work.

That means, you must *either* click in the formula bar,
OR
hit <F2 when the cell with the formula is selected.

You can tell when you're in "edit" mode by looking at what's

displayed
in
the status bar.

Click in a cell containing the formula.
The status bar displays "Ready".
Change it to "Edit" by doing either of the above,
THEN ... CSE

Does that work now?
--

Regards,

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

benefit!
================================================== ===

"DakotaNJ" wrote in message
...
OK, I cleared the spreadsheet cell contents. Cleared the formula.
Closed
out the program. Reopened and re-entered the formula. Still

can't get
curly
brackets though. No matter how I try (enter into cell or enter

into
formula
bar).

Regards,
Dominick

--
Learning and growing everyday.


"DakotaNJ" wrote:

Hello RD-

Thanks again for your continued help.

After much struggling I was able to enter the formula into the

formula
bar,
manually seemed to work better.

Here is the present formula (I changed 2005 to 2006 in order to

play
with
a
copy of the original, this would be next years spreadsheet)

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

Wordwrap was indeed a problem which I was able to correct.

Although I have performed CSE, I do not see the curly brackets

in the
formula and
I receive a #VALUE in the A3 cell.

Oddly, it seems all the cells in the spreadsheet are reacting to

the
formula
in that when I click on a cell within the spreadsheet, it seems

to
contain
the formula and the ROW reference changes accordingly. Have I

done
something
wrong?

It seems that when I click on any cell within A6:O45 it shows

the
formula
in
the bar and the ROW reference changes. I had tried to use F2

then
CSE, it
took my PC several minutes to execute this and then I received a

"0"
in
cells
in the A column. Since this was not acceptable, I reversed this
action.
Perhaps it did not reverse?

I still cannot enter data on the main sheet and have it post to

the
sub-sheet. Nothing happens.

Where do I go from here?

Regards,
Dominick



--
Learning and growing everyday.


"Ragdyer" wrote:

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



  #26   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default Is it possible?

Is this a new question or just a added response?

If a new question, I would suggest a Lookup table and a few formulas.

See Debra Dalgleish's site for help with VLOOKUP.

http://www.contextures.on.ca/xlFunctions02.html


Gord Dibben MS Excel MVP

On Mon, 18 Sep 2006 03:21:02 -0700, mat wrote:

to fill 4 cells to the right with different numbers and letters on a basic
spreadsheet using a single command code eg type L1 and the following 4 cells
fill with the selected information automatic and be able to type L2 in the
the same cell if diferent information to L1 is needed

"RagDyer" wrote:

Whew ! ! !

Glad you got it sorted (boy, is that an understatement).<g

Just realized ... this started a week ago.

--
Regards,

RD

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

"DakotaNJ" wrote in message
...
WOOOHOOOOO!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

It's working!!

The problem was I was using the A3 cell reference as the absolute location
to enter the formula. The actual location, based on what I saw on your
example, was the first cell in the entry area, in my case A5 (because of
title and headings).

So, the "130" I saw returned was actually the formula trying to pull the
information over, which began with "130" in the first cell.

I re-entered the formula in the correct cell, then copied across and

viola!
Information filled the row!

RD, I cannot thank you enough for your diligence and patience here. You
should get an award!

This is a great way to end the week. Thank you! Thank you! Thank you!

RagDyer Rocks!!!

Regards,
Dominick





--
Learning and growing everyday.


"DakotaNJ" wrote:

HI RD-

I'm missing something, but I don't see what it could be?

My workbook is set up much like yours, the formulas are identical in

nature,
but it clearly works on your WB and not on mine. What could I be

missing?

Gonna try re-entering it again and see what happens.

Thanks for your helop, have a great weekend.

Regards,
Dominick


--
Learning and growing everyday.


"RagDyer" wrote:

I'm sending you my test WB so you can see exactly what I've been

describing
to you.

Let me know if you receive it and understand it.
--
Regards,

RD


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

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

benefit !

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

-

"DakotaNJ" wrote in message
...
Yes! We now have the formula located in the A3 cell (returned a

value of
130?), and I copied that cell formula into the first row, then

copied that
row down to the end of the spreadsheet. All cells are blank.

What next? I entered a line on the main spreadsheet and it didn't

copy to
the sub-sheet?

Regards,
Dominick
--
Learning and growing everyday.


"RagDyeR" wrote:

With the formula entered into a cell, and *after* the "wrap" is
eliminated,
you must be in *edit* mode for the CSE to work.

That means, you must *either* click in the formula bar,
OR
hit <F2 when the cell with the formula is selected.

You can tell when you're in "edit" mode by looking at what's

displayed
in
the status bar.

Click in a cell containing the formula.
The status bar displays "Ready".
Change it to "Edit" by doing either of the above,
THEN ... CSE

Does that work now?
--

Regards,

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

benefit!
================================================== ===

"DakotaNJ" wrote in message
...
OK, I cleared the spreadsheet cell contents. Cleared the formula.
Closed
out the program. Reopened and re-entered the formula. Still

can't get
curly
brackets though. No matter how I try (enter into cell or enter

into
formula
bar).

Regards,
Dominick

--
Learning and growing everyday.


"DakotaNJ" wrote:

Hello RD-

Thanks again for your continued help.

After much struggling I was able to enter the formula into the

formula
bar,
manually seemed to work better.

Here is the present formula (I changed 2005 to 2006 in order to

play
with
a
copy of the original, this would be next years spreadsheet)

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

Wordwrap was indeed a problem which I was able to correct.

Although I have performed CSE, I do not see the curly brackets

in the
formula and
I receive a #VALUE in the A3 cell.

Oddly, it seems all the cells in the spreadsheet are reacting to

the
formula
in that when I click on a cell within the spreadsheet, it seems

to
contain
the formula and the ROW reference changes accordingly. Have I

done
something
wrong?

It seems that when I click on any cell within A6:O45 it shows

the
formula
in
the bar and the ROW reference changes. I had tried to use F2

then
CSE, it
took my PC several minutes to execute this and then I received a

"0"
in
cells
in the A column. Since this was not acceptable, I reversed this
action.
Perhaps it did not reverse?

I still cannot enter data on the main sheet and have it post to

the
sub-sheet. Nothing happens.

Where do I go from here?

Regards,
Dominick



--
Learning and growing everyday.


"Ragdyer" wrote:

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 07:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"