Can I populate 1 column in a table with data from a different table?

[Lisa Hooper]

I have a RAG status table with data that looks like Table 1 below. I have another table in a tab on the same page that looks Table 2 below. Can I have the January column in Table 1 be populated by the values in the Status column in Table 2?  

Last question is can I have the value of G color the cells with a green background automatically? 

Thanks for any help!

[Katerina Kovriga _Stiltsoft_]

Hi there,

As this thread mentions our Table Filter and Charts for Confluence app, we are happy to introduce its new macro – Table Spreadsheet.

The macro allows you to work with fully functional Excel spreadsheets right in Confluence.

You’ll be able to use cells’ formulas, filters, conditional formatting, etc., create pivot tables and charts from the page view and edit modes.

The Table Spreadsheet macro is available for Cloud and Server/Data Center.

[Katerina Kovriga _Stiltsoft_]

Hi @Lisa Hooper ,

We can suggest trying our Table Filter and Charts for Confluence app.

At first you may reuse these tables using the Table Excerpt/Table Excerpt Include macros (especially the second one - it is very convenient if your source tables are placed on different pages). For simplicity, I've placed them on the same page - the first step is to wrap your tables (reused or not) into the Table Toolbox macro:

The second step is to wrap your tables into the Table Transformer macro inside the Table Toolbox macro and use the following customized SQL query:

SELECT *,
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T2.'Jan, 2022' LIKE "G" THEN "#abf5d1"
WHEN T2.'Jan, 2022' LIKE "Y" THEN "#fff0b3"
ELSE "#ffbdad"
END
+ "}" + T2.'Jan, 2022' + "{cell}")
AS 'Jan, 2022'
FROM T1 OUTER JOIN T* ON T1.'Metric' = T*.'Metric'

This query allows us to look up tables by the unique "Metric" column and color the cells' background based on the status for the "Jan, 2022" column.

As a result, we'll see the following table:

Then we wrap the Table Transformer macro into the Table Filter macro, hide the unwanted columns and filter the combined result table, for example, by statuses:

Hope this helps your case.

[Lisa Hooper]

The answer looks pretty clean.  However, we don't have that macro and I don't think we'll be allowed to purchase another macro.  

Are they others that have done all or parts of this without additional macro support?

[Katerina Kovriga _Stiltsoft_]

With the standard set of macros you'll be able to reuse your tables and place them on the master page (check the Excerpt/Excerpt Include macros).

The conditional formatting is also not possible out-of-the-box.