How can I update a spreadsheet monthly (or whenever I choose) without overwriting existing data?

[LindorDoubleChoc]

Spreadsheet help please!

I expect this is beginner level, but I AM a beginner .

I've exported a report from our billing app at work onto a spreadsheet. 200 lines, all clients (one per row) with information about their billing and payments in the columns alongside.

I want to refresh this monthly. There will be some new clients to add and some changes to the figures in the billing and payments columns for existing clients.

How can I add this new information automatically without changing what's already on the sheet? Is it like merging or something?

Its Google Sheets if it makes any difference, but if I even knew what this is called in Excel that would help. In other words - I don't know what I'm asking for but I'm sure it must be possible!

In Google Sheets, nothing is overwritten. If you look at the document history you can take a look at what it used to be like before any update.

Or you can use the multiple worksheets functionality - the latest output is in a worksheet called "latest" and each month you rename "latest" to give it the date it was run, add a new worksheet and call it "latest" and drop the new data into that?

Is there a reason you aren’t just adding dated rows?

Turn off autosave (its in the top left) and use save as to create a new document.

Or

If you want the information in one place, rename the tab (right click on the tab at the bottom and select rename=use month year).
Go to the new tab next to it, rename with second month year, copy data from previous month, update with new data.

This gives you a monthly record. You can drag the tabs to swap the order.

Hope that makes sense.

Because I want to export the information from the billing app, so that will change every month. I want to use the information already there rather than remember to seek it out and perpetually update the spreadsheet. I just want to do it monthly.

I could export a new report monthly, but how can I easily see which lines have changed?

What is the information that you don't want to have to update? Is all of the data not in the download?

If you are adding some extra information (e.g. contact details) then you could have multiple tabs and look up the information from the old into the new.

If you aren't a big spreadsheet user, then AI is pretty good at creating formulas to do what you need. But with the caveat that you need to be careful about putting your data into it.

If you're literally just adding a new import each month add the new data into a new tab per month and you'll build up a history

Or do you need to compare the months in some way?

I’m not exactly sure I understand what you mean, but from what I think you are saying, you could do this in Microsoft using Power Query in a simple setup where you have a master sheet and then a folder in which you keep your exported reports. You then pull these into your master sheet, and it would add new rows and update changed information. Each row would have to have a unique identifier such as an email address or even better a customer number.

Im sure there is a way to use conditional formatting or something to highlight changes, but you could also do this by using a date in on your exported filename (eg 29260927 export) and having the filename as a populated column in the master excel sheet, so that you could see which report had populated the row and what date it was.

I use AI to help me with all my Excel stuff and have learnt tons. I have a lot of daily reports that I simply save into folders, and they feed my master documents, which I just refresh. It avoids human error with cutting and pasting and - as I suspect you want to do - retains the integrity of the original reports. Filtering and searching is easy. You can also have a sheet you make notes on which can feed columns into your master doc as it’s updated - for example actions taken or needed.

I don’t use Google sheets much but ask AI to help you. It is much simpler in Microsoft than I can make it sound here!

What does the billing app export? Is it an csv, a tab delimited, an xlsx? Are you doing that step manually or automatically?

what do you do with the information once it is in excel? Are you running a series of macros, generating pivot tables, have a bunch of formulas set up?

do you combine the old and new information or just want to look at one export of billing app information at a time?

Right-click the cell or cell range,
select Format Cells… ,
select Custom
select dd-mmm-yy (or date format you require)

Imagine I have 5 rows. Each row is client name in column A, amount they owe in column B (this is exported from the booking and billing app) and column C is my notes on action to be taken to clear the debt.

I don't want to lose the notes in column C.

Every month, some clients with a debt will be new to the report. Some clients will have paid and no longer be in debt so won't be on the report any more. Columns A and B will change. I want the information in Column C - which is ongoing/work in progress - to align with the correct client.

If it were just 5 rows it would be fine to do manually. But it's currently 200 with perhaps 30 resolved per month and 5 new ones added.

Export into a different tab. Link columns A and B to that tab, but not C. Or does the order of clients change? In which case you'd need to use a lookup I think.

You can use XLOOKUP formula for this.

Let's say you have one tab thst's the export for August and that has Column C with your notes in.
Export September's data and put it on a separate tab in the same file.
In Column C on Srptember's tab, use this formula: where i've used speech marks, that's the cell or column you need to put in the formula.
=XLOOKUP("cell with client name","column of alll client names on AUGUST tab","column C on AUGUST tab")

So what you are doing is saying "look up the client name from Sept tab, find it on the Aug tab then return the comments made about them.

So you always want a running sheet of actions, relating to clients that come and go.

Id probably set up a master client actions sheet, and pull the actions to each new data set using a lookup, and only type your actions into the master actions sheet. Any clients that don't pull through need to be added to the master actions sheet so you can ID each new client. So you'd record each data set on a new separate sheet to archive each month, whilst the master actions sheet is your dashboard.

I was going to suggest this too, I do a similar carry over of past notes relating to specific people with a weekly report.

You can ask Excel help, or AI to explain the process and formula OP. There are also some brilliant YouTube videos too.

shedmistress has a good idea, to keep a tab as a master.
So you have a tab called "master" with all clients in Column A - give A1 a title of "clients". In B1 type "Aug" then in C1 type "Sep", etc etc.
Use the XLOOKUP in B2:
=XLOOKUP("A2","client column on Aug tab","comments column on Aug tab")

Then in C2 use the XLOOKUP again to bring in Sept comments (looking up the columns in the Sept tab this time).

That way, you'll have all your comments over time in one place, making your actions and progress easy to see.

Thanks everyone for your detailed replies! I will have a go and report back when I'm next at work (Friday). I shall be so excited if I can actually do this. Sad really.