IMPORTRANGE with source formatting

[SammyGranstrom]

I have seen in the forums how to do a workaround for IMPORTRANGE to keep source formatting. Is there any add-on feature that would do what I will explain.

My role is as a mid-level manager is to check progress and data found in many different workbooks. To streamline my check-ins, I wanted to create a dashboard instead of opening up each workbook. My managers grade our client status by color each week. Currently, there is no way if I import the data to see if they update the color grade. Essentially I want to create a "window," if you will, in one workbook on one sheet to see, at a glance, the reporting tools found in other workbooks, including the source formatting.

[CloudGuardians]

Hi Sammy,

IMPORTRANGE cannot import formatting because that's not in the data itself, but in the spreadsheet instead. IMPORTRANGE doesn't do a "copy/paste", it queries the source and brings the data to the destination spreadsheet.

With that said, depending on the criteria used to apply the different colors you might find a solution in conditional formatting. You could, for instance, make a cell green or red (or any other color) if the content matched the specified criteria (equals to, greater than, etc, or even matching something variable, using a regular expression).

Obviously this is not a in-depth tutorial but I hope it can point you in the right direction. If you have specific questions you can also share a test spreadsheet with nuno@cloudguardians.com and I'll have a look.

Regards,

Nuno

Cloud Guardians

[stimms]

As @CloudGuardians has said, importrange is only pulling the cell data which does not include formatting. If conditional formatting is not already in use then I suggest implementing this so it can be replicated on your sheet. So if the grading for example is A-D with A being green then you would create conditional formatting to change the colour of the cell/row to green when A is input (best to set up data validation for this cell too so it becomes a dropdown, means users can't type in 'a' instead of 'A' when only 'A' is acceptable for conditional formatting).

This means that 1. Users don't need to change the cell colour manually (protentially using different shades by accident) and 2. You can replicate the same logic on your importrange sheet so that when the cell data of A is pulled from the source sheet it will automatically colour the cell in the same way.

What I have done in the past when running importrange from sheets that have conditional formatting is 'Copy' the source spreadsheet which will include all the formatting and conditional formatting. Then I delete all the cell data and then run an importrange from the source.

Not ideal of course because if a formatting change is made on the source sheet you'll need to replicate it manually on your importeange sheet.

[MartinMolloy]

You could try my SheetsIE add on. It will allow you to set up an automated process top import data from several sheets into one - including the formatting. Details here https://sites.google.com/mtmomk.co.uk/mtmomk/sheetsie