I have several excel files (one exists for each customer) that contain 300-400 rows of data each and numerous columns. These main data files have internal and customer information on them so I have copy and paste the columns containing customer information to another sheet that we can send out.
Currently I'm simply copying and pasting links to my second sheet in order to do this which is becoming a hassle. Every time a row is added to sheet 1 in the middle of the table I have to go to sheet 2 and update the formulas. I'm always nervous I may be missing a row on the second spreadsheet.
Is there a simpler way to make this connection that would auto update when I change a value in a cell or I add or delete rows? If you could point me in the right direction it would be greatly appreciated.
Thank you!
Hi Emily
Please attach a sample copy of your file.
Sunny
Hi Emily,
As Sunny said, a sample of your data would help, but it sounds like you might be able to use PivotTables to automate this process, as described here:
https://www.myonlinetraininghub.com/excel-pivot-tables-to-extract-data
This is far more robust than any formula based approach.
Mynda
Sorry for the late response. I had a hard time trying to get the file size down to a point where I could send it. Unfortunately, I had to remove the sheet that included source data to make it work.
Thank you for your advice Mynda. My original thought was to use a pivot table but I was hitting a road block when trying to insert two calculated rows that show a running total. I got frustrated at the time and opted for a table instead. I'm not sure what I was doing wrong!
I threw a small pivot together to show you what I was running into. I inserted a field and select show values as running total with date as the base field. This normally would work but it isn't on this file as you will see in column K. The total resets on each row.
Emily
Please disregard the attachment I just sent and use this one. I was able to get the file size down 4 MB by removing the original table I've been preparing for customers.
Sorry for the mix up!
Emily
Hi Emily
Select any cell in column A of your PivotTable and then from the ribbon select PivotTable Tools - Options - Collapse Entire Field
This will display the running total.
Hope this helps
Sunny
Thank you Sunny but unfortunately this causes another issue. When I collapse the date field since the rows from my original table are being combined, I no longer have the detailed information the customer needs such as workscope, invoice #, PO# and engine SN.
See attached.
Thanks,
Emily
Hi emily
I don't think that is possible with running total.
Sunny
Ok thanks anyways Sunny!
It looks like I'll have to stick with copying and pasting links for the time being.
