Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: error in code please help

  1. #11
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-15 04:54, KnAsTa wrote:
    Ok, maybe i have interpreted worksheet wrong. How do i create a workbook with all the week files within it?
    First things first. set 1 template that you're happy with that you can copy/paste the data into, copy this sheet 18 more times, rename the sheets week## or whatever.

    I tell you I hope I get a job with the Certificate I've got coming to me.
    "Have a good time......all the time"
    Ian Mac

  2. #12
    Board Regular
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, now i have 20 worksheets in the one workbook, they include the 1 main worksheet of the soccer wins losses etc. The other 19 sheets are the weeks 1 to 19.

    I have a formula:
    =IF(ISNUMBER(MATCH(A5,week1:week19!A1:A10,0)),IF(VLOOKUP(A5,week1:week19!A1:D10,3,0)>VLOOKUP(A5,week1:week19!A1:D10,4,0),1,0),)

    But this is just searching all 19 sheets at once, not giving values from week 1 to week 19.

    For each week we need to see if the team wins. This is what we hope the above formula to do, but obviously it doesnt.

    We need to know how to make it go through each individual week giving us a result for each week if the team wins or not, and returning the total number of wins.

    For information on the formula variables, see my other post on this same question.

  3. #13
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    I just looked over the specs for the assignments and I believe you are making this way too complicated.

    Your import of the text delimited file assumes that you will require a macro to download the data, right? If that is the case,
    1. write the data to the appropriate sheet
    2. transfer the weekly data to a single data table (DataSheet as Ian suggested)
    3. Use SUMPRODUCT on the table sheet referencing the data table.
    4. You may want to add an additional week reference, where at the change of a cell or button, you can quickly recreate the league table (sorted) for *any* week (this allows you to look at past results).
    5. The sort should be last thing you need to add to your automatic procedure.

    I'll play around with this a bit and see if I can give some guidance.

    Bye,
    Jay

  4. #14
    Board Regular
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Whats a datatable or DataSheet? is this just having the 19 week spreadsheets in the workbook?

    I am only a fairly new beginner in excel, and i can't understand much.

    [ This Message was edited by: KnAsTa on 2002-04-15 23:22 ]

  5. #15
    Board Regular
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The problem i am having at the moment aswell, is how to represent the data given to us. Like are we meant to keep the 19 weeks datafiles seperate from the main league table, or do we put them into 1 workbook, etc.
    The reasoning for this, is our lecturer is going to run his own 19week data through our spreadsheet, and by this i expect that he wants the files to be seperate?

  6. #16
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,425
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    Jay and Ian's suggestions are consistent with the approach/formula that I suggested last weekend.

    2. transfer the weekly data to a single data table (DataSheet as Ian suggested)

    One sheet on your main spreadsheet. You can also include a copy of the source data if you want but that is not necessary.


    3. Use SUMPRODUCT on the table sheet referencing the data table.

    See previous postings or with named ranges

    =SUMPRODUCT((rB_=A4)*(rF_)*(rA_=$B$3)+(rC_=A4)*(rG_)*(rA_=$B$3))

    4. You may want to add an additional week reference, where at the change of a cell or button, you can quickly recreate the league table (sorted) for *any* week (this allows you to look at past results).

    You can also use the week reference such as R1 as a criteria for the above formula.

    N.B.
    - ensure that you understand fully the requirements of your project
    - what must you accomplish
    - what information must be assembled to achieve the solution

    All of the requirements except one can be done with a few formulas like Sum, Sumif and Sumproduct on a Summary Sheet and a Total Sheet.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •