Hi!

I’ve created a workbook with 2 worksheets. They’re named “MW-562 Pay” and “Employee Hours”.

When the user fills in the “MW-562 Pay”, certain bits of the information is pulled from the “MW-562 Pay” worksheet into the “Employee Hours” worksheet.

The formula I’ve used, in the cells on the “Employee Hours” worksheet, to do this is

=’MW-562 Pay’!A14

and I change the A14 from cell to cell as needed. It works great.

Where I’m running into trouble:

Some users will need to add a second “MW-562 Pay” worksheet (and a third, and a fourth, etc.) to accommodate more employees.

To do that, they’ll copy the “MW-562 Pay” worksheet into the workbook. They’ll be instructed to name those additional worksheets as “MW-562 Pay 2” and “MW-562 Pay 3” and “MW-562 Pay 4” and so on.

I need to pre-populate the “Employee Hours” worksheet ahead of time with formulas so that it’s ready to pull the info from the additional “MW-562 Pay” worksheets once they’re added, if they’re added (some users won’t need to add additional worksheets, the one will be enough).

The cells on the “Employee Hours” worksheet that will pull info from the added worksheets need to remain visibly empty until and unless the user adds more “MW-562 Pay” worksheets.

Because I know what the name of the additional worksheets will be, I thought that I could write the formulas ahead of time to pull the info from those yet-to-be added worksheets, for example:

=’MW-562 Pay 2′!A14

And then when someone copies the “MW-562 Pay” and names that added worksheet “MW-562 Pay 2”, the formulas I pre-populated into the “Employee Hours” worksheet would start pulling the needed info from the newly added “MW-562 Pay 2” worksheet.

Unfortunately, I ended up with #REF errors on the “Employee Hours” worksheet in all cells that have formulas pulling info from the yet to be added “MW-562 Pay 2” worksheet.

I understand why I received the #REF errors (because the formulas are referencing a worksheet that isn’t present yet) but I thought, no big, that’ll change once I add the “MW-562 Pay 2” worksheet.

Alas, that didn’t happen, the #REF errors remained.

How would I go about accomplishing my goal which is to have the “Employee Hours” worksheet pre-populated with the formulas that’ll pull the info from the additional worksheets once they’re added? Is it possible?

Thank you so much!! 🙂

​Hi! I’ve created a workbook with 2 worksheets. They’re named “MW-562 Pay” and “Employee Hours”. When the user fills in the “MW-562 Pay”, certain bits of the information is pulled from the “MW-562 Pay” worksheet into the “Employee Hours” worksheet. The formula I’ve used, in the cells on the “Employee Hours” worksheet, to do this is =’MW-562 Pay’!A14 and I change the A14 from cell to cell as needed. It works great. Where I’m running into trouble: Some users will need to add a second “MW-562 Pay” worksheet (and a third, and a fourth, etc.) to accommodate more employees. To do that, they’ll copy the “MW-562 Pay” worksheet into the workbook. They’ll be instructed to name those additional worksheets as “MW-562 Pay 2” and “MW-562 Pay 3” and “MW-562 Pay 4” and so on. I need to pre-populate the “Employee Hours” worksheet ahead of time with formulas so that it’s ready to pull the info from the additional “MW-562 Pay” worksheets once they’re added, if they’re added (some users won’t need to add additional worksheets, the one will be enough). The cells on the “Employee Hours” worksheet that will pull info from the added worksheets need to remain visibly empty until and unless the user adds more “MW-562 Pay” worksheets. Because I know what the name of the additional worksheets will be, I thought that I could write the formulas ahead of time to pull the info from those yet-to-be added worksheets, for example: =’MW-562 Pay 2′!A14 And then when someone copies the “MW-562 Pay” and names that added worksheet “MW-562 Pay 2”, the formulas I pre-populated into the “Employee Hours” worksheet would start pulling the needed info from the newly added “MW-562 Pay 2” worksheet. Unfortunately, I ended up with #REF errors on the “Employee Hours” worksheet in all cells that have formulas pulling info from the yet to be added “MW-562 Pay 2” worksheet. I understand why I received the #REF errors (because the formulas are referencing a worksheet that isn’t present yet) but I thought, no big, that’ll change once I add the “MW-562 Pay 2” worksheet. Alas, that didn’t happen, the #REF errors remained. How would I go about accomplishing my goal which is to have the “Employee Hours” worksheet pre-populated with the formulas that’ll pull the info from the additional worksheets once they’re added? Is it possible? Thank you so much!! 🙂  Read More

​