Transfering data from Sheet to Sheet

**Coors** · 11-14-2008, 12:06 PM

I have attached an excel file attached. I have 175 different part numbers, but I made an example of 10 numbers to show my problem. On sheet “Supplist” I have a list of part numbers that I need data on from sheet “Subtotal”. Basically I need the numbers from the “subtotal” list to marry up with the part numbers on the “supplist” Right now I am just manually entering the data, I know excel is smarter than me. Any help would be appreciated.
Thanks

**martindwilson** · 11-14-2008, 12:18 PM

in cell m2 and dragged down
=VLOOKUP(B2,Subtotal!$A:$D,4,FALSE)
mind you i had to change the font colour to black in col M to see anything

**Coors** · 11-14-2008, 12:23 PM

That didn't work.

thanks for the try

**excel_guy49** · 11-14-2008, 12:26 PM

I know that this is an Excel forum and this doesn't exactly answer your question, but as a user of both Excel and Access I have found that each piece of software has it's strengths and weaknesses. Many people find comfort in using excel but should actually be using Access. Your scenario is a great example of one in which Access would be a much better application to consider using. This task could be completed in a matter of a few mouse clicks in access. In Access once the link is stablished between your 2 sheets (called tables in access) Data added to or changed in the subtotals would automatically show up in the Supplist Table. It would take a little reading and practice to understand how access can work but I would recomend it in your case. If you are interested in a little tutorial information or have specific questions feel free to P.M me, i would be glad to help.
Unfortunatly I don't know a way to solve this problem in Excel without using VBA programming and some complicated loops. (I'll be keeping an eye on this to see if one of the experts has an answer, because I would be interested to know if there is a simple way to do this as well.)

**Coors** · 11-14-2008, 12:55 PM

I have access, just prefer excel better because I only know how to use access to pull data.

**Coors** · 11-14-2008, 01:25 PM

Sorry about the font color in column M
not sure what happened

any more ideas?

**Coors** · 11-14-2008, 01:31 PM

Attached is the file with font problem fixed
Thanks

**Coors** · 11-14-2008, 03:36 PM

I entered in the data manually, but I have to do another project with similar requirements, so any help would be appreciated

Thanks

**NBVC** · 11-14-2008, 03:52 PM

Originally Posted by Coors

That didn't work.

thanks for the try

How didn't it work? It worked for me.

**Coors** · 11-14-2008, 04:17 PM

attached is the results,

Data appears, but the results are not right

**NBVC** · 11-14-2008, 04:24 PM

Try this in M2, copied down:

=LOOKUP(2,1/(Subtotal!$A$2:$A$244=SUPPLIST!B2),Subtotal!$D$2:$D$244)

adjust ranges to suit if you list passes row 244

You may need to format your results cell to eliminate the decimals from view.

**Coors** · 11-14-2008, 04:34 PM

Thank you for the help.

Now I just need to study the formula and figure out how you came up with that.

Just one question, did you create excel, you seem to know this better than anyone.

**excel_lover** · 11-14-2008, 04:48 PM

Some can't use access because of corporate IT policies to govern data storage. They are good to show a developer the interconnected properties of a business pre database development. Excel can also provide an easier means for on the fly "what if" calculations than a database for the novice user. The divide between IT and operations is large in the service sector.

**NBVC** · 11-14-2008, 05:00 PM

Originally Posted by Coors

Thank you for the help.

Now I just need to study the formula and figure out how you came up with that.

Just one question, did you create excel, you seem to know this better than anyone.

This Lookup() formula will find the last entry that matches the condition in the denominator of the Lookup Array argument (ie. the 2nd argument of the function).

If you select the cell with the formula and go to Tools|Formula Auditing|Evaluate Formula and click Evaluate to step through the way the formula is evaluated, you will see what is going on.

The denominator, when evaluated, creates an array of TRUEs and FALSEs depending on if the condition is met or not in each element of the array. The 1/denominator will then create an array of 1's or #DIV/0 errros because 1/TRUE is the same as 1/1 which =1 and 1/FALSE is the same as 1/0 which results in an error....

Now the next thing for the Lookup() function to do is look for a 2 in that resultant array... It can't find one and the function looks for the last entry in the array that is smaller than or equal to the 2.. ie. the last 1 in the array is targeted and that postion is returned... then the corresponding item from the lookup vector (the 3rd argument) is extracted.

Hope that helps...and thanks for the compliment.