How can I change the data source per tab instead of for the entire workbook?

Question

I am in the process of upgrading an excel spreadsheet we use to manage timecards with for employees every week, we use OCR to process them. Currently our big spreadsheet, its a multi-tabbed spreadsheet where every employee in a given department can see everyone else's time that gets logged.

What I am in the process of doing is making individual spreadsheets inside folders for each employee to access, these folders will be locked so employees of each department cant go in and see other peoples timecards. One master spreadsheet with the use of index/match will pull the data into the master spreadsheet.

I have one page completed for one employee but I have about 20+ more employees to go and it took 6 hours to do one page, is there a way that I can change the spreadsheet connection of one individual page instead of the entire workbook?

here is an illustration: i have hundreds of cells on one tab connected to this external spreadsheet

With the use of the "Edit Links" prompt I am aware that I can change data sources, but I want to edit the connection per tab not the entire workbook

i want to have one tab pulling from one data source, and the next tab pulling from another data source. I want to accomplish this without going into each cell and updating the link and spending a 40 hour work week on something so small.

How can I do this?

thanks!

Answer 1

using replace all, [ctrl + h] for windows, you can change any value within formulas on the entire page

works great