Forum Discussion

scaffnull's avatar
scaffnull
Brass Contributor
Aug 18, 2021
Solved

Transfer and changing data with two different Excel sheets

Hi, What is the smartest way to transfer data from one spreadsheet to another but not take the whole information from one? I have one spreadsheet with information and need to transfer only some info...
excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Aug 29, 2021

    scaffnull Combined both sheets into one to make it easier to follow through the entire process. The attached file contains a "dirty" PQ solution that need optimizing. Bur just to be sure that the Invoice table is something you had in mind. Perhaps it enables you to continue with what you developed earlier. If not, come back here.

scaffnull's avatar
scaffnull
Brass Contributor
Aug 18, 2021
Thank you for your help, and yes it was not good stated. But if I have work numbers (excelsheet1) on one of my spreadsheet, and every week I get new information from a new spreadsheet (excelsheet2) with hours worked on that work number. Instead of manually type in every hour for that specific work number, can I use IF statements to transfer the data? Simply IF# on (excelsheet2) match with (excelsheet1) then add hours of work to that work number.

Thank you for your reply.
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Aug 18, 2021

scaffnull You could just create one file/table (cut and paste) with all weekly hours per job number and then summarize the lot with a pivot table. Or, if you want to avoid the manual copying and pasting, look into PowerQuery (PQ). Set up the correct routine(s) once and just add weekly data files to a particular folder on your system and refresh the query(ies). It is as easy as it sounds, BUT you need to learn some PQ basics first.

But, to be sure that these are indeed the smartest ways, please upload some sample data. Replace any confidential information with something that is made up.