Dezbend
Premium,MVM
join:2001-04-20
| [OT] excel help I need to move the values in fields from one sheet to another in Excel. How would I write the formula for:
If name=name then copy the value from field B (sheet 2) to B (sheet 1) and field D (sheet 2) to field C (sheet 1) and field F (sheet 2) to field D (sheet1)
Example:
Sheet 1:
Dezbend, Wolfgang | B | C | D
Sheet 2:
Name | B | C | D | E | F
Smith, Joe | 12 | 2 | 10 | 8 | 2
Dezbend, Wolfgang | 1 | 0 | 15 | 3
Jones, Amanda | 5 | 8 | -1 | 7
The list of names on sheet 2 may change from day to day… so I can’t just sort in the same fashion and always copy the same field over. I need to write an if then formula to check for the same name.
--
DSLr Mafia Member. | |
|
 
yock
TFTC
Premium
join:2000-11-21
Fairfield, OH
| Re: [OT] excel help In Excel, you should be thinking about retrieving, not sending. In other words, you need check the values from the destination sheet to determine if it should be replicated there. Trying to distribute from a central sheet would require some VBA-fu.
--
Have more fun with your GPS.
Geocaching.com | |
|
 | 
drew
Reformation
Premium
join:2002-07-10
Port Orchard, WA
clubs:
 ·wavebroadband
