Excel Dynamic Column Formula

[tWINKLE]

Hi,

I am working with excel dashboard reports. But i am stuck with excel dynamic list.


I do have a list with dynamically growing column field.

Say Column1, Column2,..... This Column number is indeterminate. It depends on user input.

As column number grows i want to print sum of each column filed value ..


Please help..

Thanks in advance

 

[tWINKLE]

Please give me simple method..

 

[Faseeh]

Hi tWiN,


Can you give me a layout of your worksheet? Will you be totaling all the column once a new one is added or you want sum for each column plus for the one newly added.


Assuming your data is like this between A1:G5, use this:

=SUM(OFFSET(A1,,I1-1,5,1))

...where I1 contains column Number. This will give you sum of any column just be changing column no. where as you can also change the no. of row by replacing "5" in above formula by any no. of rows.


Regards,

Faseeh

 

[tWINKLE]

Okay. Let me explain


500k

Basic Pay .....................


200k ..............

300k ..............


This is my data format. Here Column fields are (Salary head) not pre determinate. Dynamically increasing column fields. But i want total spent for individual salary heads.

 

[tWINKLE]

Thanks Faseeh..


But it is not sure that my data will remain in the range A1:G5. It may cross. I can work with SUBTOTAL to get column sum if i could know columns prior. But it may vary according to employees. In such condition how can i set that formula. Here i am stuck with..;(


tWiN..

 

[Faseeh]

This is the data i worked with:

[pre]

8	4	19	13	13	18	18
14	18	16	12	11	15	4
12	11	16	3	10	12	16
12	19	12	14	3	15	17
7	16	12	1	11	2	5

[/pre]
copy it to an excel sheet between a1:g5 and use my last formula to see whether it works or not in column I1 enter no. of column. If still not working please upload the sample file! (and there is no need to get so much stressed cheer up


Faseeh

 

[Hui]

tWiN


Firstly welcome to the Chandoo.org Forums


Can you lease read the 3 Sticky posts on the main forums page


Can you please describe your problem better or post a sample file for us to review?

 

[tWINKLE]

Hi,


Thanks for your response.


http://www.2shared.com/document/MVZOO_95/Dashboard.html


This is my sample work sheet. I want individual salary head total. I am using excel template to generate my report. I can directly dump data into template sheet like sample one. And it must automatically generate salary head total. How can I design excel template file?


tWiN

 

[Hui]

tWiN

How about

J1: =SUM(OFFSET(J$3,,,COUNTA(J3:J10000),1))

Copy across

 

[tWINKLE]

Okay,,

Thanks Hui...