I know my request may seem strange but if a solution is found it will be very useful for people having the
same of my situation.
I am gonna make it simple to explain.
I have a workbook that holds guests requests of a hotel, it consists of 31
sheets (a complete month) each sheet/each day will contain in Column A the different guests requests up to 100 requests with
thier relevant values in other columns (time, status, duration…etc)
The last sheet is called Monthly Summary that
has formulae applied on all the days.
As you know, the maximum number of requests throughout the month can be
31*100=3100 requests, so there are 3 columns in this Monthly Summary sheet hold formulae that do calculations based on the
above-mentioned relevant values in each day, I had to drag down these formulae till the cell 3000 to cover to the maximum
number of requests throughout the month but in the other hand this caused the Excel file to have a very large size and
causing delay in opening and saving (charts and macro also added as well).
I tried to reduce the filled cells with
formulae, and indeed the Excel file size has dropped significantly.
My question, is there any way or a Macro VB
that can count the number of requests in each day and based on that drag down or fill the formulae in these 3 column, in this
case the file size will get larger based on the number of requests.
It is important to note that in case what I am
asking can be done, the formulae are dynamic and changeable according to the cells, we are not filling a constant values.
I hope I could make clear.
I uploaded the file so other people can benefit from it and can also have a
clearer look on it for a better help and contribution.
The zip file contains two files, the original and the
reduced size one (big difference you can see)
You will notice in the reduced size file that I deleted rows (101
till 3102) in the "Delayed" sheet in the Column A, B, C
These are the column that I want their rows to expand automatically starting from the cell 101 and get filled automatically
with the formulae.
The expansion number is based on the total orders in each day; you can benefit from the total
orders sum I have put in the end of each day.
It is on this link http://www.mediafire.com/?28ca2n7pvdfb32q
Thanks in advance for your help.