| Re: [OT] excel help The issue here yock, and I'm no expert on Excel, is that he has to iterate through the "Name" column to find it if it's there. I don't think you can do that without said VBA-fu. Or at least that's what my googling.
--
Come play Mafia! | My Picture Blog | |
|
|
|
yock
TFTC
Premium
join:2000-11-21
Fairfield, OH | Re: [OT] excel help If this requires a loop, then forget formulas. You really need VBA for that. | |
|
| |
Dezbend
Premium,MVM
join:2001-04-20 | Re: [OT] excel help not to sound dumb, but what is VBA? | |
|
| | |
yock
TFTC
Premium
join:2000-11-21
Fairfield, OH
| Re: [OT] excel help Visual Basic for Applications
It's a quasi-object-oriented macro language for automating complex tasks in Microsoft Office applications.
--
Have more fun with your GPS.
Geocaching.com | |
|
| | | |
| | | | 
bjl
Premium
join:2002-05-02
Newport Beach, CA | Re: [OT] excel help I know more VBA than I want to admit. (I hate MS Access!) | |
|
| | | | |
yock
TFTC
Premium
join:2000-11-21
Fairfield, OH | Re: [OT] excel help My hatred of Access is only surpassed by that of VBA. | |
|
| | | | | |
yock
TFTC
Premium
join:2000-11-21
Fairfield, OH | Re: [OT] excel help Except, I should add, I'm happy to help with either. =) | |
|
| | | | | |
drew
Reformation
Premium
join:2002-07-10
Port Orchard, WA
clubs: | RE: VBA
While VBA is a bastard-child of stuff that should've never been born, it's pretty freaking handy IMO. | |
|
| | | | |
drew
Reformation
Premium
join:2002-07-10
Port Orchard, WA
clubs: | MS Access is a pile of garbage. GARBAGE. | |
|
| | | | | |
yock
TFTC
Premium
join:2000-11-21
Fairfield, OH
| Re: [OT] excel help said by drew  :MS Access is a pile of garbage. GARBAGE. Except that as a relational database engine it's designed to do the things Dez is talking about. Creating complex copy routines in VBA for Excel is essentially recreating the relational links designed into any RDBMS. Access may be a bad RDBMS, but it still is one.
--
Have more fun with your GPS.
Geocaching.com | |
|
| | | | | | |
drew
Reformation
Premium
join:2002-07-10
Port Orchard, WA
clubs:
·wavebroadband
| Re: [OT] excel help I didn't say it didn't serve a purpose, I'm just saying it's garbage.
I do a bit of "side" work for my CEO at one of his buddy's businesses. They have an application that stores all of their auto body work, etc. and it's entirely in Access. There are THREE people accessing the same DB in access.. the thing is so slow.... Incredible.
I don't even want to know how much better it would run if PostgreSQL was made to be the backend... their licensing is free even to commercial products.
--
Come play Mafia! | My Picture Blog | |
|
yock
TFTC
Premium
join:2000-11-21
Fairfield, OH
| Is this an application you own or are you merely a user? This kind of stuff is usually best done in Access (that is, if you're stuck on MS Office apps).
--
Have more fun with your GPS.
Geocaching.com | |
|
Dezbend
Premium,MVM
join:2001-04-20
| thanks guys, but if it can't be done in Excel, I will just say it can't be done in Excel.
This is for work, and really just for a supervisor's sales tracking. He asked me to help him, but if it isn't something that can be done easily, I will refer him elsewhere 
--
DSLr Mafia Member. | |
|
|
drew
Reformation
Premium
join:2002-07-10
Port Orchard, WA
clubs: | Re: [OT] excel help As far as I can see, nothing about this is too hard.
Let us know if you do decide to go further. | |
|
| |
Dezbend
Premium,MVM
join:2001-04-20
| Re: [OT] excel help If excel can't do it, then I don't care. I track my students in an Excel, but I just pull their sales numbers and update it manually. A supervisor asked me to make him one, and I was trying to automate it as much as possible, but we don't have access or anything to use without getting IT involved.
--
DSLr Mafia Member. | |
|
| | |
yock
TFTC
Premium
join:2000-11-21
Fairfield, OH
| Re: [OT] excel help The problem too with delivering VBA-modified Excel workbooks to end users is that they WILL find some way to break it. Suddenly it isn't a formula not working or some Excel garbage, but the program YOU wrote and now must fix.
I think it's perfectly reasonable for you to tell him that Excel is not a practical solution.
--
Have more fun with your GPS.
Geocaching.com | |
|
Archivis
Your Daddy
Premium
join:2001-11-26
Earth
 ·Verizon FIOS
| So you need to put a value (Cell C), into Cell A, only if it meets the criteria in Cell B?
That's called an Array Formula. You don't need VBA for that. Excel supports this.
But yeah, you definitely need to be "retrieving" instead of "pushing" data. Dez, hit me up in PM's. If I don't know how to do it in excel, it can't be done, or you need to be a programmer.
--
'A government big enough to give you everything you want, is strong enough to take everything you have.' -Thomas Jefferson -
| |
|
| 
avd706
insert annoying animated gif here
Premium
join:2003-02-06
Union, NJ | Re: [OT] excel help mysql FTW | |
|
|
drew
Reformation
Premium
join:2002-07-10
Port Orchard, WA
clubs: | The issue is that there are two sheets and the rows aren't equivalent. | |
|
| |
Dezbend
Premium,MVM
join:2001-04-20
| Re: [OT] excel help said by drew :The issue is that there are two sheets and the rows aren't equivalent. QFT
don't worry about it guys. If it isn't easy, it isn't worth it. This isn't a job requirement, it was a "hey this would be nice" kind of thing. It is easy enough for me to say Excel can't do it.
--
DSLr Mafia Member. | |
|
| | |
bjl
Premium
join:2002-05-02
Newport Beach, CA
| Re: [OT] excel help looking at it again, I think you can use vlookup. see the attached. | |
|
| | | |
drew
Reformation
Premium
join:2002-07-10
Port Orchard, WA
clubs: | Re: [OT] excel help Sheet 1 won't always have the same names as Sheet 2. I don't think this is something they wanted to do "manually" | |
|
| | | | |
bjl
Premium
join:2002-05-02
Newport Beach, CA
| Re: [OT] excel help said by Dezbend :The list of names on sheet 2 may change from day to day… I took this to mean that sheet 1 won't change, but the data in sheet 2 will. | |
|
| | | | | |
bjl
Premium
join:2002-05-02
Newport Beach, CA | Re: [OT] excel help also, right now if the name from sheet 1 isn't in sheet 2, you'll get the nasty #N/A error. you can is ISNA() and IF() to change these errors to meaningful messages. | |
|
| | | | | | |
Dezbend
Premium,MVM
join:2001-04-20
| Re: [OT] excel help said by bjl :also, right now if the name from sheet 1 isn't in sheet 2, you'll get the nasty #N/A error. you can is ISNA() and IF() to change these errors to meaningful messages. can you explain this a little more for me?
I do have a bunch of #n/a I need to clean up... I want it to result a "0" if there is no name found.
--
DSLr Mafia Member. | |
|
| | | | | | | |
bjl
Premium
join:2002-05-02
Newport Beach, CA
| Re: [OT] excel help said by Dezbend :said by bjl :also, right now if the name from sheet 1 isn't in sheet 2, you'll get the nasty #N/A error. you can is ISNA() and IF() to change these errors to meaningful messages. can you explain this a little more for me? I do have a bunch of #n/a I need to clean up... I want it to result a "0" if there is no name found. you need to wrap the vlookup functions with IF(ISNA()), e.g:
=IF(ISNA(vlookup statement),0,vlookup statement)
--
flickr gallery | photo blog (rarely updated) | play mafia! | |
|
| | | | | | | | |
Dezbend
Premium,MVM
join:2001-04-20 | Re: [OT] excel help ok thanks. | |
|
| | | | | |
Dezbend
Premium,MVM
join:2001-04-20
| correct.
the vlookup function may be what I need. when I get a chance today, I will look at this function and play around with it a little.
Thanks for pointing me in that direction bjl. I will let you know.
--
DSLr Mafia Member. | |
|
Dezbend
Premium,MVM
join:2001-04-20 | vlookup works for what I need. Thanks bjl! | |
|
|
bjl
Premium
join:2002-05-02
Newport Beach, CA | Re: [OT] excel help no problem! | |
|
Dezbend
Premium,MVM
join:2001-04-20
| one last question.... and thanks for all the help.... is there a way to turn auto sorting on? (so when he drops the raw numbers into sheet 2 it will update the numbers with the functions bjl provided me, it will then sort sheet 1 by a certain column value(i have a total column that adds up the sales)?
--
DSLr Mafia Member. | |
|
|
bjl
Premium
join:2002-05-02
Newport Beach, CA | Re: [OT] excel help you could probably create a macro and add a button to run it. | |
|
| |
Dezbend
Premium,MVM
join:2001-04-20 | Re: [OT] excel help I have a macro, how do I make a button? | |
|
| | |
Dezbend
Premium,MVM
join:2001-04-20 | Re: [OT] excel help never mind... I am following the instructions in help. | |
|
|
|
|
