Register
Log In
Unanswered
Home
June 24, 2016
Offline
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!
Dashboards
June 25, 2016
Offline
Hi Emily
Please attach a sample copy of your file.
Sunny
July 16, 2010
Offline
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
June 24, 2016
Offline
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
June 24, 2016
Offline
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
Dashboards
June 25, 2016
Offline
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
June 24, 2016
Offline
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
Most Users Ever Online: 57
Currently Online:
16 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Akshatraj Adig
Liesie Marais
Moet Lewis
Michelle Anderson
Veronica Gallego
krishnaraj r
Dharan Prakash Mishra
Md. Rahman
Lilian Vo
Jane Kumwenda
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
