Posts by lyambor

    Re: Nested IF Mutiple Logical Tests


    Thank you!
    Yes - I thought of vlookup - that was plan B
    But wanted to try the nested formula since I usually have issues with parenthesis


    I appreciate you taking the time
    Cheers
    Leslie

    I have read through a few threads to match some IF Formulas to my challenge and thought I came pretty close
    I'm hoping it has to do with a , or ) misplaced since the error message is "too many arguments" and I am using 2013.


    Basically - based on cell A43 where employees calculated score is they are :
    [TABLE="width: 419"]

    [tr]


    [TD="colspan: 6"]Highly Effective: 3.5 - 4.0
    Effective: 2.5 - 3.4
    Needs Improvement: 1.5 - 2.4
    Ineffective: less than 1.5[/TD]

    [/tr]


    [/TABLE]


    My Formula try
    =IF(AND(A43>=3.5,A43<=4.0),"Highly Effective",IF(AND(A43>=2.5,A43<=3.4),"Effective"),IF(AND(A43>=1.5,A43<=2.4),"Needs Improvement"),IF( (A43<1.5,”INEFFECTIVE” ,"N/A")))))


    What am I doing wrong?
    Thank you

    Re: Parse URL


    Thank you so much
    Worked perfectly


    Quick follow-up question
    what does the ~ do in the first formula?


    Thank you
    Leslie

    I have a list of very long URLs which vary in length
    I need to parse the URL to 2 other columns breaking up the URL to its Path and (last occurrence of /)File name = 3 column URL/Path/FileName
    I have been playing with Right/Mid/Len Find but seem to miss the right combination


    I have attached a sample


    Thank you
    Leslie

    Re: Pivot table date counts


    Many thanks -


    Makes perfect sense - I must have been over complicating it
    Got it
    Appreciate your time



    -

    Quote from iwrk4dedpr;708385

    Your file you attached does not have the source data for the pivot table.


    Add a new column to the data table .... and add it to the pivot table

    Re: Pivot table date counts


    The names might change every day depending on the date of the filter
    can you give me an example of the countif formula I use it all the time but here I don't want a specific student if it is changing every day and the cell location might also change depending on the number of students and dates associated with each student


    thank you

    Working with a pivot table displaying a list of students and their tardy dates from the beginning of the semester
    each date is displayed and a total of tardies for each student
    Administration sometimes wants to filter only on a specific date so obviously the count changes


    Principal wants to display for each student their totals for the semester as well as filter on a specific date - not losing the totals column


    I know I can create a formula but if student list changes not sure if that is a solution


    Any thoughts


    Thank you

    Re: Pivot table updating to linked workbooks


    Afraid of that
    Thank you for your time
    I am familiar with PowerPivot and use it


    However these inventory files are in a SharePoint Library and our organization has not moved to supporting PowerPivot for SharePoint servers yet

    Re: Pivot table updating to linked workbooks


    Not sure if files can help-too big to send and smaller versions breaks all the links


    Master inventory -links to all school files (Thousands of rows some blank to add new items - not entirely blank as to break the source data range)
    Monthly inventory - is a Base school linking to multiple schools that report to it
    (These sheets are thousands of rows)


    Pivot tables are reporting out the multiple school inventory totals


    Problem
    source data does not change -- range is correct for pivot tables
    contents of the added inventory items display in the monthly sheet correctly


    Excel simply does not refresh the contents of these cells for calculations in the pivot table
    I understand the issue with pivots and adding/deleting rows/columns
    but if the pivot table is pointing to the right cells why not see the correct contents for calculation on refresh? or edit links update values?


    I tried to send files but links broke or file is too big


    Thank you

    I have created a manual inventory system (Dept won't buy real inventory system) in excel.


    I have monthly sheets linked to the inventory items workbook. When new items are added (always) the monthly sheets display the new items.


    the pivot tables have been setup for each month.


    I would think that refresh updates all the links. I have even gone to Data > edit links and updated values.


    Problem -- the links are updated on the monthly sheets for all new items/values Perfect
    but the pivot table does not update the items/values. on refresh or edit links to these new items
    I have to re-create the pivot table for the items/values to be current.


    Since I have over 127 sites - the idea of re-creating the pivot tables each month for each site is impossible


    Any ideas?


    Thank you

    I am building a summary report from files on a SharePoint Library.
    SUMIF does not work unless the linking files are open which I can't do.
    I searched and found out that SUMProduct is the function to use.

    I am having trouble converting my SUMIF formula to fit the SUMPRODUCT argument

    This is the model example I found online:

    =SUMIF('Z:\WUT\Daily Report\[2010 Monthly Rpt - CPM - Thru November only.xls]2010 Expense'!$E$231:$E$332,"M&R Parts D",'Z:\WUT\Daily Report\[2010 Monthly Rpt - CPM - Thru November only.xls]2010 Expense'!AE231:AE332)

    To

    =SUMPRODUCT(('Z:\WUT\Daily Report\[2010 Monthly Rpt - CPM - Thru November only.xls]2010 Expense'!$E$231:$E$332="M&R Parts D")+0,'Z:\WUT\Daily Report\[2010 Monthly Rpt - CPM - Thru November only.xls]2010 Expense'!AE231:AE332)

    This is my formula:

    =SUMIF('https://intranet.aacps.org/private/FoodServices/Inventory/Schools/[Annapolis Middle.xlsx]August'!$S$7:$S$638,"Frozen/Refrigerator",'https://intranet.aacps.org/private/FoodServices/Inventory/Schools/[Annapolis Middle.xlsx]August'!$R$8:$R$638)

    My SUMPRODUCT attempt:

    =SUMPRODUCT(('https://intranet.aacps.org/private/FoodServices/Inventory/Schools/[Annapolis Middle.xlsx]August'!$S$7:$S$638="Canned and Dry Goods")+0,'https://intranet.aacps.org/private/FoodServices/Inventory/Schools/[Annapolis Middle.xlsx]August'!$R$8:$R$638)

    Any Thoughts. I have attached a sample of my report each school has a separate file in sharepoint on our network - I would be linking to. Thank you

    Greetings-


    I am well versed in the auto fill for dates and standard fill series such as weekdays etc


    But what I am trying to do seems like an easy request but I cant get it to work


    I am trying to fill a series of dates for only tuesdays and thurdays
    I can do tuesdays only by a series step of 7 or thursday with a series step
    but not both


    Such as:
    [TABLE="width: 195"]

    [tr]


    [td]

    2/05/13

    [/td]


    [td]

    Tue

    [/td]


    [/tr]


    [tr]


    [td]

    2/07/13

    [/td]


    [td]

    Thu

    [/td]


    [/tr]


    [tr]


    [td]

    2/12/13

    [/td]


    [td]

    Tues

    [/td]


    [/tr]


    [tr]


    [td]

    2/14/13

    [/td]


    [td]

    Thu

    [/td]


    [/tr]


    [/TABLE]



    I thought if I selected the cells that had the date sequence it would follow it so only give me dates for tue / thursday/tuesday, thurday
    but it doesent and any combination of series steps and stop values does not seem to work


    what amI missing?


    Im sure it can be done in VB but this is for a user not me


    Many Thanks

    Re: Word 2010 Mail Merge multiple Rows to 1 Label



    ______________


    many Thanks - Im sure others have a similar need
    My workaround I'm thinking Access or Crystal reports

    Trying to help timekeeper to tally total hours worked by employee
    When doing a pivot for sum total hours worked for FY13 it does not calculate correctly
    I understand they formatted that column/custom h:mm
    but when I change the 8:00 hours to a number I come up with 0.33


    I am attaching a sample file
    I just want them to get a running total of hours worked/pay


    What am I doing wrong?


    Thank you