Bulk Import

[kessock]

???

Hi Lee,
Thanks for the reply.
I have just tried using the wizard and am unsure if I am supposed to complete all 4 boxes or not.
I tried doing it but it threw alsorts of messages at me which didn't get me what I was hoping for.
Here is the reference completed on my first attempt:
=VLOOKUP(A2,'[J Perkins Trade price list.xls]Price list for disc TRADE with '!$H$2,,'[J Perkins Trade price list.xls]Price list for disc TRADE with '!$H$2)

[leehack]

You need to fill out all 4 parts of the formula. Where it asks for the cell range, you currently have one single cell included, that is not a range, you need to define the range that contains the data, it's no good just clicking in one cell, it can't look up anything in just one cell. Imagine you had a matrix A-J columns, and 1-10 rows, your lookup reference would then be A1:J10. That would be a 10 x 10 grid. Click in each of the 4 parts in the wizard and read what it is asking you for.

Rather than completely confusing yourself, why not copy paste all of the contents from the J Perkins spreadsheet into a sheet in your own spreadsheet. Set things up easy and logical to start with in one place.

[kessock]

Hi Lee,
I would just use the Perkins spreadsheet but it would mean going into each and every product to create longer descriptions and also the correct path for placement in my site.
I have tried looking at what it is asking for in each of the 4 boxes but it is less than obvious to which sheet it refers in the first place.
I was hoping the wording at the bottom of the wizard would have been slightly clearer.
Cheers.
Mark

[dallen]

=VLOOKUP(A2,'[J Perkins Trade price list.xls]Price list for disc TRADE with '!$H$2,,'[J Perkins Trade price list.xls]Price list for disc TRADE with '!$H$2)

So - whatever is in A2 in the current worksheet, you want to find in another worksheet in another spreadsheet. Fine.
The other worksheet is called 'Price list for disc TRADE with ' and is in a spreadsheet called 'J Perkins Trade price list.xls'. Fine.
BUT - which column is the matching value of A2 in? If it's in column A of the other sheet, and you want to return the corresponding value in column H then you want to put !A:H at the end. That will tell it to search in the range from column A to column H. It will try to find the value of A2 in the current worksheet in column A of the other sheet.

After that you have two commas next to each other. You've missed a vital piece of data here! You need to tell it which column you want to return the value from - in the example it would be colmn H. You need to tell it which column number of the range specified this is. The range specified is A:H. A=1, B=2, C=3 ... G=7, H=8. So you'd want an 8 in there.
After that, you just named a single cell again. Take that out, and replace it with the word FALSE. That will make sure it only returns data if it finds an exact match.

So, the corrected formula (assuming that the data range you're trying to lookup in is columns A to H in this other work/spreadsheet) would be:

=VLOOKUP(A2,'[J Perkins Trade price list.xls]Price list for disc TRADE with '!A:H,8,FALSE)

[kessock]

closer

Thanks for the reply.
Getting closer thanks.
If I wanted the result to go in say column 4 or D should this formula
=VLOOKUP(A2,'[J Perkins Trade price list.xls]Price list for disc TRADE with '!A:H,8,FALSE) simply return the value of column 8 or the perkins spreadsheet as I am currently getting #N/A
Cheers.
Mark

[dallen]

If I wanted the result to go in say column 4 or D should this formula
=VLOOKUP(A2,'[J Perkins Trade price list.xls]Price list for disc TRADE with '!A:H,8,FALSE) simply return the value of column 8 or the perkins spreadsheet as I am currently getting #N/A

Not sure if we're on the same page...

Let's say the value of A2 is "eggs". So you want to look for "eggs" in the other sheet. Which column is it in in the other sheet? If it's in column A then cool. Let's say it's in A7 in the other sheet...

Now, what you want is the value of column D in the other sheet, on the same row as "eggs" was found. So you want to give it the range of A (that's A : D) to look in. So it will find "eggs" in column A7 and return whatever is in D7. So the above formula would be changed to:

=VLOOKUP(A2,'[J Perkins Trade price list.xls]Price list for disc TRADE with '!A:D,4,FALSE)

Note that all that has changed is A:H became A (A : D) and the 8 became a 4.
The value of D7 in the other sheet will appear in the cell where you put this formula.
If you're getting #N/A it's because it can't find "eggs" in the other sheet. This may be because of a data-type mismatch. If you're looking for a number and the cells are formatted as numbers in one sheet but text in the other, this can cause problems...

[kessock]

Thanks again for the reply.
I can get my head around what you have said and have checked the formula and also that the cells are of the same type etc even down to checking the cell it is getting the price from is set at number and also the destination cell set the same but this still doesn't work and just returns the #N/A
Thanks.
Mark

[dallen]

Hmmm.

Without actually looking at the sheets I can't help I'm afraid.

Try using some dummy data. Set up some simple spreadsheets with some basic data in and try it out.

Or PM me and send me the sheets (or part of them if they're big)...

We use these formulas for lots of different things. We've got a SQL databse which holds our 'true' stock data, actinic sites which hold versions of the data in MDBs, an Amazon feed and other sites which hold them in other formats, and we do daily updates across the board using data imports and lookups to get updated stock prices, rrps, stock quantities and a lot more! It should work like a charm once you figure out what the issue is...