Summary sheet for multiple sheets (difficult problem)

Here's the scenario: people owe money to companies. We assign each of these
people a number, and create a separate sheet for each of the many companies.
We need to compile a summary sheet (within the same workbook). Here's a
sample sheet (let's call it Sample Company, which is the name of the sheet):


Name            ID #        Amount

John Jones     555            500
Jim Smith       123            175
Mary Long     232            100


Thus far the workbook has about twenty sheets (not counting the summary
sheet) each named after a company and additional sheets are added frequently
(and sorted). So, Mary Long may appear in fifteen sheets, Jim Smith in one
sheet, etc. We would like the summary to look like:

Name            Company                Amount

John Jones     Sample Company        500
John Jones     Green Company          400
John Jones     Orange Company        300
TOTAL                                          1200

Mary Long    Sample Company        100
Mary Long     Green Company         400
TOTAL                                           500

So, you can see that the summary contains the detailed data, but all on one
sheet. Let's assume that the ID# and amount are always in the same columns
(I assume that would have to be the case), but we do add sheets for new
companies, and would like those included in the summary sheet.

This appears to be a difficult problem, especially with respect to new
sheets. Can anyone help me solve it?


0
z1z (7)
12/18/2003 7:32:27 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
645 Views

Similar Articles

[PageSpeed] 51

Maybe you can use this Z Man

You can use this example
http://www.rondebruin.nl/copy2.htm
To add all information on one sheet

Then use a function to sum your total on that sheet

-- 
Regards Ron de Bruin
(Win XP Pro SP-1  XL2000-2003)
www.rondebruin.nl



"Z Man" <z1z@hotmail.com> wrote in message news:e3ENj2ZxDHA.1060@TK2MSFTNGP12.phx.gbl...
> Here's the scenario: people owe money to companies. We assign each of these
> people a number, and create a separate sheet for each of the many companies.
> We need to compile a summary sheet (within the same workbook). Here's a
> sample sheet (let's call it Sample Company, which is the name of the sheet):
>
>
> Name            ID #        Amount
>
> John Jones     555            500
> Jim Smith       123            175
> Mary Long     232            100
>
>
> Thus far the workbook has about twenty sheets (not counting the summary
> sheet) each named after a company and additional sheets are added frequently
> (and sorted). So, Mary Long may appear in fifteen sheets, Jim Smith in one
> sheet, etc. We would like the summary to look like:
>
> Name            Company                Amount
>
> John Jones     Sample Company        500
> John Jones     Green Company          400
> John Jones     Orange Company        300
> TOTAL                                          1200
>
> Mary Long    Sample Company        100
> Mary Long     Green Company         400
> TOTAL                                           500
>
> So, you can see that the summary contains the detailed data, but all on one
> sheet. Let's assume that the ID# and amount are always in the same columns
> (I assume that would have to be the case), but we do add sheets for new
> companies, and would like those included in the summary sheet.
>
> This appears to be a difficult problem, especially with respect to new
> sheets. Can anyone help me solve it?
>
>


0
rondebruin (3789)
12/18/2003 8:05:35 PM
"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
news:%23XACSJaxDHA.1804@TK2MSFTNGP09.phx.gbl...
> Maybe you can use this Z Man
>
> You can use this example
> http://www.rondebruin.nl/copy2.htm
> To add all information on one sheet
>
> Then use a function to sum your total on that sheet

I don't quite know how to apply these solutions to my problem. In my case, I
need to select particular cells based upon the value in another cell in that
row (see my original post for details). Worse yet, if additional worksheets
are set up later, qualifying data on the news sheet(s) must be included.

I don't have the requisite level of Excel expertise to apply your macros to
my particular problem.


0
z1z (7)
12/18/2003 8:28:05 PM
Hi

I hope I understand you correct.
If not Sorry

If you use this example.

http://www.mvps.org/dmcritchie/excel/getstarted.htm
See David McRitchie's site if you just started with VBA
Sub Test3_Values()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    If SheetExists("Master") = True Then
        MsgBox "The sheet Master already exist"
        Exit Sub
    End If
    Application.ScreenUpdating = False
    Set DestSh = Worksheets.Add
    DestSh.Name = "Master"
    For Each sh In ThisWorkbook.Worksheets
        If sh.Name <> DestSh.Name Then
                Last = LastRow(DestSh)
                With sh.UsedRange
                    DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
                    .Columns.Count).Value = .Value
                End With
        End If
    Next
    Application.ScreenUpdating = True
End SubFunction LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End FunctionA sheet will be add to the workbook with the name Master and all information from
all sheets will be in there

You can add a formula like this to sum Column C with Mary Long in A and Green Company in B
=SUMPRODUCT((A1:A100="Mary Long")*(B1:B100="Green Company")*(C1:C100))
Change the range to yours

-- 
Regards Ron de Bruin
(Win XP Pro SP-1  XL2000-2003)
www.rondebruin.nl



"Z Man" <z1z@hotmail.com> wrote in message news:uMLXoVaxDHA.1932@TK2MSFTNGP09.phx.gbl...
>
> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
> news:%23XACSJaxDHA.1804@TK2MSFTNGP09.phx.gbl...
> > Maybe you can use this Z Man
> >
> > You can use this example
> > http://www.rondebruin.nl/copy2.htm
> > To add all information on one sheet
> >
> > Then use a function to sum your total on that sheet
>
> I don't quite know how to apply these solutions to my problem. In my case, I
> need to select particular cells based upon the value in another cell in that
> row (see my original post for details). Worse yet, if additional worksheets
> are set up later, qualifying data on the news sheet(s) must be included.
>
> I don't have the requisite level of Excel expertise to apply your macros to
> my particular problem.
>
>


0
rondebruin (3789)
12/18/2003 9:07:41 PM
Sorry for the line wrap
This is better to read
*******************************

Hi

I hope I understand you correct.
If not Sorry

If you use this example.

http://www.mvps.org/dmcritchie/excel/getstarted.htm
See David McRitchie's site if you just started with VBA

Sub Test3_Values()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    If SheetExists("Master") = True Then
        MsgBox "The sheet Master already exist"
        Exit Sub
    End If
    Application.ScreenUpdating = False
    Set DestSh = Worksheets.Add
    DestSh.Name = "Master"
    For Each sh In ThisWorkbook.Worksheets
        If sh.Name <> DestSh.Name Then
                Last = LastRow(DestSh)
                With sh.UsedRange
                    DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
                    .Columns.Count).Value = .Value
                End With
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

