Active Member
June 24, 2016
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!
VIP
Trusted Members
June 25, 2016
July 16, 2010
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.myonlinetraininghu.....tract-data
This is far more robust than any formula based approach.
Mynda
Active Member
June 24, 2016
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
VIP
Trusted Members
June 25, 2016
Active Member
June 24, 2016
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
1 Guest(s)