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:

{=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.

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

Carim 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?

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?

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

Quote from Carim;795209

To be on the safe side, just attach a sample workbook with your next message ...:wink:

I did.. https://ufile.io/l4env
sorry I don't know how to attach file.. hehe

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

Quote from Carim;795211

Below is an explanation ...

How to Attach a Sample Workbook
To attach a workbook:
1. Click 'Go Advanced' at the bottom right of the Reply Box.
2. In the new window click the Paper Clip Icon.
3. In the new window that pops up click 'Add Files'.
4. Click 'Select File' and browse to the required file.
5. Click 'Upload File'.
6. Click 'Done' when the selected file appears in the box at the bottom of the window.

The maximum file size allowed is 3MB, if your file is larger than this you can try zipping it to reduce the size. If still too large, or if your actual file contains sensitive data, then create a sample workbook with reduced amount of data or with sanitised data.
When creating a sample workbook it is essential that the structure of the actual workbook is retained.
• If data has been sanitised, then the sanitised data must be of the same type as the original (text still text, numbers still numbers, dates still dates etc.).
• The Layout of data on each sheet remains the same.
• If actual data contains blank rows, columns or cells then include the same in the sample data.
• If a cell contains a formula keep the formula in the sample, in other words do not create the sample file by pasting just values from the actual file.

Display More

Thanks! Did it already.. Can you tell me how to cancel editing too? lol.. couldn't find the button.. or even to delete a reply..

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

Quote from Carim;795213

Thanks for your file ...:wink:

Do we agree, both the copy and the formatting should take place as soon as you select the Column # in cell D5 ...?

Editable data are "Sheet Name" in cell K4 and Company's name which is in Cell D5. The "Column #" value depends on Cell D5.

But yeah.. both the copy and formatting should take place as soon as I change the value on one of the cells, either K4 or D5.

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

Quote from Carim;795217

Attached is your sample workbook ... to be tested ... :wink:

Hope this will help

THANK YOU SO MUCH! U'VE SAVED MY LIFE!
Is it possible for you to tell me what each of those codes mean? I really want to learn VBA cause it comes in very handy.

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

Quote from Carim;795222

Attached is your workbook with the explanations of each instruction ...:wink:

:smile:

Hey.. 2 questions:

x = Application.Match(Sheet3.Range("D5"), Sheet3.Range("M3:M8"), 0) + 4

Why is it +4?

Sheets(Sheet3.Range("K4")).Range(Sheet3.Range("M" & x) & "2" & ":" & Sheet3.Range("M" & x) & "6").Copy

Why it "M" and "2" and "6"?

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

Quote from Carim;795226

1. The +4 is in fact +2 plus +2 ... the first +2 is due to the placement of your Table starting in row 3 ...

Which table are you talking about? Row 3?

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

Quote from Carim;795226

Hello Carissa,

To answer your two questions:

1. The +4 is in fact +2 plus +2 ... the first +2 is due to the placement of your Table starting in row 3 ... and the second +2 is needed since within your source worksheets, all your columns A,B,C,D are in fact place 2 columns on the right ... A is in Column C, B is in Column D, etc ...

2. The Sheet3.Range("M" & x) is the equivalent of the Indirect function ... since the Column Letter which is to be used is located in Column M ... and your Source worksheets have their data always located in Row 2 to Row 6 ...

Hope these explanations are clear enough ...

Cheers

Carim

Display More

Oh and in your attached file, when I change the Company to E or F, there's a debug error

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

Well this is the real file with some tweaking cause I cannot show you what it is haha.
I did change the range etc basen on my understanding of your solution. What did I do wrong?[ATTACH=CONFIG]72998[/ATTACH]