I need help with creating a multi-worksheet formula

[Penstrokes]

If I have a text name in C3 and the commission amount paid in c4, can I recall that amount for that text name in a different spreadsheet in the same workbook?

This name and $ amounts will be listed several times within one spreadsheet, and I need to create something that will recall those amounts each month. Something that I don't have to manually do each month.

Can that be done on Excel or do I need to learn access?

Thanks for any suggestions! I've totaled the monthly commission amounts for all clients and vendors, now my client wants to see how much each vendor pays each month (when they might sell to 2 or 20 different accounts).

Donna

 

[Smitty]

Welcome to the Board!

It'll help if you post a shot of your data sheet and your summary sheet with expected results. (You can do that with Colo's HTML Maker, you'll find the link at the bottom of the page).

In the meantime, here's a possible aproach using SUMIF:

Book1
	A	B	C	D
1	Client	CommissionPaid
2	XYZCorp	$25.00
3	ABCCorp	$75.00
4	XYZCorp	$25.00
5	ABCCorp	$75.00
6	XYZCorp	$25.00
7	ABCCorp	$75.00
8	XYZCorp	$25.00
9	ABCCorp	$75.00
10
11	Client	CommissionPaid
12	XYZCorp	$100.00
13	ABCCorp	$300.00
Sheet1

Hope that helps,

Smitty

 

[Penstrokes]

Thanks! I downloaded the Colo HTML maker, but alas, now I don't know how to attach my data sheet. The workbook is huge and I wouldn't want to send the whole thing.

But in the mean time (while I try to figure that out) I'll look at the idea you sent and hopefully that will jog the right brain cells so that I can make something work.

Thanks again! [/code]

 

[Penstrokes]

I looked at the formula you gave me and that gives me a start. By looking at that, I know this is what I need to do:

If all of the cells between c4:c129 have a vendor name listed, then I need the values in the cells d4:d129 totalled.

So if c4:c129=vendorA, then total all amounts for that vendor in d4:d129.

This is what I did after you gave me the formula to start:

=sum(jimtipton!$c$4:$c$129,$d$4:$d$129) But there was no name of what I'm looking for and the total ended up being 0 (zero), so I tried to insert URC after the 129, but I don't know if that needs quote, parenthesis, or what. Maybe I'm totally off.... but maybe I'm close.

Thanks!

 

[Smitty]

See the HTML Maker FAQ

You don't need to post your entire sheet, just a snapshot.

And your SUM formula contains too many conditions. I used SUMIF, so

=SUMIF($c$4:$c$129,Name to check,$d$4:$d$129)

Smitty

 

[Zack Barresse]

Yes, use quotes around text characters. Ensure that they match exactly (i.e. no leading/trailing spaces, punctuation, case sensitivity, etc) or it will be problematic for you.

.. And your SUM formula contains too many conditions. ..

You can have up to 30 arguments. In this case, it's just checking two ranges. One on the sheet the formula is on, and the other on the indicated sheet.

 

[Smitty]

OOPS!

Sorry Zack, I was looking at it as if the OP was trying to use SUM to work like SUMIF

Smitty

 

[Penstrokes]

It's taken me a while, but if I did it right, there should be a snapshot of my worksheet. I need to total the amounts associated with each vendor name into a totally different worksheet that will add up each time the vendor name is used.

JimTipton_trial.xls
	C	D	E	F
4	URC	$10.00
5
6	TOTAL	$10.00		$0.00
7	SIM2	$150.50
8	Da-Lite	$100.00
9	URC	$10.00
10	KEF	$125.50
11
12	TOTAL	$386.00		$0.00
13	Sennheiser	$126.56
14	Oxmoor	$25.65
15
16	TOTAL	$152.21		$0.00
17	Sennheiser	$322.50
JimTipton

 

[Smitty]

Based on your post:

Book3
	A	B	C	D
1	URC	$20.00
2	SIM2	$150.50
3	Da-Lite	$100.00
4	KEF	$125.50
5	Sennheiser	$449.06
6	Oxmoor	$25.65
Sheet2

Just adjust the ranges to suit.

Smitty

 

[Penstrokes]

Thank you so much! Do I need to name the vendor name (URC, Sim2, etc)? The snapshot was small, but each vendor is named several times. Your example referred to the cell location A1. Will that work instead of naming the actual name?

I really apreciate the help! I've always loved Excel but I know there are soooo many things I still have no clue about. I appreciate your help!

Donna