I have finally gone back to work after a severe car accident. So 10 years later I have forgotten much and much has changed.
Please refer me to the best books, tutorials or training information. I need to become an Excel Wizard in my new position. I will do the work. I need help on this quickly.
As far as forums go, this is the best, I will use this and the links posted by Mr. Excel.
CHALLENGE:
We have unique properties around the country with over 10K residents. I have to bill and survey all residents.
First Issue - Automation
We use a cloud solution for managing all properties. Each property will export a unique csv file that needs some manipulation and then imported into the billing software.
I recently got help with a formula to manipulate using the following thanks to tweedle: =CONCATENATE(LOOKUP($M1,{"elec","gasr","misc","trsh","wter"},{"Electricity","Gas","Admin Fee","Trash","Water"})," ",RIGHT(H1,LEN(H1)-SEARCH(" Due",H1)))
This worked. Now to automate further.
To use the formula I have to:
1. Open a sheet and copy, paste, fill down in a column.
2. Copy the data values and paste the value to the correct column for the import.
3. Delete the column with this formula.
3. Save
4. Lather Rinse Repeat for each property.
I currently have 25 csv files and would like to automate the process of each sheet but see if it is possible to modify all sheets globally. The data and placement for each sheet is identical (format, type). A sheet may have over 2000 rows.
To demonstrate my lack of knowledge I have not figured out how to paste the formula in each sheet. I copied it to a text file and then go back to that and copy and paste into each sheet. Otherwise I end up with 4 REF# errors.
Is there a place to save a custom formule in Excel to paste in any sheet in the future?
That's enough for now, I thank you in advance for your help. I need to be the hero here at work. They are so busy that they don't take time to do things the right way.
If I knew how to get an image into the forum I would, please help with that as well. I have tried several things.
Using MSO 2010 and Ultimate 7.
Please refer me to the best books, tutorials or training information. I need to become an Excel Wizard in my new position. I will do the work. I need help on this quickly.
As far as forums go, this is the best, I will use this and the links posted by Mr. Excel.
CHALLENGE:
We have unique properties around the country with over 10K residents. I have to bill and survey all residents.
First Issue - Automation
We use a cloud solution for managing all properties. Each property will export a unique csv file that needs some manipulation and then imported into the billing software.
I recently got help with a formula to manipulate using the following thanks to tweedle: =CONCATENATE(LOOKUP($M1,{"elec","gasr","misc","trsh","wter"},{"Electricity","Gas","Admin Fee","Trash","Water"})," ",RIGHT(H1,LEN(H1)-SEARCH(" Due",H1)))
This worked. Now to automate further.
To use the formula I have to:
1. Open a sheet and copy, paste, fill down in a column.
2. Copy the data values and paste the value to the correct column for the import.
3. Delete the column with this formula.
3. Save
4. Lather Rinse Repeat for each property.
I currently have 25 csv files and would like to automate the process of each sheet but see if it is possible to modify all sheets globally. The data and placement for each sheet is identical (format, type). A sheet may have over 2000 rows.
To demonstrate my lack of knowledge I have not figured out how to paste the formula in each sheet. I copied it to a text file and then go back to that and copy and paste into each sheet. Otherwise I end up with 4 REF# errors.
Is there a place to save a custom formule in Excel to paste in any sheet in the future?
That's enough for now, I thank you in advance for your help. I need to be the hero here at work. They are so busy that they don't take time to do things the right way.
If I knew how to get an image into the forum I would, please help with that as well. I have tried several things.
Using MSO 2010 and Ultimate 7.
