Forum Discussion
How do I write this formula?
='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.
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!
