Columns populated by formulat not showing up in report

Tony Peacock

Hi,

I have replica sheets in different workspaces and I am trying to build a roll-up sheet.

Cell linking requires too much manual intervention, so at present I am creating a report.

In the report I need an additional column for 'Client' as the source sheets are client specific and do not have this column. I know that I can include the 'Sheet Name' column to identify the source, but I am looking for just a client name column.

Since I cannot add columns in the report that do not exist in the source sheets, I have tried the following:

I added a hidden column to the source sheet that populates the client name. In order to avoid manual population of this column value, I use a function to populate it based on the primary column having value.

Here is my issue:

The report does not display the values for 'Client' when the source sheet is populated this way.

Any suggestions?

Dale Murphy

Tony;

I often build what I call compatibility columns to get columns from two different sheets to match up. Reporting is generally reliable, but I have trouble when the "matching" columns from different source sheets are:

• not named exactly the same. EXACTLY (e.g. tactic does not equal tactics)
• NOT the same type. E.g. Drop down (single) does not equal drop down (multi-select)

To trouble check, try replacing the "" in your IF with a value such as "No Name". I have trouble with so-called blanks all the time.

Do you know how to check if the Report builder recognizes a given column in all source sheets?

Dale

SoS | Dan Palenchar

A report should be able to include any column that exists in at least one of the source Sheets it is built on. Are you sure you are referencing the proper Sheet(s)?

Adding a hidden formula to a Sheet and displaying it in a report is standard and should be doable.

Tony Peacock

Hey Dan,

I hear you; I would expect this to be standard functionality as well, however I have a section of my report missing data in one column.

Source Sheet (A): Client specific list of records with the [Client] column populated manually.

Source Sheet (B): Client specific list of records with the [Client] column populated with a column formula

=IF(ISBLANK([Primary]@row), "", "Client Name")

In the report the Client data appears for source sheet (A), but not for (B)

Tony Peacock

Anyone else have any suggestions?

Has anyone tried what I explained and reached different results?

Dale Murphy

Tony;

I often build what I call compatibility columns to get columns from two different sheets to match up. Reporting is generally reliable, but I have trouble when the "matching" columns from different source sheets are:

• not named exactly the same. EXACTLY (e.g. tactic does not equal tactics)
• NOT the same type. E.g. Drop down (single) does not equal drop down (multi-select)

To trouble check, try replacing the "" in your IF with a value such as "No Name". I have trouble with so-called blanks all the time.

Do you know how to check if the Report builder recognizes a given column in all source sheets?

Dale

Tony Peacock

Thanks @DMurphy,

I followed both pieces of advise:

• I replaced "" with "None"
• I confirmed that they were both the same column type

The 2nd suggestion is what solved my problem. The static populated column was a list type and the dynamically populated column was a text/number. Once I made both columns text/number and dynamic it resolved the issue.

Side Note: I had to remove the sheets from the report and add them again before it worked completely.

Dale Murphy

@Tony Peacock

Excellent sleuthing! Yes, I sometimes have to unload and reload sheets; not sure if I am just impatient or if the report builder can't detect changes easily.

Cheers,

dm