vlookup not selecting table array from another sheet

[Karnam Naveen Kumar]

Never experienced this before. I'm entering a vlookup formula or any formula which requires table array to be selected from one sheet to another sheet (i've tried to enter the formula directly into the cell and using the wizard):

"=vlookup(a2, "

After I put the comma in after A2, normally, I manually select the range I want as the Table Array. However, when I click on the other workbook that contains the array I want, it does not "select" the range and put it into my formula. If I select a range within my worksheet, it works fine (i.e., it selects the range and adds it to the formula I'm building).

I've never seen this before. Could it be some setting? Something with the file (it's not shared, or read-only, or anything else I could think of).

Its really frustrating, please help me to resolve this.

Thank you,

Karnam Naveen Kumar

 

[Toadstool]

Hi Karnam,

If the sheet is Protected and the cells are Locked then it won't let you select them.

 

[Karnam Naveen Kumar]

Hi Karnam,

If the sheet is Protected and the cells are Locked then it won't let you select them.

View attachment 29445

Hi Toadstool,

Thanks for the reply. Neither the sheet is protected nor cells are locked but still it is not working. Please guide me if you know any other solution for this.

Regards,

Karnam Naveen Kumar

 

[jasonb75]

Not sure if it would make a difference, but is either workbook being accessed from onedrive or similar?

What exactly is or isn't happening when you try to select the range? Do you get an error message? Are you able to select them but the formula is not picking them up? Something else?

 

[Karnam Naveen Kumar]

Not sure if it would make a difference, but is either workbook being accessed from onedrive or similar?

What exactly is or isn't happening when you try to select the range? Do you get an error message? Are you able to select them but the formula is not picking them up? Something else?

Mr. Jasonb75 I am not getting any error message. Normally when we enter "=" in a cell followed by formula we get a syntax of the formula, in this case it is showing the syntax and doted box which indicates which cell we are selecting then we put comma and go for next step of selecting the range which is present in another sheet. Here it is not selecting the range and not even displaying dotted box. Formula is not working. One more peculiar thing is it is displaying too many options when I try to paste special. I am attaching the screen shots for your perusal. I hope I could able to explain you the problem...

Regards,
Karnam Naveen Kumar

 

[jasonb75]

That would mean that the workbooks are open in 2 separate instances of the excel application and that the 2 workbooks are not able to communicate with each other.

Close all workbooks except one, then use the excel File menu to open the rest. If you open them from the windows browser then you will encounter this problem.
If you have workbooks being opened by vba (macros) then the method of opening will be set in the code, as such any workbooks opened this way may still encounter the same errors.

It doesn't happen with newer versions of excel but looking at your screen capture, it appears that you have an old version. You have selected every version available on your profile so that means that we have inaccurate information to work with when trying to establish the cause of the problem.

 

[Karnam Naveen Kumar]

That would mean that the workbooks are open in 2 separate instances of the excel application and that the 2 workbooks are not able to communicate with each other.

Close all workbooks except one, then use the excel File menu to open the rest. If you open them from the windows browser then you will encounter this problem.
If you have workbooks being opened by vba (macros) then the method of opening will be set in the code, as such any workbooks opened this way may still encounter the same errors.

It doesn't happen with newer versions of excel but looking at your screen capture, it appears that you have an old version. You have selected every version available on your profile so that means that we have inaccurate information to work with when trying to establish the cause of the problem.

Nope.... Still not working.....

 

[jasonb75]

How did you open the workbooks?
Which version of excel are you using? (Please edit your profile to reflect this, having every version selected is serves no purpose).

 

[jasonb75]

There may be a setting in your version of excel that controls it. Your screen captures look like excel 2007 which I have not used for a long time. I believe that this was the last version where workbooks could be opened easily (or unintentionally) the way that you are currently doing so. Newer versions do everything possible to try and prevent it happening.

Click the office button (top left corner) then go to Options and look around for a setting that is something similar to 'Open each workbook in a new window'
Unchecking the box for that option should fix it. Any workbooks that are already open will need to be closed and reopened using the method that I described earlier.
Opening workbooks through the desktop browser will still cause the problems that you have been encountering.

 

[Karnam Naveen Kumar]

How did you open the workbooks?
Which version of excel are you using? (Please edit your profile to reflect this, having every version selected is serves no purpose).

Sorry for the inconvenience Mr. Jasonb75. I am using 2007 version. I opened the workbook from start menu and there are no multiple workbooks, macro, any name manager used or protection. I have checked all the possibilities.