Merging multiple data elements from different sheets into a single view

Vasiles Kiosses · 04/30/20

Fellow Smartsheet users. I have been given an interesting challenge recently. A group gave me an excel file which a single sheet. It is built like an on-boarding form that has a series of elements. I thought it would be 2 dimensional however there are added columns that answer details about the elements in the column to the left of it.

The challenge is using smartsheet to record this data and make it presentable whereby one excel sheet equals one customer, thus present in smartsheet the combined data for one customer. I should be able to add new, or edit existing customer. A second presentation whereby I can search all customers for specific items they have in common, a report for example "which customer has this item in common". The latter report would be easy if it was all on one sheet.

I thought that you could put all the elements into one row per customer, but you end up with 137 data elements (columns) per row. If you multiply that out by say 200 rows, then you end up with 27400 cells, which I believe is exceeding the limits of smartsheet.

At first I thought about splitting the excel sheet into sections and each section was single smartsheet. I could create a report to list out all the data elements by combining all the columns of all sheets however I end up with a row per section for that customer.

Would anyone have an idea of how I could organize the data so that a: I can enter and edit customer information and b: sort and report on it. Additionally a way to show the data in a nice visible format for each customer. I think that smartsheet can only do this by using a dashboard. If so how could one use the dashboard such that you can click through each customer and the data updates on the dashboard for that customer.

I maybe asking too much of smartsheet as I think this is more of a relational database and dynmic report combination like crystal reports.

I would be grateful for any thoughts on this.

Thank you

Vasiles.

Andrée Starå · 05/01/20

Hi Vasiles,

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Vasiles Kiosses · 05/01/20

Thank you Andrée for the reply. In effect, its an on boarding process whereby a questionnaire is sent to a candidate company that will fill in and respond to various questions which require further elaboration. Whilst smart sheet handles 2 dimensional excel information, 3 dimensions (an extra layer of information) is more challenging.

There are more sections with similar entry details. However at this point I wish to ask the community for ideas on how I could use smartsheet to acheive my goal.

Here is a quick snapshot of one of the sections within an excel file. The complexity is handling the amount of column data which are the first column attributes whilst the rows are for the specific company response.