vlookup range on 3rd sheets

[kwloh75]

vlookup range on multiple sheets? <HR style="COLOR: #ffffff; BACKGROUND-COLOR: #ffffff" SIZE=1><!-- / icon and title --><!-- message -->for two sheets with referance on sheet1 and data on sheet2 and three

=IF(ISNA(VLOOKUP(A2,Sheet2!A:B,2,FALSE)),VLOOKUP(A2,Sheet3!A:B,2,FALSE),VLOOKUP(A2,Sheet2!A:B,2,FALSE))
<!-- / message -->

The above formulae after tried, it only work for the reference of 2 sheets, but if the 3rd sheets, how to formulae it ?

 

[Biz]

Try

=IF(ISNA(VLOOKUP(A2,Sheet2!A:B,2,0)),
IF(ISNA(VLOOKUP(A2,Sheet3!A:B,2,0)),
VLOOKUP(A2,Sheet2!A:B,2,0)),
VLOOKUP(A2,Sheet2!A:B,2,0))

 

[Domenic]

Try...

=IF(ISNA(VLOOKUP(A2,Sheet2!A:B,2,FALSE)),IF(ISNA(VLOOKUP(A2,Sheet3!A:B,2,FALSE)),VLOOKUP(A2,Sheet4!A:B,2,FALSE),VLOOKUP(A2,Sheet3!A:B,2,FALSE)),VLOOKUP(A2,Sheet2!A:B,2,FALSE))

 

[kwloh75]

Many thanks for all the solution...it is work...

 

[kwloh75]

Another qestion is ,how do i protect the formulae to avoid editing

 

[sanrv1f]

Hi and welcome to the board,

You can protect a formual by protecting the Sheet,

Select the entire sheet,

Goto Edit->Format cells,

under 'Protection' tab, uncheck 'Locked'

now select the cells you want to protect,

Goto Edit->Format cells,

check 'Locked' and 'Hidden'

now goto Tools->Protection->Protect Sheet, to protect the sheet

 

[kwloh75]

Hi,

i'm using the excel ver.03, can i protect my sheet and allow user to input the selected cells only ?

Thanks

 

[sanrv1f]

Hi,

i'm using the excel ver.03

Thanks

Is it Excel 2003 or Excel 3.0??

I dont know about 3.0, but in XL 2003 you can protect the cells other than those for Input,


First select all the cells and set Locked & Hidden checked,

and select only those Input cells and uncheck the 'Locked' option

now protect the sheet, that's it

 

[kwloh75]

Hi..

Thanks..i am using excel 2003...i get your suggestion.i solve it..

Appreciated for you reply.