Linking data from master tab to multiple tabs

Question

I have a master spreadsheet which contains a lot of data/columns.

I want to be able to automatically transfer portions of that data onto 2 other tabs for other people to view and for them to automatically update when the source data updates. I have tried the copy and paste as a link which partially works. However, if there are blank cells in the source data, the pasted data does not show as blank and will populate with a random number/date etc which I don't want - I want the format of the source data to copy across exactly.

To further complicate this:

• on one of the tabs, I only want to copy data across if column F has "yes" selected (I'm looking at open cases only for this tab so don't want to show the closed cases).
• If possible, I want to be able to select some of the columns to paste, not all of them.

Does anyone have any suggestions as to how I can make this work?

Answer 1

Formula can not get the cell format, for date and other cell format, you need to reset them in new sheet.

=IF(FILTER(Sheet1!A2:F9,Sheet1!F2:F9="Yes")=0,"",FILTER(Sheet1!A2:F9,Sheet1!F2:F9="Yes"))