A sheet will be add to the workbook with the name Master and all information from
all sheets will be in there

You can add a formula like this to sum Column C with Mary Long in A and Green Company in B
=SUMPRODUCT((A1:A100="Mary Long")*(B1:B100="Green Company")*(C1:C100))
Change the range to yours

-- 
Regards Ron de Bruin
(Win XP Pro SP-1  XL2000-2003)
www.rondebruin.nl



"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message news:OTA$%23raxDHA.2452@tk2msftngp13.phx.gbl...
> Hi
>
> I hope I understand you correct.
> If not Sorry
>
> If you use this example.
>
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
> See David McRitchie's site if you just started with VBA
> Sub Test3_Values()
>     Dim sh As Worksheet
>     Dim DestSh As Worksheet
>     Dim Last As Long
>     If SheetExists("Master") = True Then
>         MsgBox "The sheet Master already exist"
>         Exit Sub
>     End If
>     Application.ScreenUpdating = False
>     Set DestSh = Worksheets.Add
>     DestSh.Name = "Master"
>     For Each sh In ThisWorkbook.Worksheets
>         If sh.Name <> DestSh.Name Then
>                 Last = LastRow(DestSh)
>                 With sh.UsedRange
>                     DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
>                     .Columns.Count).Value = .Value
>                 End With
>         End If
>     Next
>     Application.ScreenUpdating = True
> End SubFunction LastRow(sh As Worksheet)
>     On Error Resume Next
>     LastRow = sh.Cells.Find(What:="*", _
>                             After:=sh.Range("A1"), _
>                             Lookat:=xlPart, _
>                             LookIn:=xlFormulas, _
>                             SearchOrder:=xlByRows, _
>                             SearchDirection:=xlPrevious, _
>                             MatchCase:=False).Row
>     On Error GoTo 0
> End FunctionA sheet will be add to the workbook with the name Master and all information from
> all sheets will be in there
>
> You can add a formula like this to sum Column C with Mary Long in A and Green Company in B
> =SUMPRODUCT((A1:A100="Mary Long")*(B1:B100="Green Company")*(C1:C100))
> Change the range to yours
>
> -- 
> Regards Ron de Bruin
> (Win XP Pro SP-1  XL2000-2003)
> www.rondebruin.nl
>
>
>
> "Z Man" <z1z@hotmail.com> wrote in message news:uMLXoVaxDHA.1932@TK2MSFTNGP09.phx.gbl...
> >
> > "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
> > news:%23XACSJaxDHA.1804@TK2MSFTNGP09.phx.gbl...
> > > Maybe you can use this Z Man
> > >
> > > You can use this example
> > > http://www.rondebruin.nl/copy2.htm
> > > To add all information on one sheet
> > >
> > > Then use a function to sum your total on that sheet
> >
> > I don't quite know how to apply these solutions to my problem. In my case, I
> > need to select particular cells based upon the value in another cell in that
> > row (see my original post for details). Worse yet, if additional worksheets
> > are set up later, qualifying data on the news sheet(s) must be included.
> >
> > I don't have the requisite level of Excel expertise to apply your macros to
> > my particular problem.
> >
> >
>
>


