Help with Creating a Dynamic Database

[audette9]

I think I need to be pointed in the right direction on this one - I have to create a database for an auction, that summarizes what each individual has won from different lists.

For example, I need to be able to select "Paddle Number 110", and then the list populates with all of the items "Paddle Number 110" was won. The list of items won consists of three seperate lists, that need to be summarized into one by the Paddle ID number,

Is Power Pivot the best way to do this? I tried creating the relationship, but with the way thew pivot works, it didn't seem like the most intuitive way and I couldn't get it to work.

Should I be looking into index and match functions?

Thanks in advance, and any help is appreciated.

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

On the face of it a simple Pivot Table with columns that pick up information from elsewhere should do the trick.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

[audette9]

The example is attached. Checkout summary tab, in yellow is what I need to auto populate based on the green.

The lists to pull from are in the respective tabs.

[alansidman]

Maybe an Access Data Base. Look at this link for a template: https://sites.google.com/site/freeauctionsoftware/

[audette9]

I am not sure that would help, it seems that one is more of a static list. I would need one that changes the displayed data based on a filter. That's why I was thinking Power Pivot, I basically need to combine data from three pivot tables.

[alansidman]

The reports update when data is changed in a database. You would run reports as needed. Your title asks about databases, therefore I offered a database solution. There is nothing static about a database. Do not confuse database management concepts to spreadsheet concepts. They are not the same. https://www.datanumen.com/blogs/acce...e-makes-sense/

[audette9]

I see what you are saying, but unfortunately I do not have Microsoft Access.

I believe this can be done simply in excel tabs - I might not be explaining it right. I uploaded a new file which I think shows much better what I am trying to do. It also shows how I started with the Index functions, but I cannot seem to get it to return values that may be the same, or repetitive, etc.

[audette9]

Sorry, don't think it was attached.

[JeteMc]

Here is a Power Query/Pivot Table proposal:
1. Standardize the column headers (i.e. Item Description)
2. Convert ranges to tables
3. Utilize Get & Transform > Get Data > From other sources > Blank query
4. Type =Excel.CurrentWorkbook() in the Power Query Editor formula bar (note that the function is case sensitive)
5. Use the following advanced editor code:

Please Login or Register  to view this content.

6. Produce a pivot table (blue) based on the resulting Power Query table (green) as modeled on the PQ PT sheet.
Let us know if you have any questions.