Question description:
This user has given permission to use the problem statement for this
blog.
Hello Team,
I have a sheet with several tabs, Tab1 extracts data from 3 other tabs using vlookup, where the data in these 3 tabs is changed each month.
how can I make vlookup functions in Tab1 work automatically once I put the new monthly data in the other 3 tabs.
For now, I have to write vlookup formulas in tab1 every month as it doesn't work otherwise.
Another issue please, Mostly after applying Vloopkup I get some cells with #NA, when I write the same formula again in these cells it gives me the output as expected in some cells but also some few cells give me #NA & i have to write the formula again& again till all are completed.
Solved by S. D. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
26/09/2018 - 09:06
Welcome to excelchat!
Excelchat Expert
26/09/2018 - 09:07
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
User
26/09/2018 - 09:07
sure
Excelchat Expert
26/09/2018 - 09:07
So, is it possible for you to show me the file?
User
26/09/2018 - 09:07
how much time would it take?
Excelchat Expert
26/09/2018 - 09:07
I'm not sure, I have to analyze the file.
User
26/09/2018 - 09:08
well.. first I ddin't know I would get a feedback now :)
User
26/09/2018 - 09:08
if u need the file I have to modify some things as i can't share it as it is
Excelchat Expert
26/09/2018 - 09:08
I understand.
Excelchat Expert
26/09/2018 - 09:09
Please make necessary changes and share the file.
User
26/09/2018 - 09:10
great , so can we postpne this lets say for 2 hours from now? or how is your policy for similar situations?
Excelchat Expert
26/09/2018 - 09:12
I'm afraid our policy is maximum 1 hour per session. If you think you can't continue this time, the chat will end automatically in 1 hour unless you choose to end it before. If you come back after 2 hours, you have to open a new chat
Excelchat Expert
26/09/2018 - 09:12
I wanted to see the file because I needed to see the formula's.
Excelchat Expert
26/09/2018 - 09:12
Which cells these formula's are referencing to.
Excelchat Expert
26/09/2018 - 09:13
How does the change of data affecting those formulas etc.
User
26/09/2018 - 09:13
yeah , sure ..
User
26/09/2018 - 09:14
well.. I have no option now :(
Excelchat Expert
26/09/2018 - 09:14
Okay, so shall we continue or you would like to end this now?
Excelchat Expert
26/09/2018 - 09:14
Oh, I'm sorry to hear that.
Excelchat Expert
26/09/2018 - 09:14
Umm, we can try without the file though.
User
26/09/2018 - 09:15
well.. ok, then that would be good ..
Excelchat Expert
26/09/2018 - 09:15
In that case it might take a lots of Q&A between two of us to figure out the problem.
User
26/09/2018 - 09:15
at least we may be able to get something
Excelchat Expert
26/09/2018 - 09:15
Yeah, sure.
Excelchat Expert
26/09/2018 - 09:15
For started, please share the formula you are using.
User
26/09/2018 - 09:15
i know .. sorry for that ..I thought I would have sometime before you get back to me :)
User
26/09/2018 - 09:16
ok, it's more than one of course but all are almost like this
User
26/09/2018 - 09:16
=VLOOKUP([@Name],'DB'!A64:BF202,58,0)
Excelchat Expert
26/09/2018 - 09:16
Well, I'm here and let's get this thing sorted.
User
26/09/2018 - 09:16
great thank you :)
Excelchat Expert
26/09/2018 - 09:16
What does the named range Name contain?
User
26/09/2018 - 09:17
or
User
26/09/2018 - 09:17
=VLOOKUP(A2,JulCap!A2:D135,4,0)
User
26/09/2018 - 09:18
the name range has normal text .. only 1 word & I make sure that no spaces are there
Excelchat Expert
26/09/2018 - 09:18
The look up ranges are always the same even after change of data?
User
26/09/2018 - 09:18
mm.. not sure I understand the question..
Excelchat Expert
26/09/2018 - 09:19
JulCap!A2:D135 this part of the formula is lookup range.
User
26/09/2018 - 09:19
yeah
Excelchat Expert
26/09/2018 - 09:19
This range changes when you paste new data for new month?
User
26/09/2018 - 09:20
yes.. but all the ordering of columns are the same only some values in some cells are changed each month ..
User
26/09/2018 - 09:21
but I copy the data from another sheet each month & then paste it in the JulCap tab
Excelchat Expert
26/09/2018 - 09:22
Do you delete the old columns before pasting new data?
User
26/09/2018 - 09:22
sometimes I do so & sometimes I just paste it above.. & it's the same results still
User
26/09/2018 - 09:22
I get the same issues I mean
User
26/09/2018 - 09:23
one note ..
User
26/09/2018 - 09:25
when I paste the data , I get a pop up window to browse to open a file , I cancel it & continue normally.
User
26/09/2018 - 09:25
not sure hy I get this window.. it pops up one I hit ctlr+V
Excelchat Expert
26/09/2018 - 09:26
When you paste new data and look at the formula, do you see any change in the formula?
User
26/09/2018 - 09:26
no
User
26/09/2018 - 09:27
it's the same, but only get me the #NA in some cells.. but some are updated normally
Excelchat Expert
26/09/2018 - 09:27
Okay, understood.
Excelchat Expert
26/09/2018 - 09:28
Unfortunately with these information it looks like everything is alright and you shouldn't get the N/A error.
Excelchat Expert
26/09/2018 - 09:29
And I am not able to figure our why you are getting those errors just from those information.
Excelchat Expert
26/09/2018 - 09:29
I'm sorry for the inconvenience, but I need to look at the source file itself.
User
26/09/2018 - 09:29
i c
User
26/09/2018 - 09:29
no prb ..
Excelchat Expert
26/09/2018 - 09:30
Do you have any other questions about this problem?
User
26/09/2018 - 09:30
not for this one obvously, as we're not able to move forward
User
26/09/2018 - 09:31
however is it possible that u may have an idea regarding the 2nd question?
User
26/09/2018 - 09:31
I thought this one would be an easy one :)
Excelchat Expert
26/09/2018 - 09:31
Yeah, I'm really sorry for the inconvenience. The 2nd question is?
User
26/09/2018 - 09:32
in 1 column, when I apply the vlookup I get some cells with #NA..Ifilter then & write down the formula again so I get the value normally for some yet some others give NA still ..
User
26/09/2018 - 09:32
& i keep filterin the NA & write the formula untill they are all retrieved
Excelchat Expert
26/09/2018 - 09:34
Not sure if it would help, but you can try this. On the Excel ribbon, go to the Formulas tab > Calculation group, click the Calculation Options button, and select Automatic
Excelchat Expert
26/09/2018 - 09:35
It seems to me those two are not for different problem, those are happening for the same reason.
User
26/09/2018 - 09:35
it already selected
User
26/09/2018 - 09:35
mm.. may be
User
26/09/2018 - 09:36
ok, then
Excelchat Expert
26/09/2018 - 09:36
If you could send me a few sample data, I could take a quick look.
User
26/09/2018 - 09:36
i'll try to do it as fast as I can & will see if I'm able to catch the time
Excelchat Expert
26/09/2018 - 09:37
We have 30 minutes remaining..
Excelchat Expert
26/09/2018 - 09:43
Please extend time again.
Excelchat Expert
26/09/2018 - 09:46
If you comeback and can't write in this box, don't be worred.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
