formula works using 2 different files but not using 2 sheets in 1 file

[El Shorty]

Hi,

I have a problem with my excel formula. I first had 2 files and using vlookup I copied the data from one file to another based on the data in a cell. However I needed to change it so it all fitted in one file, so I needed 2 sheets. Problem is when I use the exact same formula I now get #N/A instead of the correct data.

Here are the formulas...
The original:

=IF(ROW()=1;'C:\....\[File2.xml]Campaign Advanced Find View'!$B$1;VLOOKUP($D3;'C:\...\[File2.xml]Campaign Advanced Find View'!A:B;2;FALSE))

The new one:

=IF(ROW()=1;'Campaign Advanced Find View'!B1;VLOOKUP($D2;'Campaign Advanced Find View'!A:B;2;FALSE))

If the row is 1, it works fine so the data from the first cell is entered correctly, however if it gets to the vlookup is gives #N/A...

Anybody has an idea why this is?

 

[Aladin Akyurek]

Hi,

I have a problem with my excel formula. I first had 2 files and using vlookup I copied the data from one file to another based on the data in a cell. However I needed to change it so it all fitted in one file, so I needed 2 sheets. Problem is when I use the exact same formula I now get #N/A instead of the correct data.

Here are the formulas...
The original:

=IF(ROW()=1;'C:\....\[File2.xml]Campaign Advanced Find View'!$B$1;VLOOKUP($D3;'C:\...\[File2.xml]Campaign Advanced Find View'!A:B;2;FALSE))

The new one:

=IF(ROW()=1;'Campaign Advanced Find View'!B1;VLOOKUP($D2;'Campaign Advanced Find View'!A:B;2;FALSE))

If the row is 1, it works fine so the data from the first cell is entered correctly, however if it gets to the vlookup is gives #N/A...

Anybody has an idea why this is?

In which cell do you enter this formula?

 

[patel45]

-------------- deleted

 

[El Shorty]

In which cell do you enter this formula?

I use it in the first column. So I start in A1 and copy it down. I know it only works at the beginning and the end of a sheet. I use the same formula in the last column with the same result.

I have done the exact same thing for other files and there it always worked fine. But here the formula gives #N/A I just don't get it lolz..

 

[Aladin Akyurek]

I use it in the first column. So I start in A1 and copy it down. I know it only works at the beginning and the end of a sheet. I use the same formula in the last column with the same result.

I have done the exact same thing for other files and there it always worked fine. But here the formula gives #N/A I just don't get it lolz..

The logic for testing the row number escapes me. Given that that must be equal to 1...

=IF(ROWS($A$1:A1)=1;'Campaign Advanced Find View'!B1;VLOOKUP($D2;'Campaign Advanced Find View'!A:B;2;FALSE))

Why not use A2 as the start cell:

=IF(ROWS($A$2:A2)=1;'Campaign Advanced Find View'!B1;VLOOKUP($D2;'Campaign Advanced Find View'!A:B;2;FALSE))

for better alignment?

 

[El Shorty]

That is because I get the data from crm, if I start at row 2 it won't work. The whole column must contain the same formula and since the data is different on the first row I need to fill it in like that

 

[Aladin Akyurek]

That is because I get the data from crm, if I start at row 2 it won't work. The whole column must contain the same formula and since the data is different on the first row I need to fill it in like that

Interesting. I suppose the one that starts in A1 does not work either. If so, you need to elaborate by posting the data you have along with the desired results.

 

[El Shorty]

Interesting. I suppose the one that starts in A1 does not work either. If so, you need to elaborate by posting the data you have along with the desired results.

That is what is driving me crazy it works perfectly for A1 but it doesn't work for the rest of the cells... I have done the exact same thing in other excel files and they all work fine.. but this one keeps giving me this problem.

 

[El Shorty]

I will now take the award for biggest idiot... it's because of my previous post that I thought about checking the data in the excel.. Since I get it from crm, I didn't think about the fact that the data could have changed... so it is solved now. The data had changed giving me the #N/A.. it did show me a bug in my formula's so I fixed that aswell now..

Sorry for wasting your time and thanks for the help

 

[Aladin Akyurek]

I will now take the award for biggest idiot... it's because of my previous post that I thought about checking the data in the excel.. Since I get it from crm, I didn't think about the fact that the data could have changed... so it is solved now. The data had changed giving me the #N/A.. it did show me a bug in my formula's so I fixed that aswell now..

Sorry for wasting your time and thanks for the help

ROW()=1 is no good; better switch to ROWS($A$1:A1)=1.