Summing with variables

DPO

Board Regular
Joined
Feb 20, 2007
Messages
131
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! :biggrin:
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hi
Paste the following codes in the macro window ( alt F11)

Code:
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
 
Upvote 0
Hi Ravishankar, Thanks for the reply, however, I was hoping to do this via a formula and not a macro. Any thoughts?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top