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

 

[Penstrokes]

IT WORKED!!!!!!!!!!!

I did have to name the vendor, which I did in quotes. I'm so excited!

Thank you so much! I did the first part of this project by creating these spreadsheets for each of this guys sales staff, which totaled how much commission they got for each account (1 to 9 vendors for each account). They were doing it all by hand so he loved the way I did this. But then he said he needs to know how much each vendor is paying to each sales person each month. Sheesh! I said that was not a problem.... and now it won' t be.

Thanks!

Donna

 

[Zack Barresse]

Hi Donna,

Referring to Smitty's formula, you can either put A1 (or a like address) but that cell must house the criteria you are going to SUM. If you look at Smitty's example, he has put URC in A1. You could replace A1 in the formula for "URC", but as he lined the data out in such a strucutre format, you only would need to enter the formula one time in B1 and then copy down. The references would automatically change with the incriment in rows (for a better explanation check out the help files on Absolute and Relative Referencing).

If you have a large list and you want ONLY the unique items out of it and no duplicates to use in a seperate list (such as this), I'd suggest using (select your entire list, including headers, of original data) Data --> Filter --> Advanced Filter, ensure you filter for Unique Values and filter it to another location; no other criteria.


HTH

 

[Zack Barresse]

Also, Donna, if you have any real sizeable amount of data (which it sounds it may grow into) I'd also suggest using a PivotTable to display your data. They are SUPER powerful and very fast/easy to create once you get the hang of them. I'd suggest a good book (e.g. MrExcel on Excel, by Bill Jelen) you can go over them with.

Glad you got it working though!

 

[Penstrokes]

Ok, so now I have a new issue. All of the lines totalled correctly except for one. It shows $0 when there is value to those cells. There are two accounts with similar names, but I typed them exactly like they are entered. One is Mitek Brands, and one is Mitek Home. The mitek home calculated correctly, but the mitek brands is showing $0.



Donna

 

[Penstrokes]

Never mind that last question. I don't know what I did.... but I fixed it.

Donna

 

[Zack Barresse]

Donna,

Make sure you check all cells for leading/trailing spaces. If you download ASAP Utilities there is a utility on it that will do this for you. Or you could write your own routine. Which may look something like this ...


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> DeleteErroneousSpacesPlease()
    <SPAN style="color:#00007F">Dim</SPAN> cel <SPAN style="color:#00007F">As</SPAN> Range, rng <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Set</SPAN> rng = Selection
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cel <SPAN style="color:#00007F">In</SPAN> rng
        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> IsNumeric(cel) And <SPAN style="color:#00007F">Not</SPAN> cel.HasFormula <SPAN style="color:#00007F">Then</SPAN>
            cel.Value = Trim(cel.Value)
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> cel
    MsgBox "Complete!"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>



HTH