Conditional Formatting & Importrange using 2 spreadsheets

Question

I am requiring specific cells of a Google spreadsheet to be conditionally formatted based on information in a different spreadsheet.

The information is input into column J of a spreadsheet titled 'Haulage' and in a worksheet with the same name. I then require any cell in the range AE15:AH101 in a spreadsheet titled 'EMU Database' and in a worksheet titled '350' to be highlighted in a colour should the information in this cell match the information input into column J of the 'Haulage' spreadsheet/worksheet

ie '271' is in cell AE15 of the worksheet '350' (in 'EMU Database' spreadsheet) and when I input the same value into cell J3 of the worksheet 'Haulage' (in the 'Haulage' spreadsheet), AE15 is highlighted in the chosen colour.

I have tried using the following formula in the conditional formatting custom formula:

=A15=IMPORTRANGE("1AGMJxw5D5TNFI19dvpPCT0Jl6QNu5rRX1bcLqHYJ0-w",  "Haulage!J3:J")

But not been able to make it work unfortunately.

For reference, both of the spreadsheets have been linked together and access has been granted between the 2 spreadsheets

Progress report:

I have been playing around with the importrange options in making this conditional formatting work and I have managed to get it to work by importing the data into the same spreadsheet and then using a formula between worksheets as detailed below:

Data from "Haulage" spreadsheet and "Haulage" worksheet within this spreadsheet is transferred to "EMU Database", into a worksheet titled "Sheet5" using the standard importrange function. In the cells I require the custom formula to work I have then used the following custom formula:

=match(AE15,indirect("Sheet5!A1:A"),)

This offers a solution to my problem and works as I require it, however I would still like to find a solution to avoid having to transfer the data from one spreadsheet to another before the formula works. I have tried to amend the above formula in a number of ways to incorporate an IMPORTRANGE but without success. My most recent failing is below:

=match(AE15,indirect,importrange("1AGMJxw5D5TNFI19dvpPCT0Jl6QNu5rRX1bcLqHYJ0-w", "Haulage!J3:J"),)

If anyone has any ideas on how to make the importrange work on this match formula it would be appreciated as it keeps things tidier for me than having extra worksheets with duplicate data from another spreadsheet

Follow up information for @player0

Links to the test versions of the relevant spreadsheets are below:

Haulage SS = https://docs.google.com/spreadsheets/d/1elDxSOLSG-6U-jC6E5fgcKeJDNf9Kcu7GRGvK5p6fr4/edit?usp=sharing

EMU Database SS = https://docs.google.com/spreadsheets/d/1poWzun1TvIhdlrHQyM_tSvJzEHFDpvZz7O-Ta0mvAR8/edit?usp=sharing

Within the "Haulage" spreadsheet, only the 'Haulage' worksheet is of interest - please disregard the other worksheet.

Within the "EMU Database" spreadsheet you will find my current conditional formatting in cell range AE15:AO71 of worksheet '390'. This formatting is linked to the worksheet titled 'Sheet5' which contains the data that I have used the importrange formula (see cell A1 of this worksheet) to pull from cells J3:J of the Haulage spreadsheet.

Basically, what I require of the conditional formatting is for any data in cell range AE15:AO71 of the '390' worksheet which matches data entered in cell range J3:J of the "Haulage" spreadsheet to be highlighted with a coloured box - but I want this to happen without the need for the extra worksheet with the imported data in

player0 player0 · Accepted Answer · 2020-04-19T00:45:37

try:

=MATCH(A1, FILTER(
 IMPORTRANGE("1elDxSOLSG-6U-jC6E5fgcKeJDNf9Kcu7GRGvK5p6fr4", "Haulage!J3:J"), 
 IMPORTRANGE("1elDxSOLSG-6U-jC6E5fgcKeJDNf9Kcu7GRGvK5p6fr4", "Haulage!J3:J")<>""), 0)

Conditional Formatting & Importrange using 2 spreadsheets

1 Answers