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

 

[Karnam Naveen Kumar]

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...

 

[jasonb75]

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.

 

[Toadstool]

Karnam,

Does this help?

 

[Karnam Naveen Kumar]

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.

 

[Karnam Naveen Kumar]

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]

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]

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...

 

[jasonb75]

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.

office 2007; how to Open Excel Workbooks In Separate Instances as default setting

whenever I open a new excel document, it always opens in the same window. Using O2k3 I knew how to change the default setting from open in same window, to open in new window. But I cannot determine

answers.microsoft.com

 

[Karnam Naveen Kumar]

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.

office 2007; how to Open Excel Workbooks In Separate Instances as default setting

whenever I open a new excel document, it always opens in the same window. Using O2k3 I knew how to change the default setting from open in same window, to open in new window. But I cannot determine

answers.microsoft.com

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.