0
rondebruin (3789)
12/18/2003 9:11:48 PM
Z
    I take it that new people are added as well as new companies (sheets).
And I think that you want the Summary sheet updated once in a while.  Such a
flux in data will necessitate that you use macros (code).
    If you wish, send me a small file with a few company sheets and a
Summary sheet.  Include as much explanation as you can.  Also tell me the
version of Excel you are using.  Send this to me direct via email.  Please
don't attach a file to a newsgroup post.  Remove "cobia97" from my email
address or it will go nowhere.  I'll look at what you have and what you want
to have and you and I will come up with something you can use, hopefully.
HTH   Otto
"Z Man" <z1z@hotmail.com> wrote in message
news:e3ENj2ZxDHA.1060@TK2MSFTNGP12.phx.gbl...
> Here's the scenario: people owe money to companies. We assign each of
these
> people a number, and create a separate sheet for each of the many
companies.
> We need to compile a summary sheet (within the same workbook). Here's a
> sample sheet (let's call it Sample Company, which is the name of the
sheet):
>
>
> Name            ID #        Amount
>
> John Jones     555            500
> Jim Smith       123            175
> Mary Long     232            100
>
>
> Thus far the workbook has about twenty sheets (not counting the summary
> sheet) each named after a company and additional sheets are added
frequently
> (and sorted). So, Mary Long may appear in fifteen sheets, Jim Smith in one
> sheet, etc. We would like the summary to look like:
>
> Name            Company                Amount
>
> John Jones     Sample Company        500
> John Jones     Green Company          400
> John Jones     Orange Company        300
> TOTAL                                          1200
>
> Mary Long    Sample Company        100
> Mary Long     Green Company         400
> TOTAL                                           500
>
> So, you can see that the summary contains the detailed data, but all on
one
> sheet. Let's assume that the ID# and amount are always in the same columns
> (I assume that would have to be the case), but we do add sheets for new
> companies, and would like those included in the summary sheet.
>
> This appears to be a difficult problem, especially with respect to new
> sheets. Can anyone help me solve it?
>
>


0
12/18/2003 9:47:26 PM
You need this function also

Function SheetExists(SName As String, _
                     Optional ByVal WB As Workbook) As Boolean
    'Chip Pearson
    On Error Resume Next
    If WB Is Nothing Then Set WB = ThisWorkbook
    SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function

See the website for information
http://www.rondebruin.nl/copy2.htm

-- 
Regards Ron de Bruin
(Win XP Pro SP-1  XL2000-2003)
www.rondebruin.nl



