[Solved] A2 in VLOOKUP unchanged by copy down

[Sanman]

Hello,
Hoping I can be relieved of my suffering.
And Im sure this has been answered many times before, but I cannot find the situation I require in the tutorials.
=VLOOKUP(A2; $Sheet1.A$2:B$60000; 2; 0)
I know this is wrong in format and syntax and probably everything, but Im so confused with googling, reading tutorials and watching you tube videos.
I have been attempting to make a parts list of my own Lego by entering in the part number and have the next cell auto filling with a description from a second sheet which has a large Lego parts list.
The A2 (cell to find the search data) in my example is not increasing in value to reflect the correct cell it is supposed to be searching from, when I drag the formula cells down.
Im not sure when and where to use the $ sign.
I have attached the file so that you can see what I mean.
Surely this is a simple formula and Ive only mucked up the wording/syntax.

Regards

Edit: Changed subject, was VLOOKUP  Make your post understandable by others  -- MrProgrammer, forum moderator

[robleyd]

Your example file is not attached; perhaps it is larger than the 128 Kb allowed? See How to upload a file for more information.

[MrProgrammer]

=VLOOKUP(A2; $Sheet1.A$2:B$60000; 2; 0)
I know this is wrong in format and syntax …

The syntax seems correct to me. A2 should change to A3, A4, … as you fill that formula down the column. I would need to see your Calc document to investigate why this does not happen. Do not attach a picture. The picture will be worhless. I will ignore it. The attachment must be a Calc document.

I'm not sure when and where to use the $ sign.

Read section 8. Using formulas and cell references in Ten concepts that every Calc user should know.

I have attached the file so that you can see what I mean.

You can see from looking at your post that you haven't attached anything. Try again to attach your document demonstrating the difficulty. The link explains what to do if your file is larger than 128K. Or you can make a copy of it, open the copy, delete all but a dozen or so rows, delete the rest of the sheets, and attach the copy. We won't need thousands of rows and dozens of sheets to determine why your formula doesn't work as you want.

[Sanman]

Thankyou for your response.
Yep , the site malfunctioned when I attached the file and clicked send. The page timed out and didnt send. so i quickly resent and didnt realiise the attachment was now missing. ugh

I did read the 10 concepts but i just got more confused. Because I thought i had all my syntax correct.
As you noted the A2 should have updated to A3 etc but it failed to do so, staying at A2 for all.

Some examples have the $ sign in front of the sheet name, some don't. some examples have $ sign in front of the number but not the column letter.
I have attached the file again, fingers crossed it doesn't time out this time.

instead of looking for the parts in a file I have tried to put them on a second sheet.
Thankyou

Vlookup forum.ods

(11.5 KiB) Downloaded 6 times

[FJCC]

The syntax of your formula is correct. The problem is that you have spaces to the left of the equal sign. Delete those spaces in the formula in cell B2 and then you can copy the formula down the column.

[Sanman]

Thankyou Very much for you help.
Something soo simple, Doh!
Much appreciated.
regsards

[MrProgrammer]

Because I thought i had all my syntax correct.

Menu View → Value Highlighting will show B2:B10 as black indicating that they contain text, not a formula. A formula must use = as its first character. B11:B13 and B15 show in green because they are formulas, but have a #NAME? error because sheet name $sheet1 must be followed by a period, not by a semicolon. Calc does not understand $sheet1 as the second parameter of VLOOKUP. B14 is green but with a #REF! error because the sheet name preceeding .A61:A67 is incorrect. Turn off View → Value Highlighting when you are not using it to detect problems, because it will disable formatting in your cells.

Some examples have the $ sign in front of the sheet name, some don't. some examples have $ sign in front of the number but not the column letter.

It does not matter if you use $ in formulas until you want to copy them to other cells. Then you must use $ whenever you don't want the parameter to increase in the direction of the copy. In the first parameter, A2, you must not use $ before the 2 because you want that to increase as you copy down. It does not matter if you put $ in front of A because you are copying down (changing the row), not left or right (changing the column). Similarly it does not matter if you put $ in from of sheet1 because you are copying down, not to different sheets.

Normally, in VLOOKUP formulas are copied down, thus is it common to not use any $ in the first parameter. But the search table should be the same for each copy of the formula, thus it is common to put $ in front of everything, though usually only the row specifications need to be protected against change since column and sheet references are not changed when copying down.

$ creates absolute references for a sheet, column, or row. Its absence means the reference is relative. It will be very difficult for you to use Calc until you understand how to use absolute and relative references.

Yep , the site malfunctioned when I attached the file and clicked send.

The site was under attack from Artificial Intelligence servers in Singapore. The administrator has blocked them, for now. This problem began last year. This has been a continuing problem for the forum since AI servers started sending thousands of requests to this forum, and many other sites like Wikipedia, since the compaines working on AI seem to think they are free to do so even though they surely must realize that this will cause a lot of problems for websites.

[Sanman]

Mr Programmer,

Thankyou for your input, Your explanations have really helped me understand what is happening with those errors and allows for some fault finding. And clarification of where and when to use the $.
Im glad you mentioned the period, because I was curious as to whether that was correct and why it mattered.
thankyou.
I did read a bit about absolute and relative, I understand an overview of it but if you ask for specifics, the birds would be twittering.
There was something along the lines ( as an example) of it looking at two squares to the left of the cell, as relative, as apposed to a specific cell to the left of it to find the data.

Thankyou for your knowledge sharing it is VERY helpful.