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
| 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
| reply to Dezbend
The challenge here really has to do with the fact that you have to loop through a column to check. I have no idea how to do loops in Excel.
FYI, to reference a cell in another sheet: Sheet1!A1 would reference A1 in Sheet1. I believe it's based on Sheet name.
--
Come play Mafia! | My Picture Blog |
|
yock
TFTC
Premium
join:2000-11-21
Fairfield, OH | If this requires a loop, then forget formulas. You really need VBA for that. |
|
drew
Reformation
Premium
join:2002-07-10
Port Orchard, WA
clubs:
·wavebroadband
| reply to yock
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 |
|
Dezbend
Premium,MVM
join:2001-04-20 | reply to yock
not to sound dumb, but what is VBA? |
|
yock
TFTC
Premium
join:2000-11-21
Fairfield, OH
| 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 |
|
drew
Reformation
Premium
join:2002-07-10
Port Orchard, WA
clubs:
·wavebroadband
| reply to Dezbend
Visual Basic for Applications.
Essentially, it's the programming language that allows you to do all sorts of complicated stuff in Office products without being a full-fledged developer.
While I cannot guarantee any results, if you'd be willing to share with me a copy of the XLS you have and maybe a bit more detail on the spec, I could *possibly* write the app. yock  knows far more VBA than I do IIRC, but I know his time is more valuable than mine.
--
Come play Mafia! | My Picture Blog |
|
bjl
Premium
join:2002-05-02
Newport Beach, CA | I know more VBA than I want to admit. (I hate MS Access!) |
|
yock
TFTC
Premium
join:2000-11-21
Fairfield, OH
| reply to Dezbend
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 |
|
yock
TFTC
Premium
join:2000-11-21
Fairfield, OH | reply to bjl
My hatred of Access is only surpassed by that of VBA. |
|
drew
Reformation
Premium
join:2002-07-10
Port Orchard, WA
clubs: | reply to bjl
MS Access is a pile of garbage. GARBAGE. |
|
yock
TFTC
Premium
join:2000-11-21
Fairfield, OH | reply to yock
Except, I should add, I'm happy to help with either. =) |
|
drew
Reformation
Premium
join:2002-07-10
Port Orchard, WA
clubs: | reply to yock
RE: VBA
While VBA is a bastard-child of stuff that should've never been born, it's pretty freaking handy IMO. |
|
yock
TFTC
Premium
join:2000-11-21
Fairfield, OH
| reply to drew
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
| 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 |
|
Dezbend
Premium,MVM
join:2001-04-20
| reply to Dezbend
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: | 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
| 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
| 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 |
|
