Hi,
I'm trying to do several things with the goal of automating some reporting, and am not sure what the right tools to use are, so maybe you folks can provide some clarity for me. I'm trying my best and have been trying many different tools, so any help is very appreciated!
Basically, I have a directory full of daily Excel files that I need to open, and merge into a larger sheet.Based on the file namesI need to add a "Date" value to the larger Excel sheet in a column so I know what date file the same from.
Next, I have some canned formulas I wrote I need to have run every time and automatically filled down in their respective column every month
in Column AA
=NETWORKDAYS(R2,Z2,Holidays!$A$2:$A$817)
in Column AB
=NETWORKDAYS(Z2,S2,Holidays!$A$2:$A$568)
in Column AC
=NETWORKDAYS(R2,S2,Holidays!$A$2:$A$876)
in Column AD
=IF(A2=31,"RDC",IF(A2=32, "RDC",IF(A2=38,"RDC","Proposal")))
in Column AE
=IF(AA2>=60,"60 plus",IF(AA2>=30,"30-59 days",IF(AA2>=10,"10 to 29 days",IF(AA2>=2,"2 to 9 late",IF(AA2>=1,"1 Day",IF(AA2=0,"0 days","Advancedl"))))))
Next, I need to automatically screen the data and clean for certain things, like rows in column A that start with 038, 031, 032 need to be pulled to a new sheet, etc.
So, knowing these requirements how would you ladies and gentlemen suggest I proceeed? Is there a tool I should use that has a low enough learning curve for me to use?
Many thanks in advance for your suggestions