Menu
You missed a step. A UDF doesn't work unless there is VBA code behind it. Think of it this way:
When you use any built in Excel function, e.g. SUM, there is some internal programming behind it, buried deep within Excel. The nice folks that wrote Excel provide that programming for you.
Using the VBA code, I like to print (on the default printer) an existing file called 'XYZ.pdf' that resides in the same folder as the Excel file. What would be VBA code for it?Important: I am not trying to make a pdf file out of an excel sheet/range.
When you create and use a User Defined Function, you, the user, are responsible for providing that behind the scenes programming.
You are getting a #NAME error because Excel doesn't recognize the function PrevSheet as a valid function. You would get the same error if you entered any 'function name' that Excel doesn't know what to do with, e.g. =HappyBirthday(A1)
However, if Excel can find some programming behind the function name, it will (hopefully) return the value that the user wants the function to return.
That programming is done with Excel VBA - Visual Basic for Applications. VBA is a programming language that allows users to do things with Excel that go way beyond the built in functions. The set of instructions that are written in VBA are often called a Macro. The main difference between a Macro and a UDF is that a Macro is run either manually or automatically via a specific action taken by a user while a UDF can be run by including the name of the UDF in a formula.
Read up on Excel VBA and/or Excel Macros for more info.
OK, all that said, what you want is a Function that will always return the name of the previous sheet based on where the function is used. Since Excel doesn't have a built in function to do that, we can use a UDF. Here's how:
1 - Follow the first 3 steps at the link below to open the VBA editor and insert a Module.
2 - Once that Module is open, Paste the VBA code shown below into the window. That VBA code is the programming behind the UDF. The code first determines what sheet it is used in and then returns the name of the previous sheet, returning the value in the cell that you use in the formula, e.g. A1.
3 - Now go back into your sheet and try the formula I suggested, which has the PrevSheet UDF in it.
Just like any Nested formula, Excel will see the UDF called PrevSheet and run the programming that is behind it.
One more item:
Since the workbook will now contain some VBA code, you will need to save the workbook with the .xlsm file type.
How To Post Data or Code ---> Click Here Before Posting Data or VBA Code