Hi,
I am trying to figure out a faster way to transfer data from specific cells in multiple worksheets in my workbook into a master sheet.
I have 200 sheets that is an old fashioned form my company uses (they don't like change) - on this form there are specific cells that contain info that must be transferred to a master log sheet. It will be the same cells on each sheet - but there are 17 cells to transfer.
The master sheet has headings describing the contents of each of these cells in rows. (1 row for each sheet in the workbook)
Now because it is so time consuming to put the formula in each cell of the master log sheet to automatically populate from the sheets - I need to find a faster way to fill the master log sheet with the formula that will extract the data from the sheets. Is this possible?
Master Sheet Example showing the current formulas.
The subsequent rows must have the formulas to extract from subsequent sheets.
If I drag these down it only repeats the same thing and I need it to change the sheet name (001, 002, 003, 004 etc without manually having to do this for 5000+/- cells.
Thank you in advance.
I am trying to figure out a faster way to transfer data from specific cells in multiple worksheets in my workbook into a master sheet.
I have 200 sheets that is an old fashioned form my company uses (they don't like change) - on this form there are specific cells that contain info that must be transferred to a master log sheet. It will be the same cells on each sheet - but there are 17 cells to transfer.
The master sheet has headings describing the contents of each of these cells in rows. (1 row for each sheet in the workbook)
Now because it is so time consuming to put the formula in each cell of the master log sheet to automatically populate from the sheets - I need to find a faster way to fill the master log sheet with the formula that will extract the data from the sheets. Is this possible?
Master Sheet Example showing the current formulas.
| NCR # | Dept | YY | N/A | Date Raised | Raised By | Job Number |
| NCR- | =+'001'!$O$2 | =+'001'!P2 | N/A | ='001'!$O$5 | =+'001'!$R$5 | =+'001'!$G$6 |
| NCR- | =+'002'!$O$2 | =+'002'!P2 | N/A | ='002'!$O$5 | =+'002'!$R$5 | =+'002'!$G$6 |
The subsequent rows must have the formulas to extract from subsequent sheets.
If I drag these down it only repeats the same thing and I need it to change the sheet name (001, 002, 003, 004 etc without manually having to do this for 5000+/- cells.
Thank you in advance.
