table to a list

TCJ

Hello,

I have a budget sheet that records different expense down the side along with additional data (for example, Expense A, vendor, Department, activity -each in a different column). Across the top of the table are months (Jan, Feb etc). The expense for an activity is recorded in the month it is forecasts to occur. The same expense could occur multiple times on the same line. See Picture

I would like to transform this into a list form with a separate row for each expense that includes all of the associated data and the month& year in which it will appear. If there will be multiple expenses for the same activity, but in different months, I would like each one to appear as a different row in the lists and include the date it is forecasts.

I plan to do this across a large number of projects, so I can eventually role up the expenses into a report. I am not sure where to start.

Thanks for the help!

T

Matt Johnson

Hi @TCJ

I like your plan. Here's what I would do:

Save as new on your current sheet so you can play around with it more freely. Then add a Date Column and an amount column. Next create a form so that people can add new rows without going to the sheet. Make as many things multiple choice as possible. Avoid free form text. You'll probably delete the current dates columns after you migrate the info into the new date and amount column.

Next create a report with the source being this sheet and use the group and summarize feature to look at the data my date and/or expense type. The report will automatically add new entries into the appropriate groups or add a new group if its a new date or a new type. The report will be your main source of information with the sheet in the background probably.

There's more ways to do it but let me know if you have any questions on this.

I hope that helps.

Matt

TCJ

Hello Matt,

Thank you for your response. I may have left out an important detail, I would like the data to be entered in the "calendar" view as in the picture. The you can see and easily move a cost from one month to the next, and it is a format that people are used to using. The list format, in a separate sheet would be used for reports, and metrics. My challenge is how to get the data into the list format from the calendar format.

Thanks,

Tim

TCJ

Additionally, I have access to the premium apps, Pivot and Datamesh

Cory Page

Good Day, you have a very interesting problem here, in this case I think we will for sure need some formula's but which ones would depend on how many months/dates you have for columns?

Is your Primary column structured and if yes how many unique values do you have? I am guessing "Work Description (one line per PO) is your primary? If you only have a few to a few dozen unique values that you would like in your primary column then life will be a bit easier for sure.

The same question for your Dates, if there are a few to a few dozen will make a huge difference with how much time this might take.

Are you planning on using workspaces for you new tables? When storing and managing large amounts of data as well as wanting to use Reports its best to use workspaces.

Finally the largest pain point with Smartsheets is the max row counts, it doesn't seem like you're maxing out or even coming close but its a good question to ask especially if you would like to re-organize your raw data.

If you can answer the above questions as best you can I will think about a solution and test it out on my end real quick. We will for sure need to use some index and match and prob some + but shouldn't be too big of a lift, unless of course the data set is a monster. :)

TCJ

Thank you for your response Cory. Part of me is glad to know that this is not an easy solution that I just did not see!

Answer to your questions. The primary column is not structured and is free text. I am not sure I could put a limit on it, and even if I did, it would be a very long list.

The number of dates will be a monthly date, reaching out in the future for at least a couple of years for each project.

I hope that helps. It could be that this is just something Smartsheets can't do.