Advanced Find Replace in different column

[dnorton22]

I have a spreadsheet that has a column A with a unique code per row, multiple codes in column A, i.e. misc, gasr, wter etc.

Misc = Administration Fee
wter = Water
gasr = Gas

In the same row I have a column B that has data like = "10/6/2010 - 11/4/2010 Due Date -12/5/2010"

Based on the data in column A I need to change Column B to be "Gas Due Date - 12/05/2010" Removing the first part and replacing a value based on Column A but NOT equal to column A.

I need to replace either part of column B or all it, it already has the correct "Due Date - 12/05/2010", so the first part needs to be replaced with Gas, or Water or Admin Fee which is determined by the code in column B.

This sounds like an advanced search and replace but I cannot find in Mr.Excels forums on the Web an exact solution.

Thanks,

 

[dnorton22]

Thanks for the help.

motmit, I would like to do a code solution instead of formula. I just need to learn more about it.

My current problems, and I apologize, I have not used Excel for 10 years.

While we are using the formula, I need to copy it to a saved source and then use that as a place to copy it from and into each new spreadsheet. I used another excel sheet but it gives me #REF! errors everywhere a cell is referenced.

Should I just save it in word or text and then paste it? Not sure the best way to use this formula over and over without having to retype it each time.

 

[motmit]

the code i typed jus needs a table to have codes and real names defined as the name 'codetable' -- i thought this could be useful as it sounded like u may need more codes (i may hv got tht wrong) but this allows you to add or change codes just by including it in the named range codetable

i wouldnt call myself an expert, but as long as the sheet with information is on is sheet1 and the defined name 'codetable' exists sorry i do not know about the ref# error...

 

[tweedle]

Excellent!, One lesson down.

Excel tables to the web >> Excel Jeanie HTML 4
is an add-on to Excel that lets you post your sheets to the forum.

 

[dnorton22]

tweedle,

I wish that worked for MSO 2010. Or maybe I don't know how to install it correctly but it would not work for me. Thanks.

 

[tweedle]

The data is exported from our managment software with a workbook for each property. What is the best way to do this for what is now 16 properties but might end up being over 100? This changes everything, no?

To handle this kind of volume/growth in Excel, you would do well to create a "Controller" workbook that could maintain the inventory of Property-centric sheets; for updates such as this, and future updates that are bound to occur, and consolidated repository for aggregating your other sheet-data.
Since it sounds like you are really in the infancy of this, I would encourage a peek at Access for handling your billing and receivables needs. They are both quite capable, but over time, you are going to end up with a large set of historic data. Access will store that better and keep it more readily available than what will be potentially hundreds & hundreds of spreadsheets as the months go on.
Just a thought... depending on your potential future needs.