How do I write this formula?

[DianeDennis]

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!! 🙂

[#2]

If you know that there will be at most a couple of these sheets, then all you need to do is include the as-of-yet-unavailable sheets inside the IFERROR function. For example:

='MW-562 Pay'!A14+IFERROR('MW-562 Pay 2'!A14,0)+IFERROR('MW-562 Pay 3'!A14,0)...

What happens with the IFERROR function, is if the first parameter results in an actual answer, the function produces that answer. If the first parameter results in an error, then the function returns the 2nd parameter (the zero in these cases). As soon as those sheets are created, then the IFERROR functions work as long as the naming conventions are perfect.

When you enter that function, Excel will try to open an existing workbook to find that sheet name. Just hit "cancel" (cancel for each of the IFERROR cases) when you are creating the formula.

You can also use 3D named ranges. Repost if the IFERROR doesn't work and I'll walk you through 3D named ranges.

[#3]

@BillY2305 

Hi! And thank you so much! 🙂

Unfortunately, there could be many additional "MW-562 Pay X" worksheets added by the users.

If I understand your formula correctly, I see that it encompasses, or can encompass, all of the to-be-added worksheets.

One thing I didn't explain well enough, and I apologize for that, is that each cell on the "Employee Hours" worksheet needs to pull from only one cell in only one "MW-562 Pay" worksheet.

I set up ten rows for the first "MW-562 Pay" worksheet to pull the information from that worksheet. Then, I have to do another ten rows to pull from the to-be-added "MW-562 Pay 2" worksheet, then another ten rows to pull from the to-be-added "MW-562 Pay 3" worksheet, and so on.

Late yesterday I came up with the following formula:

=IFERROR(INDIRECT("'MW-562 Pay 2'!A14"), "")

My plan, provided the formula is good (it does work but I don't yet know if there are issues that will pop up with it), is to repeat that formula into the cells in the next ten rows on the "Employee Hours" worksheet for all info that needs to be pulled from the to-be-added "MW-562 Pay 2" and changing the A14 as needed, and then, in the next ten rows, repeat the "MW-562 Pay 2" formula but change it to be "MW-562 Pay 3" for info that needs to be pulled from the to-be-added "MW-562 Pay 3" worksheet (and changing the A14 as needed), and so on.

Does my formula, and my plan, make sense or do you see anywhere that I might falter with it?

If it does make sense, would you know if there's a way for me to "fill down" and have the cell reference (such as A14) increase by two during the fill down?

For example, on the "Employee Hours" worksheet, cell A12 has the following formula:

=IFERROR(INDIRECT("'MW-562 Pay 2'!A14"), "")

And cell A13 needs to have the following formula:

=IFERROR(INDIRECT("'MW-562 Pay 2'!A16"), "")

And cell A14 needs to have the following formula:

=IFERROR(INDIRECT("'MW-562 Pay 2'!A18"), "")

When I tried it, the cell reference in the formula increased by only one, as follows:

=IFERROR(INDIRECT("'MW-562 Pay 2'!A14"), "")

to

=IFERROR(INDIRECT("'MW-562 Pay 2'!A15"), "")

to

=IFERROR(INDIRECT("'MW-562 Pay 2'!A16"), "")

And then also, would the 3D named ranges that you mentioned be a better (quicker) way to set this up?

Again, thank you SO MUCH for your time and help!