"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message news:Oo5$TuaxDHA.1196@TK2MSFTNGP12.phx.gbl...
> Sorry for the line wrap
> This is better to read
> *******************************
>
> Hi
>
> I hope I understand you correct.
> If not Sorry
>
> If you use this example.
>
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
> See David McRitchie's site if you just started with VBA
>
> Sub Test3_Values()
>     Dim sh As Worksheet
>     Dim DestSh As Worksheet
>     Dim Last As Long
>     If SheetExists("Master") = True Then
>         MsgBox "The sheet Master already exist"
>         Exit Sub
>     End If
>     Application.ScreenUpdating = False
>     Set DestSh = Worksheets.Add
>     DestSh.Name = "Master"
>     For Each sh In ThisWorkbook.Worksheets
>         If sh.Name <> DestSh.Name Then
>                 Last = LastRow(DestSh)
>                 With sh.UsedRange
>                     DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
>                     .Columns.Count).Value = .Value
>                 End With
>         End If
>     Next
>     Application.ScreenUpdating = True
> End Sub
>
> Function LastRow(sh As Worksheet)
>     On Error Resume Next
>     LastRow = sh.Cells.Find(What:="*", _
>                             After:=sh.Range("A1"), _
>                             Lookat:=xlPart, _
>                             LookIn:=xlFormulas, _
>                             SearchOrder:=xlByRows, _
>                             SearchDirection:=xlPrevious, _
>                             MatchCase:=False).Row
>     On Error GoTo 0
> End Function
>
> A sheet will be add to the workbook with the name Master and all information from
> all sheets will be in there
>
> You can add a formula like this to sum Column C with Mary Long in A and Green Company in B
> =SUMPRODUCT((A1:A100="Mary Long")*(B1:B100="Green Company")*(C1:C100))
> Change the range to yours
>
> -- 
> Regards Ron de Bruin
> (Win XP Pro SP-1  XL2000-2003)
> www.rondebruin.nl
>
>
>
> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message news:OTA$%23raxDHA.2452@tk2msftngp13.phx.gbl...
> > Hi
> >
> > I hope I understand you correct.
> > If not Sorry
> >
> > If you use this example.
> >
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> > See David McRitchie's site if you just started with VBA
> > Sub Test3_Values()
> >     Dim sh As Worksheet
> >     Dim DestSh As Worksheet
> >     Dim Last As Long
> >     If SheetExists("Master") = True Then
> >         MsgBox "The sheet Master already exist"
> >         Exit Sub
> >     End If
> >     Application.ScreenUpdating = False
> >     Set DestSh = Worksheets.Add
> >     DestSh.Name = "Master"
> >     For Each sh In ThisWorkbook.Worksheets
> >         If sh.Name <> DestSh.Name Then
> >                 Last = LastRow(DestSh)
> >                 With sh.UsedRange
> >                     DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
> >                     .Columns.Count).Value = .Value
> >                 End With
> >         End If
> >     Next
> >     Application.ScreenUpdating = True
> > End SubFunction LastRow(sh As Worksheet)
> >     On Error Resume Next
> >     LastRow = sh.Cells.Find(What:="*", _
> >                             After:=sh.Range("A1"), _
> >                             Lookat:=xlPart, _
> >                             LookIn:=xlFormulas, _
> >                             SearchOrder:=xlByRows, _
> >                             SearchDirection:=xlPrevious, _
> >                             MatchCase:=False).Row
> >     On Error GoTo 0
> > End FunctionA sheet will be add to the workbook with the name Master and all information from
> > all sheets will be in there
> >
> > You can add a formula like this to sum Column C with Mary Long in A and Green Company in B
> > =SUMPRODUCT((A1:A100="Mary Long")*(B1:B100="Green Company")*(C1:C100))
> > Change the range to yours
> >
> > -- 
> > Regards Ron de Bruin
> > (Win XP Pro SP-1  XL2000-2003)
> > www.rondebruin.nl
> >
> >
> >
> > "Z Man" <z1z@hotmail.com> wrote in message news:uMLXoVaxDHA.1932@TK2MSFTNGP09.phx.gbl...
> > >
> > > "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
> > > news:%23XACSJaxDHA.1804@TK2MSFTNGP09.phx.gbl...
> > > > Maybe you can use this Z Man
> > > >
> > > > You can use this example
> > > > http://www.rondebruin.nl/copy2.htm
> > > > To add all information on one sheet
> > > >
> > > > Then use a function to sum your total on that sheet
> > >
> > > I don't quite know how to apply these solutions to my problem. In my case, I
> > > need to select particular cells based upon the value in another cell in that
> > > row (see my original post for details). Worse yet, if additional worksheets
> > > are set up later, qualifying data on the news sheet(s) must be included.
> > >
> > > I don't have the requisite level of Excel expertise to apply your macros to
> > > my particular problem.
> > >
> > >
> >
> >
>
>


