create a "live" input table for product prices from a list of automatic unique values

[andymcnichol]

Hey guy's,

New to this forum but im going to be on here a lot from now on asking advice! Im building a door and door frame schedule/pricing tool on excel which deals with typically 3000+ individual doors complete with frames etc.(there are about 35 different criteria for doorsets each thier own options based on drop down lists)

I want to get the excel file to price up doors automatically based on a pivot table that produces a list of auto updated unique values (the value being based on CONCATENATE of about 10 different criteria to produce a code).

The company estimators are then going to price up all the different types of doors on the project in the column next to the pivot table (which is located on a secondary sheet). I've managed to get VLOOPUP returning the correct price into the door price column of the main sheet based on information from a secondary sheet containing the pivot table and a corresponding price next to each unique value.

The estimator can price up all doors on the project by pricing only the unique values once with the lookup formula filling all the blanks in on the main sheet.

My only problem is that if after the project was priced another type of door was added into the schedule, this will add another row to the pivot table and my corresponding prices next to each unique value (CONCATENATE'd door code) will not correspond with the correct door code any more as these arent a part of the pivot table.

IS there anyway to "link" cells from outside of a pivot table to information given within the pivot table so that prices will stick with the door code they are originally assigned to regardless of the codes relative location in the pivot table list? This would enable VLOOPUP to always enter the correct door value from the pivot table/pricing column and stop the estimators from having to re-enter all prices in the correct place each time a different variant of door is added to the schedule....

Many many thanks for reading and hopefully someone can help as this has me around the bend at the minute!

Regards

Andrew

[andymcnichol]

Guys,

I've attached an example schedule I use on a regular basis.

I've highlighted the important columns in green. The pivot table is located on the second sheet. Prices the VLOOKUP searches for are immeadiately to the right of the pivot table.

Thanks

Andrew

[andymcnichol]

If anyone has any ideas at all or alternative ways to price these items it would be a great help. I cant use a price library as rates are constantly changing with fluctuations in the euro/pound and due to the fact I have so many different variables I would end up with price lists of thousands of items which would need to be managed. :/

[Xer2]

Try this and let me know what else you need.

In "SECONDARY SHEET" vba:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Target.Column = 3 Then updateRates
    Worksheets("SECONDARY SHEET").Activate
    Range("A1").Select
    Application.ScreenUpdating = True
    
End Sub

In a module in vba:

Sub updateRates()
Application.ScreenUpdating = False

Dim bottom, selRow As Long
Dim doorInfo As String

    Sheets("MAIN SHEET").Activate
    bottom = Cells(Rows.Count, "U").End(xlUp).Row
    
    For i = 13 To bottom
        Range("R" & i).Select
        doorInfo = Selection.Value
        Sheets("SECONDARY SHEET").Select
        Cells.Find(What:=doorInfo, After:=ActiveCell, LookIn:=xlFormulas _
            , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Select
            
        selRow = Selection.Row
        Range("C" & selRow).Copy
        Sheets("MAIN SHEET").Select
        Range("DG" & i).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
    Next i
        Range("A1").Select
End Sub

Here is your example with the code:

[andymcnichol]

thank you very much for replying! if i update the pivot table with new information and a row of data is inserted for example midway into the table how would i get the prices for items ive already costed up to stick with thier code in the pivot table?

[Xer2]

Try this test file and tell me if it works now.

[andymcnichol]

Hi Xer2,

Thanks for all your help but i cant seem to get the rate's on the right of the pivot table to follow the information in the table if a new item enters the pivot table list.

For example;

Originally the list of indivual items in the pivot table is 10 items long each with its own price which I have costed up.
My client then comes back to me and asks for 1 extra unique product type which i then add into the schedule.
I'll then go onto update the pivot table which will add-in this extra type of door which make the list 11 items long.
The only problem is, the pivot table by default set's the list alphabetically so the new item could be inserted midway into the pivot table therefore putting the rates I have set for each item against the wrong door type as it moves the list about.

What would you suggest? My only thought would be is if there was a way that the pivot table could only add new items onto the bottom of the list but then again if there is an ommision from the list that will still make the rates go against the wrong products.....

Again, thank you for all the help.

Andy

[Xer2]

After speaking, I believe this new file is what you need. Please let me know if not.

[andymcnichol]

Thank you very very much. Sorry for the late reply I haven't been on here often of late!