Named range with Vlookup on Multiple Sheets

I have the following named range which uses a vlookup to return the correct information based on the value in Column A.
=IF(ISERROR(VLOOKUP(Workings!$A19,Nominals,1,0)),0,VLOOKUP(Workings!$A19,Nominals,2,0))

This is working correctly on one sheet but now i would like to be able to use the same named range on several sheets.

Is this possible without creating a named range for each sheet?

It depends what Nominals refers to. If this is a table of data that other sheets can make use of, then you do not need to define it for each sheet. It might be better, though, to put that general data in another sheet, like Ref_data, so it is clear that it is independent of the other sheets.

Hope this helps.

Pete

Thanks Pete,

Nominals refers to a table of data on a completely seperate sheet and contains the data that i would like to be able to pick up on several sheets by picking up the unique value entered into column A on these sheets.

I want to be able to use this range on say "Sheet1" instead of "Workings" without the need to setup a new named range for each sheet.

Yes, that's fine then. Try it out. One of the many advantages of using a named range.

Pete

I have tried to do remove the "Workings!" and just leave the cell reference from the formula but whatever sheet i am using when i click ok it puts this into the named range in place of "Workings!". This puts errors on the other sheets as they are now referencing the row in column A on "Sheet1" instead of say "Workings" or "Sheet2" etc.

Say for example on Workings i put 100 in cell A3 as my unique number it returns "Testing" in B3 (which has the range as its formula). If i then put 101 in cell A3 on Sheet1 it still returns "Testing" in B3 as it is still looking up the 100 entered in Workings.

Using the above example i want it to return on Workings in B3 "Testing" and on Sheet1 in B3 "Complete" based on the values found in my table of data.

Your formula on all sheets should be:

=IF(ISERROR(VLOOKUP($A19,Nominals,1,0)),0,VLOOKUP($A19,Nominals,2,0))

This is based on your first formula, so presumably this is in row 19 of your sheet. So, did you remember to delete both Workings! from the formula? If so, then I suggest you attach a workbook which shows the behaviour you describe, then we can take a closer look at it - the FAQ describes how to attach a file.

Hope this helps.

Pete

I have just re read the thread and realised that I failed to mention that the formula in column B is =Test and not the formula I attached. That formula is defining the name "Test".

I have attached a workbook as an example of the problem.

I am not able to remove the sheet reference from the definition of the named formula, Test - everytime I try to do so it gets put back in. So, I think you will have to define other named formulae Test1, Test2 etc. for each sheet and then you could use =Test1, for example, in the appropriate sheet.

Hope this helps.

Pete

Cheers Pete,

You appear to have come up with the same conclusion as me which ideally I was hoping to avoid. Guess i'll just have to put the extra work in to get the desired outcome.

Thanks for you help though.