Posts by jarko28

    Re: Convert multiple columns to multiple rows with identifier


    Patricia,


    Please see attached file. Enable Macros and Press button on sheet1, then view results in sheet 2




    Let me know if this helps.


    Thanks!

    Re: Dynamic INDEX and MATCH?


    apo,
    One option is to replace the "Monday$A2" portion with:


    TEXT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),"ddd")


    The "-1" in the formula takes you 1 column to the left, so it would be appropriate in column B.


    With respect to VBA to auto populate the cells in the Roster Sheet, why would you not want to have the formula there and just have an IF A2="", then entire formula is blank? I guess I am not clear on how the weekday sheets will be populated and deleted, via vba or are they to be auto fed in from another sheet, for example employees sheet?


    Let me know if the above is what you were after...That formula you can now use on all your weekday sheets...

    Re: Macro to link cell formula versus just copying the content from another cell


    Glad it worked.


    For anyone who may be facing the same problem:


    Re: Macro to link cell formula versus just copying the content from another cell


    Can you try this:


    Re: Macro to link cell formula versus just copying the content from another cell


    tennisc,


    Where B4= desired output on current active sheet and C14= input from next sheet :


    Re: Dynamic INDEX and MATCH?


    apo,



    Formulas to be dragged down each column as far as you need them -do not drag across the row


    On sheet Monday:


    Formula B2:


    =IFERROR(IF(OFFSET(INDEX(Roster!$C$9:$P$23,MATCH(Monday!$A2,Roster!$A$9:$A$23,0),1),0,0)=0,"",OFFSET(INDEX(Roster!$C$9:$P$23,MATCH(Monday!$A2,Roster!$A$9:$A$23,0),1),0,0)),"")


    Formula C2:


    =IFERROR(IF(OFFSET(INDEX(Roster!$C$9:$P$23,MATCH(Monday!$A2,Roster!$A$9:$A$23,0),1),0,1)=0,"",OFFSET(INDEX(Roster!$C$9:$P$23,MATCH(Monday!$A2,Roster!$A$9:$A$23,0),1),0,1)),"")


    Formula D2:


    =IFERROR(IF(OFFSET(INDEX(Roster!$C$9:$P$23,MATCH(Monday!$A2,Roster!$A$9:$A$23,0),1),2,0)=0,"",OFFSET(INDEX(Roster!$C$9:$P$23,MATCH(Monday!$A2,Roster!$A$9:$A$23,0),1),2,0)),"")


    Formula E2:


    =IFERROR(IF(OFFSET(INDEX(Roster!$C$9:$P$23,MATCH(Monday!$A2,Roster!$A$9:$A$23,0),1),2,1)=0,"",OFFSET(INDEX(Roster!$C$9:$P$23,MATCH(Monday!$A2,Roster!$A$9:$A$23,0),1),2,1)),"")


    Formula J2:


    =IFERROR(IF(OFFSET(INDEX(Roster!$C$9:$P$23,MATCH(Monday!$A2,Roster!$A$9:$A$23,0),1),-1,0)=0,"",OFFSET(INDEX(Roster!$C$9:$P$23,MATCH(Monday!$A2,Roster!$A$9:$A$23,0),1),-1,0)),"")


    Formula K2:


    =IFERROR(IF(OFFSET(INDEX(Roster!$C$9:$P$23,MATCH(Monday!$A2,Roster!$A$9:$A$23,0),1),-1,0)=0,"",OFFSET(INDEX(Roster!$C$9:$P$23,MATCH(Monday!$A2,Roster!$A$9:$A$23,0),1),-1,0)),"")

    Re: Dynamic INDEX and MATCH?


    Apo,


    Can the dates be changed to a standard format or do they need to remain as they are? Also, are you referring to date as MM/DD/YYYY as per the standard excel format? so 1/4/2013 on your sheet would be Jan 04, 2013?

    Re: Dynamic INDEX and MATCH?


    apo,


    Please post an actual excel sheet example with the manual input and output (the result as it should look).


    It's not very clear to me what it is you want to accomplish. What will drive the the Roster sheet to be deleted and do you need the Roster sheet to have the headers between each employee - this is not standard formatting, you ideally want the roster sheet to be headers top row only.


    also, if you want to use INDEX MATCH, you may not want to have merged cells in headers.


    thanks.

    Re: how to combine function if and and


    Hi,


    Please post a generic example of your data and explain what you are trying to calculate.


    If i understand correctly, your condition is that a cell is equal to a range, which I don't see why or how this would work. Second portion is that you are assigning the conditions but not the second part of the formula which is the If, then statement.


    Thanks

    Re: Macro to link cell formula versus just copying the content from another cell


    tennisc,


    Try:

    Code
    Range("B4").Value = Sheets(ActiveSheet.Index + 1).Range("C14").Formula


    Or if you want to declare the variable



    Let me know if this works.


    Thanks.