But if I create a custom sheet name such as "output!A1" or even "Sheet4!A1" it does not work unless I first open the excel file and create a sheet with that name, afterwards I can then run the function in mathcad and it will write output to that custom named sheet. This is not an ideal solution however! Is this a know limitiation of the WRITEEXCEL() function and is there some work-around so that I dont have to go and manually create sheets in the excel file?
I don't know for sure, but I expect it's a limitation of the WRITEEXCEL function. It attempts to write to whatever location you specify, but if that location does not exist it does not attempt to create it, it just fails.