Week 8 - 03 Dec 2012 - Mon

34th day at SSMC.

Today, I had done a more detailed mind map and a breakdown of the macros' functions as a start off for today. I have also stated down which macro or part of a macro I had replaced with formula.

However, here's 1 constraint that I have found out after starting this task regarding formula. When using formula, there will be a reference cell or range. So, my concern is how to keep the calculated value if the reference cell's or cells' data will be changed.

Anyway, today I was trying to solve one of the macro's function. This macro is used to copy and paste data from one excel spreadsheet into another spreadsheet. Not just that the data quantity is quite a lot, the data is also updated weekly. I guess that's why they have chosen to used macro for this action.

I was trying to replace this macro's function with the 'IF' and 'SUMIFS' formula as there were many criteria, for example, data from spreadsheet 1 was

and to be paste into spreadsheet 2 in this format

So, the formula cannot be pull down or left, right and I have to edit the formula for every cell. This is too time consuming and too many steps and conclusion, troublesome. 

Towards the end of today, I found out the 'TRANSPOSE' function and discovered how to apply it into the excel spreadsheet. It made the whole job to be done easily and I only have to set the formula 2 times for each spreadsheet. 

I can't describe how happy and excited I was when I found out the 'TRANSPOSE' formula works for the data. (^^)

That's about all for today.