Design help/suggestions

[JackDanIce]

Hi,

I have various tables of data (the number of columns and rows are always equal 6 x 368)

I'd like to design a "front" sheet where I can select different tables to retrieve and compare (i.e. average between values etc)

So far, I have a single drop down list and a macro that compares the value in the cell linked to the list and retrieves the related table. Code for this is:

Sub ExtractSingleProduct()

Dim i As Long
Application.ScreenUpdating = False

Select Case Range("SingleListChoice")
    Case 1
        Range("GO_Swap").Copy
        With Range("SingleListChoice").Offset(0, 2)
            .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            .PasteSpecial Paste:=xlPasteFormats
        End With
    Case 2
        Range("BS_Swap").Copy
        With Range("SingleListChoice").Offset(0, 2)
            .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            .PasteSpecial Paste:=xlPasteFormats
        End With
    Case 3
        Range("FO_Swap").Copy
        With Range("SingleListChoice").Offset(0, 2)
            .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            .PasteSpecial Paste:=xlPasteFormats
        End With
    Case Else
        i = Range("D" & Rows.Count).End(xlUp).Row
        With Range("D9:I" & i)
            .ClearContents
            .ClearFormats
        End With
End Select
Range("E:E").NumberFormat = "dd/mm/yyyy"
With Cells
    .Font.Size = 8
    .Columns.AutoFit
End With
Range("A1").Select

Application.ScreenUpdating = True

End Sub

Can people suggest ideas of how I can go about designing retriving two tables? One idea is two drop-down lists but I'd like the second list to be dynamic, i.e. not include the value from the first drop down list once selected

In addition, the number of tables is likely to increase in the future, so how can I make the code and the workbook layout to accomodate this, I'd rather not keep defining named ranges for each different table as I currently am.

If anyone wants to work on this with me, please PM for more details. I don't think it's particularly difficult as it's just data retrieval, but what I'm struggling with is an unknown number of final tables that would need to be included in the drop down lists (so those lists would need to be dynamic too) and then how to make the code adaptable for this.

Thank you in advance for any helps/thoughts/considerations,
Jack

 

[shg]

I assume you've read about dependent data validation at Contextures.

If the user always selects a table higher in the list from the first dropdown, then the second dropdown could use a dynamic named range that extends from one below the first table selection to the end of the list.

 

[shg]

Also, if the name selected from the dropdown was the same as the named range, you could skip the Select Case statement, and use the value in the dropdown cell directly as the copy source. Testing for an error would allow you to do what you're now doing in the Case Else statement.

 

[JackDanIce]

Thanks shg and how odd, it's not showing my first reply.

No, never, heard of dependent data validation at Contextures, some fun reading to do on a Sunday night me thinks

Good suggestion about the name selected from the drop down, unfortunately, examples of names are:

Gas Oil Swap
Brent Swap
3.5% Fuel Oil Swap

So I could use a VLOOKUP table to match against named ranges, but then I thought I could just use a CASE statement. But I can see what you mean and would save on the case (with an error capture). This would also allow me to add new tables and update the VLOOKUP table..

Thanks, given me something to think about.

 

[shg]

IMO, what you want to avoid is hard-coding range names in the code; that would be a maintenance headache if they change. The lookup table sounds good.

 

[JackDanIce]

Always try to avoid hard coding names! Bane of my life at the moment at work, trying to work thru my predecessor's code... and now the powers that be want new columns and stuff inserted and everything's having to be moved around - not happy!