# non-volatile alternative to indirect function

#### Soulfree

##### New Member
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

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
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.
Code:
``````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
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:
Code:
``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.

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,

Replies
2
Views
128
Replies
6
Views
553
Replies
3
Views
149
Replies
2
Views
666
Replies
7
Views
998

1,196,048
Messages
6,013,089
Members
441,747
Latest member
darkman77

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back