Pulling data from one smart sheet to another to create chart on results

David Clunie

Hi all

i need help on how to pull data together.

I need this data to be grouped on a chart to either show qtys or percentage which I cannot do with reports.

I have a column that shows my individual products that we are working on and this is abased on a formula in each cell to formulate the data . This column is called Model Name

Formula

=[Unit name]@row + " " + [Unit Size]@row + " " + "X " + Bed@row

I need a way of reporting on each individual variance found and if a match add to count

Unit Name is constant and pulled from a drop down

Unit Size is unknown and can vary ?

Bed@Row is constant and is from a drop down.

David Clunie

Hi,

Yes sorry for delay exactly as you said. If I get the functionality on how to do one then im sure I can adapt for others

thanks

Paul Newcome

Ok. So to pull a variable list, we need to first pull the string into the other sheet and then parse it out down a column.

To parse it out down a column, we need to "pre-fill" the sheet with formulas. That's why I ask what the max number is. You said 30, so I suggest prefilling 40 rows.

HERE is a link to a parsing solution. Once you have parsed out the list, you will use a COUNTIFS formula with cell references to get your counts.

Paul Newcome

Happy to help. 👍️

Paul Newcome

I'm not sure I follow. Are you looking to pull one row for each unique instance and then add a count for each of them?

David Clunie

Hi, Paul,

Yes, I need to formulate how many of each specific type is developed and how many.

Ideally, I want it in a Graph and a pie chart as an example below

Paul Newcome

Ok. What is the maximum number of unique entries you anticipate at any given time?

David Clunie

At the moment I have 120 projects so far but will grow over the year, and .probably about 500 rows to be safe.

Paul Newcome

How many UNIQUE entries though? Do you anticipate having a graph that displays counts for up to 500 different items, or could those 500 projects be condensed down into a list of say 20 or 30?

David Clunie

Hi Paul ,

Yes in reality probably would be about 30 in a list as they are separate projects for each customer. Although they are separate models will most probably equate to the same size models in most cases.

I would like to think it will be less but we never know.The graphs would be handy to understand the customer insight and trends.

I will probably also separate graphs out as their Range core name too break it down.

One for Acorn, another For Charnwood models ...etc

Paul Newcome

So you are thinking 30 total, or 30 for Acorn, another 30 for Charnwood, etc...?

David Clunie

Hi,

Yes sorry for delay exactly as you said. If I get the functionality on how to do one then im sure I can adapt for others

thanks

Paul Newcome

Ok. So to pull a variable list, we need to first pull the string into the other sheet and then parse it out down a column.

To parse it out down a column, we need to "pre-fill" the sheet with formulas. That's why I ask what the max number is. You said 30, so I suggest prefilling 40 rows.

HERE is a link to a parsing solution. Once you have parsed out the list, you will use a COUNTIFS formula with cell references to get your counts.

BullandKhmer

https://community.smartsheet.com/discussion/comment/273631#Comment_273631

Thanks for this, saved me hours

Paul Newcome

Happy to help. 👍️