Connect values from sheets to a drop-down list

OK. You won't like this.... but.

I have added a sheet, onto which the sheet names have to be added (I have allowed for 20 sheets, but the Named Range can be adjusted to add more, if needed). I've used Data Validation on cell A2 o your Value Sorter. I had to kill the merged cell (A2&B2) to get it to work. Merged cells are the work of Satan and are best avoided wherever & whenever possible.


In cells C2 and right I have used the INDIRECT function to show you the problem. Cell E2 picks up the sheet name and combines it with E6 to correctly return 9,105,430. BUT. The syntax means that when you drag it to the right, the column letter does not update. To re-work your entire sheet would be a total nightmare.

However, by the addition of another phrase into the formula, you can get the column letter to update. See Cell F3. A more complicated formula, but when you drag it to the right, the use of RIGHT, CELL, etc, makes the column letter update as you drag to the right. BUT.

You cannot readily make something that copies DOWN as well. The rows that are being copied form the other sheets are not incrementing by 1 or 2 or 3. That would be something that could be addressed. However, in some places they increment by 1. In other cases it appears to be 22. There may be other variants.

You have two choices, maybe more.

1. Paste the formula in E3 into E22, E24, etc, adjusting the 6 at the end to reflect the target row. Then drag across.

2. It should be quite easy to get the formula set to increment the row number by 1, as you paste down two rows at a time. That would cut out a lot of the manual adjustments needed. However, I can't do that tonight. I'll certainly take a look in the morning for you. Just let me know how you'd like to proceed.

Here's some code that you can try out to edit the formulas, check out the attached example, when you click new formula, Column E formulas will change to the indirect formulas.

Sub FigureItOut()
    Dim ws As Worksheet
    Dim r1 As Range, r2 As Range, s As String
    Dim x, p As String, fla As String
    Dim Rws As Long, Rng As Range, c As Range

    Set ws = Worksheets("Value genetrator")
    Set r1 = ws.Range("B:B")
    Set r2 = ws.Range("E:E")
    Application.ScreenUpdating = 0
    r1.AutoFilter Field:=1, Criteria1:="<>"
    r2.Replace What:="=Sheet", Replacement:="""", LookAt:=xlPart, _
               SearchOrder:=xlByRows, MatchCase:=False
    ws.AutoFilterMode = 0
    r2.AutoFilter Field:=1, Criteria1:="=*""*", _
                  Operator:=xlAnd

    Rws = Cells(Rows.Count, "E").End(xlUp).Row
    Set Rng = Range(Cells(2, 5), Cells(Rws, 5))
    For Each c In Rng.SpecialCells(xlCellTypeVisible)
        x = InStr(c, "!")
        s = Mid(c, x + 1, Len(c))
        p = """=INDIRECT(""'""&A11&""'!""&"""
        fla = p & s & """)"
        c = fla
    Next c
    r2.Replace What:="""=", Replacement:="=", LookAt:=xlPart, SearchOrder _
                                                              :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    ws.AutoFilterMode = 0
End Sub

Can you not use this forums upload to attach pics instead of using a 3rd party uploader??