|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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.
|
|
Absolutely possible if you can define what and how you want to do it
manually.
"DakotaNJ" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
news:32F16C14-0253-433E-8F36-5992E50F92BF[ at ]microsoft.com...
[Quoted Text] > 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.
|
|
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
|
|
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 Pushit()
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" <pynasocas[ at ]yahoo.com.sg>
wrote:
[Quoted Text] >Absolutely possible if you can define what and how you want to do it
>manually.
>
>"DakotaNJ" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
>news:32F16C14-0253-433E-8F36-5992E50F92BF[ at ]microsoft.com...
>> 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.
>
|
|
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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
news:32F16C14-0253-433E-8F36-5992E50F92BF[ at ]microsoft.com...
[Quoted Text] > 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.
|
|
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:
[Quoted Text] > 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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
> news:32F16C14-0253-433E-8F36-5992E50F92BF[ at ]microsoft.com...
> > 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.
>
>
|
|
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),INDEX(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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
news:58ED8C38-D858-4717-BA12-E18C7BFE2845[ at ]microsoft.com...
[Quoted Text] > 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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
>> news:32F16C14-0253-433E-8F36-5992E50F92BF[ at ]microsoft.com...
>> > 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.
>>
>>
|
|
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),INDEX(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),INDEX(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:
[Quoted Text] > 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),INDEX(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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
> news:58ED8C38-D858-4717-BA12-E18C7BFE2845[ at ]microsoft.com...
> > 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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
> >> news:32F16C14-0253-433E-8F36-5992E50F92BF[ at ]microsoft.com...
> >> > 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.
> >>
> >>
>
>
|
|
Here's your revised formula to use.
=IF(COUNTIF(FNb!$F$5:$F$154,$A$1)>=ROWS($1:1),INDEX(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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
news:6D18299B-829E-4780-A49E-0DF875B2C704[ at ]microsoft.com...
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),INDEX(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),INDEX(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:
[Quoted Text] > 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),INDEX(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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
> news:58ED8C38-D858-4717-BA12-E18C7BFE2845[ at ]microsoft.com...
> > 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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
> >> news:32F16C14-0253-433E-8F36-5992E50F92BF[ at ]microsoft.com...
> >> > 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.
> >>
> >>
>
>
|
|
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:
[Quoted Text] > Here's your revised formula to use.
>
> =IF(COUNTIF(FNb!$F$5:$F$154,$A$1)>=ROWS($1:1),INDEX(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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
> news:6D18299B-829E-4780-A49E-0DF875B2C704[ at ]microsoft.com...
> 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),INDEX(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),INDEX(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),INDEX(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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
> > news:58ED8C38-D858-4717-BA12-E18C7BFE2845[ at ]microsoft.com...
> > > 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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
> > >> news:32F16C14-0253-433E-8F36-5992E50F92BF[ at ]microsoft.com...
> > >> > 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.
> > >>
> > >>
> >
> >
>
>
>
|
|
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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
news:28B6C0BB-6F3B-4A1C-B2CB-A21A3A64B69F[ at ]microsoft.com...
[Quoted Text] > 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),INDEX(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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
> > news:6D18299B-829E-4780-A49E-0DF875B2C704[ at ]microsoft.com...
> > 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),INDEX(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),INDEX(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),INDEX(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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
> > > news:58ED8C38-D858-4717-BA12-E18C7BFE2845[ at ]microsoft.com...
> > > > 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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
> > > >> news:32F16C14-0253-433E-8F36-5992E50F92BF[ at ]microsoft.com...
> > > >> > 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.
> > > >>
> > > >>
> > >
> > >
> >
> >
> >
|
|
OK, sorry about that RD.
The EXACT names are:
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 are:
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:
[Quoted Text] > 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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
> news:28B6C0BB-6F3B-4A1C-B2CB-A21A3A64B69F[ at ]microsoft.com...
> > 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),INDEX(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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
> > > news:6D18299B-829E-4780-A49E-0DF875B2C704[ at ]microsoft.com...
> > > 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),INDEX(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),INDEX(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),INDEX(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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
> > > > news:58ED8C38-D858-4717-BA12-E18C7BFE2845[ at ]microsoft.com...
> > > > > 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
|
|
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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
news:52A865FE-14AA-45ED-9CAD-188D54F8F6FA[ at ]microsoft.com...
[Quoted Text] > OK, sorry about that RD.
>
> The EXACT names are:
>
> 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 are:
> 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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
> > news:28B6C0BB-6F3B-4A1C-B2CB-A21A3A64B69F[ at ]microsoft.com...
> > > 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),INDEX(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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
> > > > news:6D18299B-829E-4780-A49E-0DF875B2C704[ at ]microsoft.com...
> > > > 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),INDEX(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),INDEX(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),INDEX(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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
> > > > > news:58ED8C38-D858-4717-BA12-E18C7BFE2845[ at ]microsoft.com...
> > > > > > 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
|
|
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:
[Quoted Text] > 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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
> news:52A865FE-14AA-45ED-9CAD-188D54F8F6FA[ at ]microsoft.com...
> > OK, sorry about that RD.
> >
> > The EXACT names are:
> >
> > 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 are:
> > 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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
> > > news:28B6C0BB-6F3B-4A1C-B2CB-A21A3A64B69F[ at ]microsoft.com...
> > > > 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),INDEX(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" <DakotaNJ[ at ]discussions.microsoft.com> wrote in message
> > > > > news:6D18299B-829E-4780-A49E-0DF875B2C704[ at ]microsoft.com...
> > > > > 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),INDEX(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),INDEX(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?
|
|
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" <DakotaNJ[ at ]discussions.microsoft.com> | | |