Large Spreadsheet Help!

[emilybrush]

Hi All - I'm looking to develop a spreadsheet which can collate all of the contacts that we receive as a team. We have developed a template page for each company/ organisation that contacts us. I'm looking to create a summary page which provides a line with each company/ organisation number and some of the key details which I'll pull across from the organisations specific page. I'm looking to add a button on the summary page which will essentially copy and paste the template company/ organisation page into a new sheet, automatically create a new number in the summary page (with a link to the correct sheet) and populate the summary sheet.

I'm definitely no Excel whiz and would be very grateful for any help/ pointing in the right direction!

 

[awoohaw]

is the "template company/organization" page an excel document? Is it standardized form?
Providing some sample data would help. Please provide data, not images. if the data source is a document other than excel please post a sample. You may need to load the documents (sanitized for privacy) to a share drive like one drive, or dropbox.

Thanks in advance.

 

[emilybrush]

is the "template company/organization" page an excel document? Is it standardized form?
Providing some sample data would help. Please provide data, not images. if the data source is a document other than excel please post a sample. You may need to load the documents (sanitized for privacy) to a share drive like one drive, or dropbox.

Thanks in advance.

Hi, the template is a sheet in Excel. Please find a dropbox link here (hope that works and is helpful!): TEST.xlsx

 

[awoohaw]

Thank you for sharing that. If you put all templates in the same folder when you get them you can use power query to aggregate all your data. The set up takes time, but no more than working with formulas on two different spreadsheets. But, after that it can be repeated as long as there is consistent data. When you drop a new contact workbook into the designated folder (you can also further insure no import errros by importing by the template name .....abc_company_contact_template.xlsx. There are many you tube demonstrations/lessons on how to do this. YOu can find them on reputuable youtube channels like MyOnlineTrainingHub, Mr. Excel, ExcelIsFun, and Leila Gharani. Here is a video from a power query playlist by MyOnlineTrainingHub:

 

[emilybrush]

Thank you for sharing that. If you put all templates in the same folder when you get them you can use power query to aggregate all your data. The set up takes time, but no more than working with formulas on two different spreadsheets. But, after that it can be repeated as long as there is consistent data. When you drop a new contact workbook into the designated folder (you can also further insure no import errros by importing by the template name .....abc_company_contact_template.xlsx. There are many you tube demonstrations/lessons on how to do this. YOu can find them on reputuable youtube channels like MyOnlineTrainingHub, Mr. Excel, ExcelIsFun, and Leila Gharani. Here is a video from a power query playlist by MyOnlineTrainingHub:

Thank you, that's really helpful! Is there a way to do this with a button which copy and pastes the template sheet within the same spreadsheet as a new tab, automatically generates a sequential number, and updates the summary table?

 

[awoohaw]

You're welcome!

I don't know how to do that with PQ or formulas. It is probably very possible in VBA.

What is the purpose of cluttering up you summary workbook with the individual worksheets? You still have them for record keeping and reference.
I'm sure you can assign a sequential number somehow (you could put that in your input worksheet name however. And that can be used to create an index of sorts.

With power query all your data is aggregated in a big behind the scenes dataset/table. You can query that data however you need to see whatever company contact information you want with pivot data based on the aggregate data and a slicer/filter.