Creating a formula in Name Manager

The following formula is meant to create a range of the last payroll listing using the payroll date as a filter. After each payday, the list of those who were paid is added to an ongoing list. The data is sorted in order to ensure that all common dates are together. The formula works fine when placed in a cell.

="Listing!A"&(COUNTA(Listing!Z:Z)-COUNTIF(Listing!Z:Z,INDIRECT("listing!Z"&COUNTA(Listing!Z:Z))))&":Z"&COUNTA(Listing!Z:Z)

However, when I copy it as is into the Name Manager, naming it 'myRng'; nothing when using the following formula

=IFERROR(VLOOKUP(A2,INDIRECT(myRng),6,FALSE),"Not Listed"). Here I am comparing the payroll listing to a listing of authorized positions and their respective pay grades. A tool to be used to make sure raises remain within budget.

Hi,
I'd assume you've selected your range and named it in either the name box or name manager?

Now I may be missing the point entirely here but:

However, when I copy it as is into the Name Manager, naming it 'myRng'; nothing when using the following formula

If you're trying to create a dynamic range/filter condition within name manager, I don't believe (and I hope someone will correct me if wrong) that you can do anything other than select and name a range.

If I was doing something like this, I'd extract this list to an empty worksheet and either use vba to resize the range or create a table. The table size should automatically resize with the data.

If you create a table and enter a formula in the first row, as far as I can remember, it will copy it down the column automatically.

ScreenShot.JPG
Pierre, regrettably it did not work. After using your formula I followed your line of thinking in reverse and removed the INDIRECT function in the lookup formula. That did not work. The screen shot shows the setup that works. Really spinning my wheels on this one.

newdoverman. Yes, it work! But now I need to understand why. I substituted me data into your sheet and it worked. Will check my vlookup formulae before putting up the solved prefix.

For those who may read this post. The reason why this component of my workbook was not working is that I failed to assign absolute cell references where appropriate in the Name Manager.