vlookup not selecting table array from another sheet

Karnam Naveen Kumar

New Member
Joined
Jan 9, 2021
Messages
10
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,307
Office Version
  1. 2016
Platform
  1. Windows
Hi Karnam,

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

1610219079323.png
 

Karnam Naveen Kumar

New Member
Joined
Jan 9, 2021
Messages
10
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,983
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 9, 2021
Messages
10
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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
paste special.png
Workbook1.png
Workbook2.png
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,983
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 9, 2021
Messages
10
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
11,983
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,983
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 9, 2021
Messages
10
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,346
Messages
5,601,087
Members
414,426
Latest member
fraru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top