Calculate cost from given list

subodhsapkota · Mar 19th 2017

All,
I have a spreadsheet to calculate the cost for works / purchases.

I first have a sheet with suppliers - I will have total of about 20 or more suppliers with different items and rates as below

Attachment 71684

Now, I have got a list of suppliers from the main rates table including names of supplier, item name item unit and rate of the item.

Another table will have the name of supplier (Drop-down from the list of suppliers from previous table.

Now, when we go to the item and select a particular supplier from the dropdown list, I want the items only relating to the supplier selected on previous column to be displayed. Also, the units and rates of the corresponding items to be displayed - may be through a vlookup. As we then enter the quantity of the items it will get the total of the cost for that item.

[TABLE="class: cms_table, width: 911"]

[tr]

[td]

Item

[/td]

[td]

Date

[/td]

[td]

Day

[/td]

[td]

Supplier

[/td]

[td]

Item

[/td]

[td]

Qty

[/td]

[td]

Units

[/td]

[td]

Rates

[/td]

[td]

Total

[/td]

[/tr]

[tr]

[td]

1

[/td]

[td]

09/03/17

[/td]

[td]

Thursday

[/td]

[td]

Supplier1

[/td]

[td][/td]

[td]

8

[/td]

[td][/td]

[td]

$ -

[/td]

[/tr]

[tr]

[td]

2

[/td]

[td]

09/03/17

[/td]

[td]

Thursday

[/td]

[td]

Supplier2

[/td]

[td][/td]

[td]

1

[/td]

[td][/td]

[td]

$ -

[/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

09/03/17

[/td]

[td]

Thursday

[/td]

[td]

Supplier4

[/td]

[td][/td]

[td]

8

[/td]

[td][/td]

[td]

$ -

[/td]

[/tr]

[tr]

[td][/td]

[td]

$ -

[/td]

[/tr]

[/TABLE]

I understand it can be done using dependent dropdown lists. But as the number of suppliers on the actual data is more, it becomes very hard to get names for each suppliers.

Also, I want to give the users the freedom to add the suppliers name and also the new item as it becomes necessary.

https://drive.google.com/open?id=0B6...nM2RG4zM2tzTVU

The sample excel file is located on the link above.

Excel "gurus" - looking for your tips / help on this.

bryce · Mar 20th 2017

Re: Calculate cost from given list

Attachment 71684 - This doesnt work

Can you post a sample that includes all the tables you mentioned? If so we can help.

**royUK** · Mar 20th 2017

Re: Calculate cost from given list

The example is not very clear and contains circular references. Can you attach one that shows what the end result should be.

Attach it to the post not an external service.

Click Go Advanced below on the right of the screen

Then click Manage Attachments

subodhsapkota · Mar 20th 2017

Re: Calculate cost from given list

Bryce & royUK - appreciate your help and apologies that my post was not clear enough for you to understand.

One part of my job is to track the total cost of materials purchased by different branches - so there will be different suppliers from where we get the materials. Say 20-30 different suppliers and each suppliers will have several line items that we get from them.
So, some supplier will have 20 different items and some will have 5.

For this, I have set up a table with four columns - Supplier,Item,Unit,Rates.

And, will have the supplier and the items we can order from them next to it with rate of the items.

The whole work being dynamic, its quite common that we regularly add new suppliers and also new item from existing suppliers.

So, in a nutshell the table will have several suppliers with items and their units and rates. (The table is on sheet Rates on attached workbook.)

The spreadsheet is on a shared folder and anyone will have access to check the rates and add new items or suppliers. So, I want to make it as simple as possible. Having different tables for each supplier or naming each suppliers etc. is hard.

Now, once this is done and setup we move to the next spreadsheet. (worksheet named cost on the attached workbook)

On this table First three columns are item no, date and day of the week and have no issues on them.

The next column is the supplier - where I want to have the suppliers from my previous table on Rates to be listed.

As on the sheet the names are in duplicates - I would need the names on this only once.

After we select the supplier from the dropdown list, lets say Supplier1, I would like to see only the items that are relevant to the supplier selected to be displayed - ie only the items that the supplier is supplying again from the table on Rates sheet. So, on the attached spreadsheet, if supplier1 is selected the items on the dropdown should be Item2,Item3,Item4,Item5,Item6 and Item7.

After the corresponding item from the supplier is selected I want to use a function, like vlookup and have the rate of the item and unit corresponding to the item selected from the supplier. For instance, If supplier 1 and item2 is selected it should show m as the unit and 5 as the rate and if supplier 1 and item 7 is selected the unit should be kg and the rates should be 22.

Once this is done the formulas on the cell will calculate the total - multiplying the quantity with the rates and add them.

bryce · Mar 21st 2017

Re: Calculate cost from given list

Here is one way you could do this. I used a change event because the data on your Rates table is a stack and growing. What it does is when you select a supplier the existing validation list in column F is deleted and a new one specific to the chosen supplier is added.

I dont see QTY in your rates table. Are they entering that manually? Also, will the Items always have unique names? I ask because I used vlookups to get the get the units and rates. If there will be duplicate items this will have to be modified.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsRates As Worksheet, wsCost As Worksheet
    Dim rowRates As Long, rowCost As Long, x As Long
    Dim itemRange As Range
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    
    Set wsRates = Sheet1
    Set wsCost = Sheet2
    
    rowRates = wsRates.Range("A" & Rows.Count).End(xlUp).Row
    rowCost = wsCost.Range("E" & Rows.Count).End(xlUp).Row
    
    If Not Intersect(Target, wsCost.Range("E2:E" & rowCost)) Is Nothing Then
        Target.Offset(0, 1).Validation.Delete
        Target.Offset(0, 1).ClearContents
        
        For x = 2 To rowRates
            If wsRates.Range("A" & x).Value = Target.Value Then
                If itemRange Is Nothing Then
                    Set itemRange = wsRates.Range("B" & x)
                Else
                    Set itemRange = Union(itemRange, wsRates.Range("B" & x))
                End If
            End If
        Next x
        
        Target.Offset(0, 1).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="='" & wsRates.Name & "'!" & itemRange.Address
        
    End If
    
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic


End Sub

Display More

subodhsapkota · Mar 23rd 2017

Re: Calculate cost from given list

Thanks Bryce - it works as expected.

Calculate cost from given list

Participate now!

Share