Extracting Data From A Separate Spreadsheet

former_student

Hi All,

I have a column within a spreadsheet via which I need to verify that barcodes from 4 other spreadsheets have been input to the column on the 5th spreadsheet, is this possible to do and if so how?

Thanks in advance.

isofa

You can link calculations from other sheets and workbooks to the master column by referencing very easily, but I'm not 100% clear or what you are trying to achieve...

j.smith1981

You say verify, if you are looking up values could be a simple case for a VLOOKUP (using the formula's name I mean hence it all being in caps).

You just set the value you want to look up against, designate the columns on the sheet that has your data (even external sheets would work, though would be slower to lookup of course) then say which column you want in the cells value of where you put the VLOOKUP formula into and say usually you want false in the last part.

=VLOOKUP(valuetolookup,data you want to lookup against, column in tghe data you want to show, then the false or true part if you want an exact or not so exact lookup value).

You could then wrap that all around an if statement but my guess would be that it would be quite slow in finding the info, but you could even do some tutorials in VBA but this is not my area of expertise anymore, used to be but haven't scripted in VBA for ages!

Like I mean if #N/A then look into another spread sheet, then if that returns another #N/A value then lookup another all within the same formula but yea would be quite slow and annoying to run, but would logically work.

AlwaysSomething

I made a small worksheet to illustrate what I think you are describing. This is all on one sheet for now - I will get to that. Column B is a list of items. Column A has barcodes for those items, but in A3, the barcode is missing. Column D is a formula that checks for that. You can see the text for the formula in Column E - there are various ways to verify the barcode - this one just looks for the number.


So, is that roughly what you re doing? Anyway, to answer your real question, here is how you reference another spreadsheet
=if([OtherSheet1.xlsx]Sheet1!A2>0,"Okay","Missing")
So, wherever you used a simple reference to a cell, you just need slightly more complicated text:
Start with the name of the spreadsheet in square brackets
Then put the sheet name, followed by an exclamation mark
Then put the cell reference
Note: This only works if the other spreadsheet is already open in Excel. If it's not open, then the reference looks like this
=if('C:\Users\Student\Documents\[OtherSheet1.xlsx]Sheet1'!A2>0,"Okay","Missing")
Notice the single quote ' at the start of the file location, and just before the exclamation.
Is that what you wanted to know?