Link existing table

LTH

Hi,

I have a table which is linked to database and the table would have new row add. I want a table that have the same data and I can insert a new column to calculate the data.

I've found some ways: Cell linking, Report, Connect Your First Sheet to a DataTable.

If I use Cell linking, I need to maintain it manually. Report, on the other hands, I can't insert a new column for calculation. I'm not sure if Connect Your First Sheet to a DataTable can work. It seems I don't have permission.

Another questions, there is a column named as Request items. Each case contains different items and each of them is separated by ",". I want to draw a bar chart to see the quanty of each items, but I don't know how to do.

Could you kindly give me some suggestions?

Georgie

Hi @LTH,

To link data between two sheets, you could use INDEX MATCH formulas, or, if you use table view, you could Create column links between the two sheets.

For more information about using INDEX MATCH formulas, check out the “Lookup one cell using a matching value” section of this help article: Formula combinations for cross sheet references. I’d also recommend taking a look at this Best Practice post here in the Community: INDEX and MATCH across two sheets: a detailed explanation.

Regarding your question about counting the quantity of each request item and then creating a bar chart, there are two ways you can achieve this:

1. Create a sheet summary with fields containing formulas to count the quantity of each request item, create a sheet summary report, and use that report as a source for the chart.
2. Create a metrics sheet using cross-sheet formulas to count the quantity of each request item, and use the metrics sheet as a source for the chart.

I’ll explain both options further below.

Option 1

First, you’ll need to create fields within the sheet summary for your existing sheet. You’ll need one field for each possible request item.

Next, you’ll need to create COUNTIF formulas in each field, with each formula counting the respective request item. Your formulas will be similar to:

• =COUNTIF([Request Items]:[Request Items], CONTAINS("Basic information (location. die size...)", @cell ))

See the screenshot below:

You can copy the formula into all other fields and just change the “search_for” text within the CONTAINS Function for each formula.

Once you’ve got all sheet summary fields set up with the formulas, the next step is to create a sheet summary report. Create a new report and ensure you select the “Sheet Summary Report” option. Then, select your existing sheet as the source sheet, and check the box next to all fields that you want to include in your chart.

You should then see one row populate with the data from your sheet summary, like my example below:

Now, you can create a dashboard and add a chart widget, then select your sheet summary report as the source. Your chart should look similar to below, but you can customise it using the editing panel on the right:

Reports are charted in their entirety, so if any new request items are added to your sheet, you’d need to add the new field into your sheet summary with the relevant formula, and you’d need to add that column into your sheet summary report, but this data would then automatically be added to your chart.

Option 2

For option 2, the first step is to create a new sheet which will be our metrics sheet - the source for our chart widget. 

You could name this “Request items metrics” or similar. In the new sheet, list all request items in one column. In the next column, create a cross sheet COUNTIF formula. As you start typing the COUNTIF formula, you’ll see a “reference another sheet” prompt:

Click this and locate your original sheet, then select the “Request Items” column header so that the entire column is highlighted, edit the Sheet Reference name to something clearer, for example “Request Items”, then click Insert Reference. 

Then, add a comma, type CONTAINS( and then click on the cell in the same row that holds the value you want to search for - this will populate the formula with “Item@row ” (or similar, depending on what you named the column that lists your items in the metrics sheet). Finish the formula off with “, @cell ))”.

So, your final formula would be similar to:

• =COUNTIF({Request items}, CONTAINS(Item@row , @cell ))

Then, you can either drag this formula down to other cells in the same column, or you can convert it to a column formula so that it applies to all rows. You’ll see the number of each item populated. Take a look at my example below:

You can then create a chart using this metrics sheet as the source, and again, you can customise the chart as desired. With sheets, when you select the sheet you’ll be using as the chart’s source, you also highlight the range of data you want to chart. This means that if new fields are added to your original sheet and you want to chart the new data too, you’ll need to add the item to your metrics sheet, ensure your formula is applied (it will be applied automatically if you made it a column formula), and then edit your chart to reselect the data within the sheet that will be used, so that the new data is included.

My screenshot below shows two charts - one created from each option described above - and you can see they display the data the same way, so it’s just a matter of preference for which option you decide to take!

Hope that helps!

Georgie