Posts by carissa

  • How to use indirect formula to show value AND formatting?

    Re: How to use indirect formula to show value AND formatting?


    Quote from Carim;795205

    Hello Carissa,


    Unfortunately the Indirect() function cannot 'copy format' ... :dead:


    You might need an event macro to handle your situation ...


    ugh.. I know actually but hoping someone can miraculously give me the answer. So should I just move this thread to the macro section or not?

  • How to use indirect formula to show value AND formatting?

    I have attached the example file just in case my explanation is not clear:
    forum.ozgrid.com/index.php?attachment/72990/


    So everyday I need to e-mail to my customers their daily delivery order based on their request and contract. Usually, they only order what they requested and leave out their required take of daily order.
    So, I usually mark the "forced" order using orange color.



    Each file is based on each month. And each sheet is based on each day so I have July file with 01,02,03 etc as sheet name. Each sheet has company and products details and the amount kgs of their order.



    Each day I have to email them the "forced" order only to notify them what's coming to them.



    Now, I have separate sheet for e-mail like this:
    [ATTACH=CONFIG]72988[/ATTACH]


    My formula for cell D6:D10 is:

    Code
    {=IFERROR(INDEX(INDIRECT("'"&$K$4&"'!$A$2:$H$6"),(MATCH(1,(INDIRECT("'"&$K$4&"'!$A$2:$A$6")=$B6)*(INDIRECT("'"&$K$4&"'!$B$2:$B$6")=$C6),0)),$K$7),0)}


    The problem is that the orange color is not shown in that column and I need the color so I can use the filter by color option. I also want to keep the "Sheet Name" function so that I only have to change the sheet name to get the datas that I want since I will have up to 31 sheets.



    Is there a way to keep the indirect formula to show not only values but also the format of the referred cell?



    Thought of using paste link VBA but when I searched for the codes, it seems that I only find codes that refer to specific cells.
    But I don't mind using VBA if I can still use the "Sheet Name" function.





    Thank you in advance.