Hi,
Our Company runs reports every month that are exported from our Property Management Software. We like to edit the excel documents to help us get better information from the spreadsheet. We cannot edit the formatting from the Property Management Software. What would be the best way to have it so that I can copy and paste the report into a spreadsheet, and it automatically format it for me?
Are you aware of Paste Special... VALUES?
You could set up your desired Excel spreadsheet with the fonts and colors and all of the other formatting the way you want, and then just paste the VALUES from your Property Management Software Excel document into the previously formatted Excel document.
I have heard of it, but I would need the excel document to add columns and rows to the document that is being exported.
It sounds like what you may want is a macro, that takes your exported Property Management Software Excel file and "cleans it up" automtically for you. The macro will format it the way you want, adds columns and/or rows as desired, and generally makes it easier to view the data.
Can you post an example document of the original, raw Property Management Software file, and also an example of what you would like it to eventually look like? That will give us an idea of exactly what is desired and what might be required to achieve the goal.
Please see the attached. Also keep in mind something like this report would be adjusted each month. Ie the next month would have July in it etc.
Well, here's a quick and dirty solution which should help a bit... although it might not be exactly what you want. (But this only took a minute or two to put together.)
See the attached spreadsheet.
Each month, copy the new monthly column values from the exported worksheet into this edited worksheet, for the new month only. (Just copy the values, as to preserve the new workbook formatting.)
Update the "Period Ending" on row 4. (In July, for example, you will want to to say "Jul 2017")
The TOTAL column (column N) and the Average column (column O) do NOT have to be changed, as they are formulas.
However, you will have to update the month to month formula in column P. However, once you update that first row, row 11 (in July it will be =H11-G11), you can copy and paste that same formula down the entire column. (There's probably a tricky way to make that column a formula too, that never needs to be changed, but I don't know what it is.)
If desired, hide all of the columns that are blank until you need them.
It should take literally a minute or less to copy and paste the values each month, and update the month to month Column P formula.
Last edited by Ed_Collins; 07-26-2017 at 05:52 PM. Reason: typo
The above solution, of course, requires no macros.
Thank you!
That is a great idea, did not think about it like that at all!
Just curious, where is a good place to learn about a macro edit: That could do well.
No problem. Glad I could give you another idea to the problem.
Much of what I've learned about macros I've learned right here on this website, by reading all of the different problems others have had, and looking at the different solutions provided by the users.
A macro to edit column P automatically would be easy to write. All it would have to do is loop through columns and find the first empty column. (In this case that's column H, July.) It would then replace the current formula in column P with the last non empty column it found, minus the column before that.
If you really wanted to get fancy you can also have a macro import all of the data from the exported file into this new file at the click of a button. That way you wouldn't have to copy and paste it by hand.
It's just a matter of how much time you want to spend on it. Note that if it takes you or someone, say, 20 minutes to create, test, and debug a newly-created macro, and yet it only takes a single minute each month to do everything by hand, the ROI is going to take a long time before the writing of the macro will sees dividends... in this case 20 months.
Finally, other tabs within this spreadsheet could be created to display some or all of this monthly data in chart form.
Wow so much to learn. Thank you again for your time and insight!! I wish I could leave you a review or something.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks