IF test doesnt work to match data. Any ideas?

OK All you excel guru's, here one for you.

Im building a couple workbooks and need to see what you think is going to be the best FORMULA to use for this. I stress formula because thats where the problem lies. Because of the application im building this for, i cannot use macros or conditional formatting. So here is the 1st problem.

we'll call the books, book1 and book2.

I need a range of cells in book2 to look at a range of cells in book1, if any of book2's range of cells finds a match in any of the range of cells in book1, i need it to copy AT LEAST the matching data into a Range of specified cells on book2. I tried this just as it sounds using an IF test but the result was #VALUE!. so unless i didnt code it right, an IF test isnt the way to go. To "dumb it down" to my level, i guess what im trying to do is to search a range of cells for matching data, if it finds a match to that data,i needs it to copy the whole row over to a different workbook. It could need to copy one line, it could be 100 lines. it depends on the wether or not the data is matched.

OK, i'll let you guys roll that one around in your heads for a while and we'll see what falls out.

Thanks in advance for the help.

Not very specific on the details, possibly VLOOKUP which can work across workbooks.

Try attaching example workbooks

Im not exactly sure how i can get more specific than i was above but i'll try. as far as giving an example book, due to the nature of the data, i cant do that. so let me try to explain a little better.

i have 2 workbooks that im working with. In each of the workbooks there is a common column called SKU. This "SKU" number is a number that is entered by a barcode reader and is already set up in book2 on a different sheet under the same heading. What i need to happen is on the second sheet of the same workbook, when the barcode is scanned by the user, i need excel to go to the first sheet, search the "SKU" column, if it finds a match in a range of cells, i want it to copy the whole row of data that was matched by the "SKU" to the second sheet into the "next availble row" so to speak.

So in a nutshell, this is what i need. I scan a barcode, which comes out as numbers, into a cell on sheet2, lets call that B1, that number that is entered by the scanner is compared to numbers on sheet1 in the range of lets say A2:500-J2:J500, if the number scanned matchs any of the numbers in the "SKU" column, which is C2:C500 by the way, i want all the cells in that row to be copied to sheet2 on the first availble line under a header that has the same structure as sheet1. I then enter another scanned barcode into the same B1cell and the process starts over entering the set of cells into the "next available line under the last entry". Hopefully this gives you a little better look at what im trying to do. Keep in mind that macros and conditional formatting cannot be used in this application.

By the way, the only reason i mention the 2 workbooks is for clarifacation purpose only that there are 2 workbooks in this project that work together. This specific problem will not effect the 2nd workbook. Or should'nt anyway.

Thanks for taking the time to look,

I think you would need VBA for this. If a formula based solution could be developed, any values would be replaced when the "lookup" value was change.

It's extremely difficult to come up with any kind of a solution without seeing the structure of the workbook and the worksheet and some examples of the data, albeit not live or sensitive data.


Regards

I've put some formulae into the sheet.

Unfortunately, I can think of no way of being able to update a single cell and populate the "next" row without using VBA.

You could, perhaps, have a Data Validation list for each of the SKUs and drag down the formulae.

Different approach; see the example: sheet3 (DV)


Regards, TMS

The SKUList is a dynamic name range, so if you add entries to sheet1, it will cope with that and they will be added to your Data Validation list.

To be able to enter a SKU in cell B4 and it transfer data from the "master" list and add it to the bottom (or top) of another list will require VBA.

Good luck with whatever approach you choose to adopt.

Regards, TMS