Posts by dRaz

    Re: Filtering Pivot Table by date in cell


    Ok,


    After hours and hours of messing around with this I figured out what was wrong:


    It is all working as it should - the problem is, it is converting the date in AH11 to US format mm-dd-yy from dd-mm-yy and placing that in the filter.


    Can anyone shed any light on why this would be happening?

    Hi,


    I cannot understand why this code is not working, please can somebody point out the mistake:



    The pivot table is filtering but it is only filtering out the blanks, not any dates before the date in AH11....which is the idea.


    Thanks in advance.

    Re: Minimum Date if cell contains part of another cell value


    Ali - thank you for taking the time to reply.


    Unfortunately this is giving me a NAME error - is this function compatible with excel 2010?


    Any suggestions?


    Also, I would like $C$7 and $A$7 in your example above to be open ended which is why I was trying to implement OFFSET($U$6,0,0,COUNTA($U:$U)-COUNTA($U$1:$U$5),1) into the solution.

    Hi,


    This has been driving me nuts, please can someone take a look.


    Column U contains unique codes made up of parent codes and child codes as such:


    [TABLE="width: 500"]

    [tr]


    [td]

    Code (Col U)

    [/td]


    [td]

    Parent

    [/td]


    [td]

    Date

    [/td]


    [td]

    Minimum

    [/td]


    [/tr]


    [tr]


    [td]

    TEST100

    [/td]


    [td]

    1

    [/td]


    [td]

    10-08-16

    [/td]


    [td]

    08-08-16

    [/td]


    [/tr]


    [tr]


    [td]

    TEST100.10

    [/td]


    [td][/td]


    [td]

    08-08-16

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    TEST100.20

    [/td]


    [td][/td]


    [td]

    25-09-16

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    TEST200

    [/td]


    [td]

    1

    [/td]


    [td]

    26-10-16

    [/td]


    [td]

    26-10-16

    [/td]


    [/tr]


    [tr]


    [td]

    TEST200.10

    [/td]


    [td][/td]


    [td]

    28-10-16

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    TEST200.20

    [/td]


    [td][/td]


    [td]

    10-11-16

    [/td]


    [td][/td]


    [/tr]


    [/TABLE]


    It is the minimum column I am trying to achieve.


    What I need is:


    IF parent THEN search all codes in U6:U for codes containing the parent and return the minimum date.


    The closest i have gotten is:


    MIN(AB6:INDEX(OFFSET($U$6,0,0,COUNTA($U:$U)-COUNTA($U$1:$U$5),1),MATCH(TRUE,(OFFSET($A$6,0,0,COUNTA($U:$U)-COUNTA($U$1:$U$5),1)=1),0))),"")


    A = parent column
    U = code column
    AB = dates


    I would much appreciate some help here - thanks.

    Hi All,


    Looking for some help here please:


    Context


    I have a workbook containing one master sheet "Master" and several other sheets i.e. "1", "2" etc
    There are more than 2 sheets to pull from and not all sheets in the workbook should be included in the loop
    All of the sheets have the same layout and formatting including the master
    The range I am looking at is B10:P109 on each sheet.


    Requirements

    For each non-blank in column B, copy B:P and paste values to next empty row in row B on the master sheet.
    Once you reach a blank in column B on sheet 1, repeat the process on sheet 2 and so on.


    If you need any further information please ask, I will be following closely.


    Thanks.


    Shaun

    Re: Sum values from multiple sheets based on date


    For some reason I am getting a circular reference.


    My sheetnames begin at $BF$2 because I don't want the master sheet to be included.


    I went to name manager and input =OFFSET(Master!$BF$1,,,COUNTA(Master!$BF:$BF),2)


    Then when I use your new formula I get a circular....?

    Re: Sum values from multiple sheets based on date


    I guess my only outstanding question is the one regarding the changing range of the sheet list - any way to make this adjust?


    I wanted to use X1:X500 to make it more future proof, but that returns #REF

    Re: Sum values from multiple sheets based on date


    Hmmmm, ok on test I get no errors but only when I make the "sheet list" range the exact size - I expect this to grow and shrink on a weekly basis so this isn't ideal (any clues?).


    Also, it is not bringing back any results.


    I can see that Matt (for example) whose hours are posted on all sheets on row 20 has some hours planned for w/c 10/02/14, but it is still coming back blank - is this because of what you were alleviating to above?

    Re: Sum values from multiple sheets based on date


    I assume though that I could copy that forumla and slightly amend for each row (there are only 20 and I would only have to do it once?


    Am I right?


    Thank you so much for your help by the way - I will test now!

    Hi all,


    I thought this would be easier than it is, but it is causing me problems.


    I have multiple sheets in a workbook which are all structured identically.


    D8:BC8 contains a date for every monday over a 52 week period.


    D10:BC30 contains hours related to those weeks quoted by different team members for a Project - 1 team member per row.


    These projects can start at different times, which is causing the problem because I am trying to sum the total hours always from next Monday and going forward for 52 weeks. So in the master sheet - cell D8 would contain 10/02/14 right now.


    What I need to do is look across all sheets for the hours quoted for that week for each person.


    So the formula for each row would be SUMIF(D$8=any value in D8:BC8 across all sheets THEN sum the hours)


    So far I have:


    =SUMIF('*'!D8:BC8,D8,'*'!D10:BC30)


    This is not recognising D8:BC8 or D10:BC30 as ranges though so it is returning #VALUE.


    Any ideas please? Banging my head here.


    Shaun

    Hi,


    I am trying to implement a VBA code which inserts a row across multiple sheets and then copies down the formulas from the above row.


    The code I am using at the moment is very cumbersome and wondered if there was a way of condensing it - seems there would be?


    Code
    .Cells(Target.Row, Target.Column) = Target.Value
    .Cells(Target.Row, 1).FillDown
    .Cells(Target.Row, 2).FillDown
    .Cells(Target.Row, 3).FillDown
    .Cells(Target.Row, 4).FillDown
    .Cells(Target.Row, 216).FillDown
    .Cells(Target.Row, 217).FillDown
    .Cells(Target.Row, 218).FillDown
    .Cells(Target.Row, 219).FillDown
    .Cells(Target.Row, 220).FillDown


    What I would like to do is condense to rows 1-4 and rows 216-220


    Is this possible? :)


    Thanks in advance.


    Shaun


    Apologies, but also, just to fill down formulas.


    If there was a way to condense to the entire row then that would be fine as long as only formulas were copied down.


    Thanks again.