Anthony's VBA Forum

[ajay]

hi i wish to ask a question in excel but for that i need to uplod the sample file that i have created. is there any way to upload the sample file and then ask my question in that context ?

[mohan.ankur]

Hi,

May be you can explain what we are trying to do. And I may not need to look at the sheet to understand.

I can not see any attachment link here. May be u can try some rapidshare or something.

The document link should be here. I will see what I can do.

Thanks
Ankur
_________________
http://www.exceldepot.com
http://www.xlmacros.com
http://www.learnexcel.org

[ajay]

hi ankur,

thanks for your reply. ok the question is like this. i have a spread sheet which u can consider to be that of Classes in a center. So i have different batches and diff no. of students in different batches. AT the bottom of each batch i take the total of each column, based on certain conditions, month wise. Different batches months coincide on the same sheet so that the totals for all batches for any month all fall in one column at various rows.

This formula for totals is same for all batches except that it works on a different range for various batches in the same column (for any one month). If there are 25 batches in a sheet, then every time i feel i need to modify this formula, i need to make changes at all those 25 places.

What i want to know and do is this.
Is it possible for me to create a formula template in some cell. The take that formula from there to all the various 25 places in the sheet. Find a dynamic way to calculate the range for each of those 25 batches which is automatically lifted by this formula and the results calculated. This would be tremendously beneficial because the next time i need to make some modification in the formula, i would be able to modify it at just one place and all changes would be accomplished automatically.

the formula template shud be sth like this (the formula itself is not important, it cud be anything). (if(iscount(RANGE),"",if(......) that i may place in just one cell. then place this formula at the various places reqd. and the RANGE be dynamically calculated (or Lifted) from a given cell against each Batch. If i insert or delete rows the RANGE shud be automatically modified as well.

I hope i have been able to express myself well enuff for u to understand the problem. Hope to get a reply to this very interesting and very useful problem.

Thanks & have a great day,
Ajay

[mohan.ankur]

Hi Ajay,

Try readin a bit from http://www.ozgrid.com/Excel/DynamicRanges.htm

Hope this helps.

Regards
Ankur
_________________
http://www.exceldepot.com
http://www.xlmacros.com
http://www.learnexcel.org