non-volatile alternative to indirect function

[Soulfree]

Hi!
I am sure that there are better ways to solve my issue, but I am not proficient enough to come up with a solution myself, so i am throwing myself at your mercy :P

What I am trying to do is to get identically structured information from the same cells in multiple worksheets (up to 200) and and put them in one worksheet as a list for the purposes of comparing, filtering and sorting the information.

I am using =IF(ISBLANK(INDIRECT(A4&"!E3")),"",INDIRECT(A4&"!E3")) where the name of the sheet to get the information from is placed in the column A, and the cell to get the information from is placed in the text string.

Each row in the list gets information from a new worksheet.

Thanks for all help

 

[RickXL]

Hi and welcome to the MrExcel Message Board,

I think I would use VBA. Would that be suitable for you?

I tried a User Defined Function but that had the problem that because it was not volatile it would not always update - so the reverse of the current situation.

Then I tried to write a macro that detected when a change had been made to one of the numeric worksheets. When that happened, it would look to see if it was one of the cells it was interested in and if so, it would also make the change to the Deltakere worksheet at the same time.

The following code needs to be pasted into the ThisWorkbook (in English) macro module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim wsDelta     As Worksheet
    Dim nRow        As Long
    
    If IsNumeric(Sh.Name) Then
        On Error GoTo err
        Application.EnableEvents = False
        Set wsDelta = ThisWorkbook.Worksheets("Deltakere")

        nRow = Sh.Name + 3
        Select Case Target.Address
            Case "$E$3": wsDelta.Cells(nRow, "C") = Target.Value
            Case "$D$3": wsDelta.Cells(nRow, "D") = Target.Value
            Case "$A$1": wsDelta.Cells(nRow, "E") = Target.Value
        End Select
    End If
err:
    Application.EnableEvents = True
End Sub

I invented some examples so that you could see the pattern required to add new cells. The work is done here:

Case "$E$3": wsDelta.Cells(nRow, "C") = Target.Value

That says, if the changed cell was "$E$3" then update column "C" row nRow on the Deltakere sheet.
nRow is set to 3 plus the numeric sheet number. I am not sure if that is OK? You might want to actually look up the value in column A as now. I just assumed that the calculation would work. If you do need the look up, for instance, if you need to sort Deltakere and have things still work, then I can add that.

When placed in the ThisWorkbook module, the code above will run every time a change is made to a worksheet. The first thing it does is checks to see if the change was on a numeric sheet.
If so it adds 3 to the sheet number and checks the address to see if it is one of interest.
If E3 is changed on, for example, sheet 4 then the value is also written to column C on row 4+3=7.

I added these as examples:
If D3 is changed on, for example, sheet 6 then the value is also written to column D on row 6+3=9.
If A1 is changed on, for example, sheet 99 then the value is also written to column E on row 99+3=102.

The Application.EnableEvents lines stop the macro from re-calling itself in a loop and the On Error line helps to stop it switching off the events when an error occurs.


Regards,