entering data without duplication of supplier names

[Stewit]

I have a sheet im trying to devise for monthly budgeting when making
purchases from suppliers, i may purchase from same supplier 3 times in the
month.

So far I have everything in the sheet to get this going, ideally I dont want
to have a static supplier list on the right next to their respective monthly
total.
COL"B" COL"C" COL"D" COL"E" COL"F"
Suppliers Date Of Purch Amount Supplist Con Amount
ables 24/03/2003 $500.00 ABC $ -
fbc 25/03/2003 $1,000.00 Ables $1,500.00
sss 28/03/2003 $200.00 Allens $ -
ables 30/03/2003 $1,000.00 Arty Group $ -


This is what I have, unfortunately I can't figure out a way not to have a
static supplier list. thats the issue i have, if I have not purchased from
them I don't really want to see them in the Supplist on the right.

Any help with sorting this will be greatly appreciated.

 

[Stewit]

Formatting got a little screwed on the way here sorry bout that.

COL"B" COL"C" COL"D" COL"E" COL"F"
Suppliers Date Of Purch Amount Supplist Con Amount
ables 24/03/2003 $500.00 ABC $ -
fbc 25/03/2003 $1,000.00 Ables $1,500.00
sss 28/03/2003 $200.00 Allens
$ -
ables 30/03/2003 $1,000.00 Arty Group $ -

 

[Frank Kabel]

Hi
not really sure what your proble is. What do you mean with 'static'
supplier list. do you mean if column F is blank?

 

[Debra Dalgleish]

Instead of creating maintaining a supplier list in column E, you could
create a pivot table from the data in columns B. There are
instructions and links here:

http://www.peltiertech.com/Excel/Pivots/pivotstart.htm

 

[Stewit]

Hi Frank,

Sorry about the lack of information in the initial posting...

What im trying to achieve is entering multiple purchases with the same
supplier when i enter into Col B

COL B supplier is entered
COL C date of purchase entered
COL D Amount of purchase
COL E Static list of Suppliers is presently here
COL F accumulative totals for each supplier here next to their name only
after Supplier name and amounts are entered.

I tried to emulate the look with my initial posting to give a better feel
for what im trying to do but the formatting was lost when I posted the
question.

I have been successful in getting the formulas for the cummulative amounts
to show next to the Supplier name in COL E. but im stuck with a static list
of suppliers in COL E. What im really wanting in COL E is a show of
suppliers only from which I have made purchases from.

That way the supplier list in COL E will be a whole lot shorter than it is
at present.

the formula I use for the figures in COL F is
=IF(E8=0,"",SUMIF($B$8:$B$102,E8,$D$8:$D$102))

In a previous version of this sheet. I had the supplier name not showing
till there was a $ amount in COL F next to them, that then gave me a whole
lot of spaces down the COL and that looked really messy.

maybe im being too fussy?

Hope this doesn't bend your brain too much, it currently has me beat!

thanks for looking at it
Regards
Stewart

 

[Frank Kabel]

Hi
still not totally sure but I would set-up the sheet in a different way:
- col B-C could stay as they are
- For creating a report about your suplliers and the respective amount
I'd use a pivot table which would create this report nearly
automatically.
See
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

This way you just have to refresh the pivot table to get a current
report (with only the suppliers from which you have bought something).
I would also use data validation for column B (to allow only valid
suppliers). See
http://www.contextures.com/xlDataVal01.html

 

[Stewit]

thanks for your input Frank I'll have a go

this will be my first time with pivots so will be an interesting experience.

regards

Stewart