Editable Cells in a Report and Possible Cross Sheet Formulas

Trang Nguyen · 09/07/22

Hi,

I have 2 questions:

1) I have a report pulling 2 sheets with similar columns (same name and type) to pull cumulative data but also allow the end user to provide data back to the source sheet. In the screen shot below, rows that do not have numbers in the Revenue Budget column has the ability to edit the cells in the last 2 columns; whereas, rows that have Revenue Actuals AND Budget numbers do not have the ability to edit the last 2 columns. Any thoughts as to why?

2) Because the report is pulling from 2 source sheets, am I able to create formulas across the columns that are pulling data from separate sheets? i.e. referring to the screen shot below, IF the last 2 columns were editable (which I want but can't i.e. issue #1 above), would I be able to create the formula to calculate the Variance if the Revenue Actuals and Revenue Budget columns are pulled from different source sheets?

Genevieve P. · 09/12/22

Hi @Trang Nguyen

You would need to create the formula in the Sheet, not the Report, and then the result will display in the Report, but only on the rows from that sheet.

Here are some visuals that may help:

Sheet A has two columns, a Primary Column and a secondary one that we're not using.

Sheet B has two columns, a Primary Column and a column where we'll put a formula in a minute.

When we bring both sheets into one report, each row is associated only with its one, underlying source sheet.

This means that if I add "Formula Column" to my Report, it will only show with blue cells, and Sheet A will show white (instead of orange) because that column doesn't exist.

Notice that these rows do not combine in a Report. Each row is still distinct and only connected to one of the two sheets.

It also means I can only edit the cells that are blue, that go with the column that exists. Now, I can change/update Sheet A to also have a column titled "Formula Column":

This then updates the Report to have those cells available to edit, as well!

However, to have a formula in that column, I have to write the formula in the Sheet. If you try to write a formula in a Report it will just show the text of the formula and won't calculate:

But if you write that formula in your Sheet, the calculation will then appear in the Report.

In the Sheet:

In the Report:

Does that make more sense?

Trang Nguyen · 10/12/22

Sorry this is a severely delayed response and appreciation for your time. I'm going through some of my posted questions. Yes your last response helped explain it better. If I removed the "sheet name" field in your example, it did combine the sheets, but once I put a formula or other text in any of the other fields after the primary, is when they would separate so it doesn't help me solve for my inquiry, but I figured it wasn't possible. I wasn't sure if there happened to be a workaround or something I wasn't thinking of. Thanks again!

Genevieve P. · 09/09/22

Hi @Trang Nguyen

The way I think about Reports is that they're a window into the underlying Source Sheets. This means that each individual row can only be associated with its underlying sheet.

If a column doesn't exist on one sheet, that means the row from that sheet does not have access to that column.

1) It sounds like the columns may not have the exact same name in both sheets.

If the columns are different by even one character, e.g. in one sheet there's no space between % symbol and ) but in the other sheet there is a space, this means that the two columns you selected in the Report may only be associated with one of your sheets.

Can you check the Columns section of the Report to see if there are two similar column names that are not selected? This would explain why the cells appear uneditable for specific rows.

2) Formulas only apply to the underlying sheet.

If you build a formula in Row 1 that's associated with Sheet 1, then that formula is only on Row 1 Sheet 1. A formula on Row 1 (Sheet 1) cannot look into Row 2 (associated with Sheet 2). If you reference "Row 2" in your formula, it will look into row two of the underlying sheet. Does that make sense?

To build formulas across multiple sheets, you would need to do this in the Sheet (not in a Report) and use cross sheet formulas. See: Create cross sheet references to work with data in another sheet and Connect Live Data Across Sheets Webinar

Cheers,

Genevieve

Trang Nguyen · 09/12/22

Yeah they are all the same name and type columns. I learned that early on :) So what I screen shot in my last post is the combination of the two sheets pulling in the same row. I'm just not able to edit the blank cells the combo data is pulling together.

Actuals is one sheet. Budget/Forecast is another sheet. I want to be able to have the end users enter see both and provide monthly actuals and/or update any budget/forecast for future months. I've created the formula on the source sheet, so as they provide numbers, the variance columns will automatically calculate as the actuals are fed into the source sheet.

I was just curious to know if it was possible to create formulas in a report and that formula appear on the sheet the column is pulling from. The variant columns are on the Actuals sheet only so it's only one sheet it's sourced from.

Genevieve P. · 09/12/22

Hi @Trang Nguyen

Thank you for clarifying! I understand now that you mean you have cell links or a cross sheet formula in your second sheet to bring the first sheet data in, enabling you to see Sheet One's data in the purple cells of the Revenue Actuals column on the same row as Sheet Two's data in the purple cells of the Budget column.

Since you note that the Variant columns are only located on the Actuals sheet (Sheet One) this is why you cannot edit those cells next to the purple cells (Sheet Two). Those columns don't exist on Sheet Two so there's no where for the entered data to go.

If you add those two columns with the same name in Sheet Two, then you'll be able to edit them in the Report. However, if you want to add a formula, that formula will need to be added to the Sheet.

Let me know if that helped explain Report functionality! If not, it would be useful to see a screen capture of just Sheet Two (the one with the purple cells and cell links/formulas).

Cheers,

Genevieve

Trang Nguyen · 09/12/22

If I add the same columns to the Budget sheet (Sheet 2) to make the cells editable on the report, and create the formula in the report itself, it will put the formula in both sheets? How will it know which sheet the formula goes to? Or will it link both ways between both sheets?

Genevieve P. · 09/12/22