Forum Discussion
creating a table/new work book that filters data
I need to create a new table based off the info from one year and add the same columns for this year so it can be easily seen the same sections year to year
i need this data in a new sheet or table form that i can then sort it by location, #s, etc
or a way to keep this info from previous years and then add to it this current year so it can easily be viewed what was done on the previous year etc and see everything
14 Replies
- need to create a new table based off the info from one year and add the same columns for this year so it can be easily seen the same sections year to year
Do you mean data clean or ETL(extract transfer and loading)?
I guess this need more efforts than you think of.
In another words, no build-in tool to accomplish it. Amber -- how can I put this most gently? From that image it appears that your (or your organization's) main reason for using Excel in this instance is to take advantage of its rows and columns. Although I see three instances, in Row 21, where some addition appears to have taken place -- so you may have used the SUM function in those three instances -- you're not really using Excel for its abilities in summarizing and manipulating data. You could just as readily be using Microsoft Word with its tables. Excel's only advantage over Word is that it comes with as many cells as you need, all neatly arrayed from the get-go; yes, and it can do the occasional addition when that's needed.
There's nothing particularly wrong about that, but it's also (for that reason, I suspect) why more than 60 folks here have viewed your inquiry without responding: it's not really an Excel question, per se; at least not a question that looks to take advantage of the real strengths of Excel.
To the extent that you're using Excel for its rows and columns, for the clarity those present--which is fine, as noted--you can insert columns for different years, changing the headings to include the year of the data under it, hiding and/or revealing columns based on a particular question from management. Similarly, you can add rows as needed, and display or hide those.
Or you could copy the entire sheet, creating a new one, then delete all the content under the various headings, and fill that in for the new year. Then show them side-by-side.....
Now, if I'm missing something here, please come back and say so. If the spreadsheet behind the image you've shared is, in fact, taking advantage of other Excel-exclusive data manipulation features, please say so.
Finally, if doing so would not be a violation of proprietary data, consider posting a copy of the actual workbook on OneDrive or GoogleDrive, with a link pasted here that grants access to it.
- There's nothing particularly wrong about that, but it's also (for that reason, I suspect) why more than 60 folks here have viewed your inquiry without responding: it's not really an Excel question, per se; at least not a question that looks to take advantage of the real strengths of Excel.
we are try8ing to move each "section" of this sheet to its own page then have a sheet for each year with each "location" on a new page and then we want to be able to sort the data based on different variables when we need to and keep building new years onto it as we do this job a few times a year and need to be able to review previous notes/info from the last times we did it etc
please help lmk if theres a better way (we also need to be able to print this for our guys in the field to use as they dont have tech in the field and they will then handwrite new notes for the day and it will need to be added to the sheet for that year(hopefully microsoft lens would be able to read the handwritten notes on a blank sheet exactly the same as the workbook and insert into the corrrect columns if we scan it in? or do you know of a way to do this?)That's a tall order, not one I'm equipped to handle. You say that the guys in the field don't have tech, which is kind of surprising. Do they have iPads? If they do, they would be able to use an iPad to enter directly into the spreadsheet. I'm not at all confident that the scanning of a handwritten document would be able to get the data cleanly into the sheet in the appropriate spot, etc.
But from here (seeing only the tip of the iceberg), it seems to me that you should be talking with a local IT person about the whole process of gathering and summarizing the data.......
As I suggested in my last post, if you are able to post a copy of the actual spreadsheet, so long as it's not violating any proprietary guidelines, I and others could take a look at it in more detail. Absent that, though, there's not a lot more to suggest.
