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
 

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
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.
But hitherto I have been opening the workbook from start menu and was working fine. Since 2 days I am facing this problem. How can all of a sudden the process of execution would change like that. I even tried to open from the open option of the file menu. Its not working...
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,908
Office Version
  1. 365
Platform
  1. Windows
There could be any number of reasons for it. Making a change to another setting and clicking the box for that one without realising it. Running code which makes a temporary change to the setting which was interrupted before it finished.

I don't recall the exact description of the option but it will be something similar to that, most likely either in the 'General' or 'Advanced' screens of your Excel options. If the box is ticked then un-ticking it should resolve the issue.

Until you actually do this and confirm that this is or is not the cause of the problem I will not be able to assist further. Please post a screen capture of the option that you check so that we can confirm it is the correct one.
 

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
There could be any number of reasons for it. Making a change to another setting and clicking the box for that one without realising it. Running code which makes a temporary change to the setting which was interrupted before it finished.

I don't recall the exact description of the option but it will be something similar to that, most likely either in the 'General' or 'Advanced' screens of your Excel options. If the box is ticked then un-ticking it should resolve the issue.

Until you actually do this and confirm that this is or is not the cause of the problem I will not be able to assist further. Please post a screen capture of the option that you check so that we can confirm it is the correct one.
Mr. Jasonb75,

Please let me know which option to be unchecked from the attachments.
Untitled.png
Untitled1.png
Untitled2.png
Untitled3.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

ADVERTISEMENT

Karnam,

Does this help?

Hi Toadstool,

I am using version 2007. In this I couldn't find the windows menu. I tried to add it from the customize option from the excel option. But there is no windows option at all. Between the sheets within the same workbook all formulas are working. I am facing the trouble when applied to different workbooks. Even in the video which you have attached is not the solution. It is a alternative for the function. I want to know why this peculiar problem has occurred? What mistake I have done? Will the software expires after certain period and starts behaving peculiar? Please let me know.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,908
Office Version
  1. 365
Platform
  1. Windows
I'm not seeing the correct option in any of those screen captures, try the 'Popular' tab, that is the equivalent to 'General' in your version.
 

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

I'm not seeing the correct option in any of those screen captures, try the 'Popular' tab, that is the equivalent to 'General' in your version.
Here it is...
Untitled.png
Untitled1.png
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,908
Office Version
  1. 365
Platform
  1. Windows
I thought it was an excel setting but it looks like it is a windows setting. See the link below but note that this is to do the reverse of what you want, meaning that you need to check the boxes where is says to uncheck and vice versa.

 

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
I thought it was an excel setting but it looks like it is a windows setting. See the link below but note that this is to do the reverse of what you want, meaning that you need to check the boxes where is says to uncheck and vice versa.

I give up. I even tried whatever suggested in this as well. I think this is due to expiration of the software or some malfunction of software and OS. The developer of the windows and ms office would know the cause and solution for this. Thank you Jasonb75 for your guidance.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,437
Messages
5,596,130
Members
414,043
Latest member
thomas Stein

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