Summing with variables

[DPO]

Hello. I am looking to create a formula which sums the values associated with several defined names. For example, I have a workbook with the following defined names SalesPerson1Total, SalesPerson2Total, etc. and these amounts are all sourced from multiple tabs. The amount of defined names (i.e. 'SalesPersons') is variable, therefore, I want the formula to read Sum the values of all defined names which are named with the following convention 'SalesPerson(X)Total'. I hope this makes sense. Thanks in advance!

 

[ravishankar]

Hi
Paste the following codes in the macro window ( alt F11)

Sub Macro1()
    Range("A1").Select
    Selection.ListNames
    x = Cells(Rows.Count, 1).End(xlUp).Row
    For a = 1 To x
    b = Cells(a, 1)
    Cells(x + 1, 1) = Cells(x + 1, 1) & Cells(a, 1) & ","
    c = Cells(x + 1, 1)
    d = Left(c, Len(c) - 1)
    Cells(x + 3, 1) = "=Sum(" & d & ")"
    Next a
End Sub

run the macro. It will list all salespersons in col A and their total underneath it.
Ravi

 

[DPO]

Hi Ravishankar, Thanks for the reply, however, I was hoping to do this via a formula and not a macro. Any thoughts?

 

[DPO]

As a follow-up, here is what I have tried so far (but w/o any luck):

=IF(ISBLANK(E$1),"",SUM(INDIRECT("SalesPerson"&"*"&"Total"))/INDIRECT("SalesPerson"&E$1&"Total"))

Where the value of E1 (F1, G1, etc.) will be the names of each potential Sales person. This formula should sum all of the total sales (already summed on their individual tabs) of each respective sales person and then divide by the total for the person represented in E1, F1, G1, etc. to provide a % of total sales per salesperson.

 

[ravishankar]

Hi
in any cell enter
=SUM(salesperson1total,salesperson2total,salesperson3total,....) will give you the total of the range mentioned.essentially that is what my macro does.
Ravi

 

[DPO]

Hi. Thanks again for the reply. I understand that I could 'hard code' in the defined names for each iteration of 'SalesPerson(x)Total', however, the number (x) of SalesPersons will continue to change, therefore, I would like to make the formula dynamic in that for any defined name with a naming convention = to 'SalesPerson(x)Total', the formula should total the values of those fields. Does that make sense?

 

[ravishankar]

HI
I am confused. if I say salesperson(10)total: does it mean 10th salesman - john's total or total of 10 salesmen? Which cells are these info. stored
Ravi

 

[DPO]

Hi. Currently, for the sake of the example, I have 3 salesmen:

Sheet 2, Cell A1 is a def. name (SalesPerson01Total) which sums A1:A500

Sheet 3 Cell A1 is a def. name (SalesPerson02otal) which sums A1:A500

Sheet 4 Cell A1 is a def. name (SalesPerson03otal) which sums A1:A500

On Sheet 1, I want my formulas to reside in cells E2, F2, and G2 with cells E1, F1, G1 holding the heading 01, 02, etc. I want the formula to be able to accomodate a new tab (Sheets 5, 6, etc.) with the same format/defined name as Sheets 2,3,4 and just need to add a new column heading on Sheet 1 and copy the formula over. I hope that makes my task more clear, please let me know if you need more info.

 

[DPO]

Hi Ravishankar, Just checking in to see if my last reply helped to clear things up and if you have any thoughts on a solution for my formula? Any help is greatly appreciated. Thanks, David

 

[DPO]

Hi all, Is anyone able to help me with this request below? I don't want to post a new thread, but I'm concerned that no one is responding because Ravishankar has been helping me so far. Unfortunately, I have not heard back from him and I have a deadline, so I need to either figure this out in a short time frame or go a completely different (less dynamic/advanced) route than what I'm shooting for. Help....please!