Posts by rnomis

    Re: pick items from a list and total them


    That's great. I did something similar to this a couple of years ago in a Google sheet but got made redundant and lost access to all my work!
    Thanks for your help.
    Got it working on all data and works perfectly.8-)

    A friend has asked for help. I know the long way of doing this but there must be a simpler and faster way.


    She makes up packs of Lego bricks for schools. They have names such as Early Years Pack 1, Early Years Pack 2 etc. Her Sheet1 lists the packs on Row 1 so 'Early Years Pack 1' is in cell B1
    That pack has various bricks which are listed on rows 2-53, column A but this pack doesn't use bricks from all rows. Example:


    [TABLE="width: 303"]

    [tr]


    [td]

    Bricks

    [/td]


    [td]

    Early Years Pack 1

    [/td]


    [/tr]


    [tr]


    [td]

    black 1x2

    [/td]


    [td]

    2

    [/td]


    [/tr]


    [tr]


    [td]

    blue1x2

    [/td]


    [td]

    2

    [/td]


    [/tr]


    [tr]


    [td]

    yellow1x2

    [/td]


    [td]

    7

    [/td]


    [/tr]


    [tr]


    [td]

    red1x2

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    white1x2

    [/td]


    [td]

    3

    [/td]


    [/tr]


    [tr]


    [td]

    orange1x2

    [/td]


    [td]

    1

    [/td]


    [/tr]


    [tr]


    [td]

    green1x2

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    lime1x2

    [/td]


    [td]


    [/td]


    [/tr]


    [/TABLE]

    She needs to have a data entry form on Sheet2 where she can insert the number of each pack required against the pack name so she knows what to order from her supplier. So if she wants 2x 'Early Years Pack 1' her order list will show black 1x2: 4, blue 1x2: 4 and yellow 1x2: 14 etc.
    I could of course just use an if statement to calculate from a drop down list: =IF($B$1="Early Years Pack 1",(B2*$J$2),0) (J2 contains the pack quantity needed) but feel there should be a better way. I hope someone can help.


    Thanks, Simon

    I have a worksheet which allows me to see the status of subscriptions exported from our CMS and to show data input errors made by colleagues. This has been working well until someone decided to add another class of subscriber called 'Trial'.


    I now need to show this on the error sheet which is currently calculated with the following:


    =IF(ISBLANK(A142),,IF(AND(L142-TODAY()>1,OR(I142="Subscriber RENEWAL",I142="Subscriber NEW")),"",$J$5&I142))


    This checks that column A contains data (client reference number) then looks for 2 subscriber types (column I) and ignores them if they are present and not overdue (column L) or concatenates an error message in J5 with the subscriber type in column I and send the results to an error sheet.


    I have now been asked to show if they are a 'Trial' subscriber. Column L may not have a date in so I need to show all occurrences of 'Trial' (in column I) so if they are a Trial client, the cell returns TRIAL.


    I am sure it can't be that hard but inspiration has left me today! Any help gratefully received.


    Thanks
    Simon

    I have some data from an sql output that is in the wrong format.


    What I have is:


    Sheet1
    ID Year Group Time Number of students
    20 YG-10 110 169
    20 YG-11 219 199
    20 YG-9 824 1128
    20 YG-8 779 1082
    20 YG-7 1129 1685
    38 YG-10 1281 1789
    38 YG-9 1574 2166
    59 YG-7 785 987


    You will see that a particular year group may not always appear if there is no time or Number of Students so I need to return 0.


    What I need is:


    Sheet2
    [TABLE="width: 704"]

    [tr]


    [TD="width: 64"]ID[/TD]
    [TD="width: 64"]HrsYR7[/TD]
    [TD="width: 64"]HrsYR8[/TD]
    [TD="width: 64"]HrsYR9[/TD]
    [TD="width: 64"]HrsTY10[/TD]
    [TD="width: 64"]HrsYR11[/TD]
    [TD="width: 64"]YR7[/TD]
    [TD="width: 64"]YR8[/TD]
    [TD="width: 64"]YR9[/TD]
    [TD="width: 64"]YR10[/TD]
    [TD="width: 64"]YR11[/TD]

    [/tr]


    [tr]


    [TD="align: right"]20[/TD]
    [TD="align: right"]1129[/TD]
    [TD="align: right"]779[/TD]
    [TD="align: right"]824[/TD]
    [TD="align: right"]110[/TD]
    [TD="align: right"]219[/TD]
    [TD="align: right"]1685[/TD]
    [TD="align: right"]1082[/TD]
    [TD="align: right"]1128[/TD]
    [TD="align: right"]169[/TD]
    [TD="align: right"]199[/TD]

    [/tr]


    [tr]


    [TD="align: right"]38[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]1574[/TD]
    [TD="align: right"]1281[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]2166[/TD]
    [TD="align: right"]1789[/TD]
    [TD="align: right"]0[/TD]

    [/tr]


    [tr]


    [TD="align: right"]59[/TD]
    [TD="align: right"]785[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]987[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]0[/TD]

    [/tr]


    [/TABLE]


    I have tried combinations of INDEX and MATCH to no avail and just seem to get the same data in column A in Column B.


    I think this is fairly simple but has beaten me!
    Thanks,
    Simon

    I have a workbook that uses the following formula that returns a week number: CELL E3: =IF(ISBLANK(B3),"",(INT((B3-DATE(YEAR(B3),1,1)-WEEKDAY(B3))/7)+2))


    This is then referenced to pull data from another sheet according to the week number. CELL G3 =IFERROR(VLOOKUP($A3,INDIRECT("Week"&E3&"!A:V"),8,0),"")


    My year runs from September 1st so I have changed the formula in E3 to: =IF(ISBLANK(B3),"",(INT((B3-DATE(YEAR(B3),9,1)-WEEKDAY(B3))/7)+2))


    This works fine up to December 31st after which it goes horribly wrong. Jan 1st 2012 now shows as week -33 instead of week 19. How can I make it continue to the end of August, this being week 52?
    I will start a new workbook each year as my data starts at zero each September.

    I am starting a new project at work and need to calculate usage and increase figures over a 6 week period.


    The scenario is: A client support visit takes place. 6 weeks after, I need to show the usage up to that week and calculate the increase during the 6 week period. My colleagues will also be using this sheet so the data entry needs to be simple.


    I will have an input sheet with column headings. Data will be put in columns A-C with the results showing in columns D-E.


    A: Client Reference No.
    B: Client Name
    C: Week No visited
    D: the usage on the week visited
    E: the increase in usage in the 6 weeks after the visit
    F: the percentage increase in the 6 weeks after the visit



    I have a separate sheet showing the usage figures in column C. In column A is the client reference No. I will use a separate sheet for each week and number them as Week1, Week2 etc.


    When data is entered in columns A and C, the data is looked up from the appropriate sheets. So, if a visit takes to client No. 20 (A2)and takes place during Week3 (C2), the lookup will add the client name in B2, the usage at visit week in D2, the usage from sheets Week3-Week8 inclusive in E2, and the percentage increase in F2.


    I can put all client numbers and names on a separate sheet to use as lookup if needed. There is other data I need to show but most will be based on the same formula.


    I hope someone can help.
    Thanks
    Simon

    I have just done a mail merge for my company to 1300 existing clients (not spam honestly!). As most had been neglected for some time, many email addresses are out of date so the emails bounced back. I now have 106 email addresses in an Excel column. In another worksheet, I have all the email addresses for all client contacts and a reference number for each company. Each row contains the reference number in column A and the email addresses in either column V or AB.
    I need to find the reference number and enter it against each email address.
    I have tried a vlookup but just get errors as I am not that confident it is what I should be using!
    What I have:
    sheet: email
    Column A email addresses
    Sheet: data
    Column A reference number, Column V: contact1 email, Column AB: Contact2 email
    I need to put the reference number in the email sheet in column B against the email address


    It's Friday and my brain hurts so any help would be gratefully received.


    Thaks
    Simon

    Re: Problem with IF(AND formula


    Brilliant. I am starting to see the light. I tried to nest formulas but I can see that it is better to start from the lowest award and work up so to speak.


    Unfortunately I made a small error in the first line of the criteria in my my post. It should have read:


    If B2 is blank: No award (Column B contains the client name If it is blank, I need to return a blank field - although "No Award" would be ok.)
    Bronze: C2>=1000
    Silver: C2>=3000,D2>=25,E2>=10
    Gold: C2>=6000,D2>=50,E2>=20
    Platinum: C2>=10000,D2>=75,E2>=25


    Does this look ok? It seems to work.


    =IF(B2="","",IF(AND(C2>=10000,D2>=75,E2>=25),"Platinum",IF(AND(C2>=6000,D2>=50,E2>=20),"Gold",IF(AND(C2>=3000,D2>=25,E2>=10),"Silver",IF(C2>=1000,"Bronze","No Award")))))



    Thanks to you both for your help.


    Simon

    I am just starting to use Excel 2007 more but I am having a problem understanding the basic concepts of building nested IF with AND & OR statements.


    I need to produce a list of clients who will receive an award. There are 4 awards available: Bronze, Silver, Gold and Platinum.


    The criteria are:
    If B2 is blank
    Bronze: C2>=1000
    Silver: C2>=3000,D2>=25,E2>=10
    Gold: C2>=6000,D2>=50,E2>=20
    Platinum: C2>=10000,D2>=75,E2>=25


    I have the following which contains errors:
    =IF(B2="","NONE",IF(C2>=1000,"BRONZE",(OR(C2>3000,D2>=25,E2>=10),"SILVER", (IF(AND(C2>=6000,D2>=50,E2>=20),(IF(AND(C2>=10000,D2>=75,E2>=25),"PLATINUM","GOLD"))))


    I hope someone can:
    A, tell me where I am going wrong
    B, give me the right answer and
    C, point me in the right direction for a good book or website


    Thanks in advance,