0
rondebruin (3789)
12/18/2003 9:57:41 PM
Reply:

Similar Artilces:

daily sales summary info
I have a small clothing boutique that recently moved locations. We would like to know if our traffic has increased, and if our average sale has increased. Is there any way to access this information in a report? Paging through every day of Z reports is pretty tedious. MP There's a custom report you can download called "average sales by store". It's just summary data that you can filter by date ranges. It includes your average transaction $ amount, avg # of units/transaction, profit marging % and avg # transactions/day. Good luck. "MP" wrote: > I ha...

Multiple emails
Since yesterday I am receiving duplicate copies of emails. This was particularly bad because I had received some rather large video clips. (I'm on dial-up.) Where is the problem most likely to lie, my isp or something in OE6? I'm using XP8 SP3. Thanks...Susie Are you using the email scanning module of your anti-virus program? "Susie" <Susie@discussions.microsoft.com> wrote in message news:F5FC2EA1-F41D-4F17-8BCB-C1F97C03ED94@microsoft.com... : Since yesterday I am receiving duplicate copies of emails. : This was particularly bad because I had received s...

Copy data from column that begins with data to new sheet
Hello, I'm currently trying to copy data from a row in excel based on what's in colum B to it's own sheet. I've found some good macros but need some help on how to modify it to fit my needs. The one macro that is almost perfect is AdvFilterRepFiltered.zip on http://www.contextures.com/excelfiles.html#CondFormat That being said, Column B consists of something like this: 3960020262 3960020263 4550050473 4550050474 I want to be able to sort 39600 and 45500 to their own sheet. The macro I mentioned above works great but it will sort everything to it's own sheet that doesn...

Email problem on Outlook
I need some help. I received an error message while trying to delete an email. It is in my Outlook .pst files. It tells me to quit all mail enabled applications and then run the Inbox Repair Tool. I have not been able to figure out how to do that. Please help. Thank you. Gil Look for a file named scanpst.exe and run it until your .pst file returns no errors. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searchin...

Count based on multiple conditions (between two dates)
Hello, I wonder if anyone can help me on this Excel 2000 problem. I want to perform a count of data in a worksheet based on two conditions. Condition one is when cells in column C contain "E". The second condition is where the cells in column H (which all contain dd/mm/yyy data) are within a certain month (i.e in May or between 01/05/2002 and 31/05/2002, whihcever way of looking at it is the easier) The multiple condition array formulas just dont seem to work! Thanks for your help, Joe Joe, SUMPRODUCT does it nicely =SUMPRODUCT((C1:C100="E")*(TEXT(H1:H100,"...

totaling to a different sheet
I have 2 work sheets, 1 is weekly time card, the other expenses. I want to keep a running total of hours worked from sheet one, and milage driven, material expenses, and reimbursement from sheet 2, listed on sheet 3. can this be done? -- -Brian-H- ------------------------------------------------------------------------ -Brian-H-'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5665 View this thread: http://www.excelforum.com/showthread.php?threadid=465762 Brian, Lets say that sheet1 cell a1 contains (hours) 45 and that sheet2 cell a2 contains (expenses...

Sum Multiple Criteria 11-18-09
Hello Everyone, I have been try to learn how to use the SUM function with multiple criteria. I have run into a road block with my experimentation. By the way, I am using Excel 2003 SP3. My data looks like the following: city Mfr model volume dearborn ford taurus 10 troy ford fusion 20 shelby gm lacrosse 10 warren chrysler sebring 10 dearborn gm camaro 30 detroit ford fusion 40 shelby chrysler magnum 20 warren chrysler avenger 40 troy chrysler avenger 20 detroit gm lacrosse 50 detroit gm impala 30 warren ford fusion 40 troy chrysler avenger 20 detroit ford taurus 10 ...

Sorting sheets by name
Is there a possibility to put sheets inside of a workbook in alphabet order? I can't find that anywhere. It would be quite useful if one would have a large number of sheets named after different people. Thanks! There is no built-in way to do this. For a VBA macro to do it, see www.cpearson.com/excel/sortws.htm . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Eternity" <zabo@ravi.com> wrote in message news:bu7c0h$dtm$1@bagan.srce.hr... > Is there a possibility to put sheets inside of a workbook in alphabet orde...

Rebulid Inventory Summary from Period Summary
If you fail to close the Inventory module on the correct date, all Summary values for an inventory item are summed incorrectly. I would like to see a rebuild of the summary values in an item be rebuilt form the period summary that is correct. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree&...

how to manage multiple mail aliases in Outlook (vs O. Express)
Hi, I have 3 mail aliases that point to the same mailbox, 2 of which are mine and one that is my wife's. Before I "upgraded" from Outlook Express to Outlook 2002, I had rules set to leave any messages with my wife's email address in the "To" field on the server and download the rest to my OE client. That way, she could get her mail on her own computer and I would get mine through my own client. But I haven't found a way to do this in Outlook 2002. There does not seem to be a rule action to "leave message on server" if "To field contains: ...

Multiple Instances of Manager Program
Greetings I have a situation where it might become necessary to have multiple people running the SO Manager program at the same time. Doing edits and changes primarily. Has anyone run up against any performance/stability issues under such circumstances. Does this cause any instability problems with the POS frontend also operating at the same time? The reason I mention this is that we did some experimentation with having an MS Access forms app try and edit some data in the Customer table and it would cause the frontend to crash if we were operating on the same record. We we...

Having Problem with Select Case
Hi -- This is my situation: I have a list box from which users can choose the report that they want to print. The code works just fine when I use an "If-Then-Else" construct but not so when it is transferred to "Select Case". Here is a snippet from both: If lstStores.ListIndex = 0 Then stFullName = "I:\Ops\Grocery Rescue\Jet Reports\Rosauers\Rosa01\Rosa01.xls" ElseIf lstStores.ListIndex = 1 Then stFullName = "I:\Ops\Grocery Rescue\Jet Reports\Rosauers\Rosa02\Rosa02.xls" ElseIf lstStores.ListIndex = 2 ...

multiple managers for distribution list in gal on exchange 2k3
Hi all, is there a way to allow multiple persons in a organisation to manage GAL distribution list on exchange 2k3 via the outlook client? If we se one as owner, only this person could administer the list, but we need solution for more than one manager. Thanks a lot. Klau - ZeroFighte ----------------------------------------------------------------------- Posted via http://www.webservertalk.co ----------------------------------------------------------------------- View this thread: http://www.webservertalk.com/message535481.htm Klaus- Check out this KB: http://support.microsoft.com...

Problem with sheets that mirror each other
I'm trying to setup that mirror each other at all times. I am currently handling "Worksheet_Change()" in my sheets, so any time one sheet gets modified, I am trying to copy the contents of one sheet to the other. The shortened code looks as follows: ' ' Sheet 1 ' Private Sub Worksheet_Change(ByVal Target As Range) changedCell = Target(1).Address(0, 0) Sheet2.Range(changedCell).Value = Sheet1.Range(changedCell).Value End Sub ' ' Sheet 2 ' 'Private Sub Worksheet_Change(ByVal Target As Range) changedCell = Target(1).Address(0,...

Group and ungroup protected sheets
I need help with grouping and ungrouping protected sheets. I have 3 sheets in a workbook that must be protected. With protection on I can't use the group and ungroup function. Is there a solution for this ? If anyone could help that would be great. -- Regards Bob. Do you mean grouping as in Data|Group (outlining) or grouping worksheets (click on one worksheet tab, then ctrl-click on subsequent tabs)??? If you meant the Data|Group (or even Data|Subtotals).... If you already have the outline applied, you can protect the worksheet in code (auto_open/workbook_open??). Option Expl...

SOP Summary Breakdown report vs SOP Summary Breakdown Reprint repo
Hi, My client has just upgraded to vs10 and they noticed that the SOP Summary Breakdown report now prints at a document level instead of at an account level as the SOP Summary Breakdown Reprint Report does. In vs8 it apparantly worked the same as the SOP Summary Breakdown Report. I have had a look and the report uses the SOP Distribution temp table, whereas the SOP Summary Breakdown Reprint Report using the SOle.P Distribution Open and History table. I tried adding a relation to the Account Master table from the SOP Distribution temp, but this then just corrupts the report and caus...

Security for viewing Excel rows or sheets
I am working on creating a kind of gradebook to use online. I'd like to be able to enter student grades and then give each of them a code to allow them to view only their own grades. I've tried protecting cell ranges, but that only stops users from changing the cells. I've also tried putting one student's grades per sheet, but I can't seem to find a way to get Excel to ask for a password when navigating to that sheet. Here's what I'd like to do: Student opens workbook, and there's a cover sheet with instructions. From there, they navigate to either a single sh...

Working with multiple files/workbooks cross linked
Hi, It seems that the dozen workbooks I have created and the cross sharing of nformation with some single filesworkbook sharing several other files / workbooks need to have the files all open otherwise the paths seems to get a bit mixed up. Is there a way then that will open all files related to a group when I want to work on a particular set of files but as mentioned get ALL the oher files/ workbooks open to ensure the links all work corretly? Thanks Mike Here is an example with three workbooks: first.xls second.xls third.xls Let's say that if one of the files is opened, we wa...

Filtering for multiple sheets
I need to filter out information from 1 sheet to another to take out duplication. Does anyone have a formula that I can use?? I need it to sort out 2 columns of information. Thanks for the help! Maybe you could use data|filter|advanced filter. Debra Dalgleish has some notes at: http://contextures.com/xladvfilter01.html#FilterUR JVDM wrote: > > I need to filter out information from 1 sheet to another to take out > duplication. Does anyone have a formula that I can use?? I need it to sort > out 2 columns of information. Thanks for the help! -- Dave Peterson ...

Dynamic sql problem with reporting services
Hi all, I've got a report linked to a stored procedure which is a dynamic sql union query (apologies if this is the wrong terminology I'm new to this!). The union query joins 3 tables and has selection criteria added depending on the parameters selected by the user. I've simplified the stored procedure to show you how it looks here: ALTER PROCEDURE SearchResultsAll /* This stored procedure uses dynamic sql to build a stored procedure for search results based on user selected criteria */ @docno varchar(50) AS Declare @SQLQuery AS NVarchar(4000) Decl...

Problem with "Favorite Folders" section in Outlook 2003
Below is a description of the problem I have encountered with Outlook 2003 since reinstalling Office 2003 on my new HD: Outlook 2003 does not save all of the folders or the order of the folders that I place in the "Favorite Folders" section of the Navigation pane when I close Outlook. So every time I open Outlook, some of the folders that I have previously removed from the "Favorite Folders" section reappear and others that I had added are not there and have to be readded, and the order of the folders that do stay there has changed. I have to continually add or remove so...

Delete the sheets in the current workbook which are not have colored in the tab
i need a vba code to delete all the sheets whose sheet name are not coloured ....... Sub DelNonColouredTabs() Dim i As Long Dim s As String Dim shot As Object If Val(Application.Version) <= 9 Then MsgBox "Coloured tabs N/A in this Excel version" Exit Sub End If ReDim arrNames(1 To ActiveWorkbook.Sheets.Count) For Each sht In ActiveWorkbook.Sheets If VarType(sht.Tab.Color) = vbBoolean Then i = i + 1 arrNames(i) = sht.Name End If Next If i = ActiveWorkbook.Sheets.Count Then ...

Problem with combobox data
Hello, I have a database for our non-conforming materials. There is a NCR Master Table where I use a form called NCR Generation to allow data entry. I use a combo box which displays item number, description, revision, and unit cost which is drawn from our item information in a separate file. It works fine on the form in displaying description, revision, and unit cost on my form. I would however like to break a cardinal rule about databases by storing the revision and unit cost in my NCR Master Table since these may change over time and I want to have the information that was correct a...

Replace All within selection does all sheet in 2002
Is there a way to use the "Replace All" command on a selection of cells and not the whole sheet. I'm sure that this used to be possible on eralier versions of Excel. The "Find All" command give the correct cells in the results box. ...

Jump from cell to new sheet?
Is there a way to click on a cell to cause a specified sheet to open? What I want to do is put a thumbnail image in a cell and then have the full image in another sheet; then when I click on the thumbnail, the other sheet will open and the full image will be displayed. Excel 2003, SP3 Windows XP Pro, SP2 Thanks, Don Culp On Aug 7, 8:04=A0am, "Don Culp" <dc...@krell-engineering.com> wrote: > Is there a way to click on a cell to cause a specified sheet to open? Wha= t I > want to do is put a thumbnail image in a cell and then have the full imag= e > in another sh...