vlookup from multiple sheet

Question

Hi,

I have a excel attendance sheet. In the master sheet with unique employee ID in first column and date in the third row. On each field corresponding to date and unique Emp ID I have a validation with "P", "EL", "SL", "OFF" etc. When I am trying to vlookup the (present/EL/SL) status from multiple sheet from the same workbook, it showing the standard data validation error message

"the value you entered is not valid. A user has restricted values that can be entered into this cell"

therefore I'm not getting the desidred result from vlookup from multiple sheets.

I want the result based on a formula (preferably vlookup) from multiple sheet in a same workbook without removing the data validation in the master sheet

 With regurds

Manik Nag

Answer 1

Are you trying to put your VLOOKUP() formula into the cell(s) with the data validation?  That is not going to work, you will need to add another column to put the VLOOKUP() formula into that references the data validation cell

Since I can see column AW in your graphic, you could put a formula like this into AW6 to lookup a result based on the entry in L6

=IFERROR(VLOOKUP(L6,'Some Other Sheet'!$A$1:$C$10000,3,False),"")

Obviously the

,'Some Other Sheet'!$A$1:$C$10000,3,

portion just comes from my imagination, but

'Some Other Sheet'!$A$1:$C$10000

would be the reference to just one sheet's lookup table, and ,3, would be an assumption that the data you wanted to return is in the 3rd column of that table (column C).

Now, if you need to get the results from several sheets, only one of which may give you a good result, then you could set up the formula like this:

=IFERROR(VLOOKUP(L6,'Some Other Sheet'!$A$1:$C$10000,3,False),"") & IFERROR(VLOOKUP(L6,'Another Sheet'!$A$1:$C$10000,3,False),"") & IFERROR(VLOOKUP(L6,"A 3rd Sheet'!$A$1:$C$10000,3,False),"")

If I have not understood your problem correctly, then it would be helpful if you would tell us what sheet the formula is to go into and what cell on that sheet might hold the formula, and then copy the formula itself into a posting here.

Answer 2

Hi,

If you are writing a formula in cell L6 referring to cell L6 itself, then you will run into a circular reference problem.  Your formula will have to be written in column AW.  You may refer to the following link on my website - http://www.ashishmathur.com/extract-data-from-unknown-lookup-range/

Hope this helps.

Answer 3

Are you trying to put your VLOOKUP() formula into the cell(s) with the data validation?  That is not going to work, you will need to add another column to put the VLOOKUP() formula into that references the data validation cell

Since I can see column AW in your graphic, you could put a formula like this into AW6 to lookup a result based on the entry in L6

=IFERROR(VLOOKUP(L6,'Some Other Sheet'!$A$1:$C$10000,3,False),"")

Obviously the

,'Some Other Sheet'!$A$1:$C$10000,3,

portion just comes from my imagination, but

'Some Other Sheet'!$A$1:$C$10000

would be the reference to just one sheet's lookup table, and ,3, would be an assumption that the data you wanted to return is in the 3rd column of that table (column C).

Now, if you need to get the results from several sheets, only one of which may give you a good result, then you could set up the formula like this:

=IFERROR(VLOOKUP(L6,'Some Other Sheet'!$A$1:$C$10000,3,False),"") & IFERROR(VLOOKUP(L6,'Another Sheet'!$A$1:$C$10000,3,False),"") & IFERROR(VLOOKUP(L6,"A 3rd Sheet'!$A$1:$C$10000,3,False),"")

If I have not understood your problem correctly, then it would be helpful if you would tell us what sheet the formula is to go into and what cell on that sheet might hold the formula, and then copy the formula itself into a posting here.

Thank you, it's true that when i'm trying to look up in field where contains data validation it's not working but when I'm doing it in AW6 cell I've got my desired result

If you have any other option to fetch the data in a cell contain data validation please let me know, so that my job gets easier

Regards

Manik Nag

Answer 4

Hi,

If you are writing a formula in cell L6 referring to cell L6 itself, then you will run into a circular reference problem.  Your formula will have to be written in column AW.  You may refer to the following link on my website - http://www.ashishmathur.com/extract-data-from-unknown-lookup-range/

Hope this helps.

Sir,

I've checked your solution, but If you have any other option to fetch the data in a cell contain data validation please let me know, so that my job gets easier

Regards

Manik Nag

Answer 5

I'm sorry, there really is no other way that I can think of.  This is much like the law of physics that says 2 objects cannot occupy the same space at the same time.  In this case, you cannot have a choice that the user makes in a cell that also has a formula to get a result based on that choice.