Tip èn vraag over complexe cel-adressen

[WHdeLeeuw]

Mijn tip:

Wist je dat 'zoeken en vervangen' ook werkt met betrekking met de inhoud
van formules in de cellen?

Ik werk met regelmaat in een spreadsheet dat gegevens uithaalt uit andere
excel-bestanden met bestandsnamen als 'omzet2015januari', 'omzet2015februari'.
En elke maand wil ik de verschillen in de cijfers tussen die maanden.
Met zoeken en vervangen wordt 'omzet2015januari'B4! automatisch vervangen
door 'omzet2015februari'B4!

Handig om te weten. Ik wist niet dat het werkte. Maar het werkt.

Mijn vraag:

Is het mogelijk om het cel-adres zelf te ontlenen aan de waarde van een
andere cel?

Anders geformuleerd:

IN Veld A1 staat 'de naam van de actuele maand'.

Is er een manier waarop ik een celverwijzing kan maken die er uitziet als
'omzet2015[inhoud veld A1, dus januari]'B4!

Wim

 

[Jezet]

Hoi Wim,

Dat kan met de formule indirect:

=INDIRECT("omzet2015"&A1&"!B4")

Let op ik heb het !-teken in de formule verplaatst naar een plek waar ik denk dat het thuishoort.

Groet,
Jeroen

 

[WHdeLeeuw]

Held!

Ahum, ik vreesde al dat voor lieden die dit probleem konden oplossen, mijn top volstrekt overbodig was ;-).
Dank je wel voor je enorm rappe reactie.

Toch nog een aanvullende vraag. Kan ik die truuk ook keer herhalen?

Namelijk voor het JAAR, dat bijvoorbeeld in B1 staat en de MAAND die in C1 staat?

='\\server\schijf\subdir\subdir\subdir\subdirJAAR\[Omzet per vestiging MAAND JAAR.xls]Tabblad'!CEL)

Alvast bedankt voor de moeite!

 

[Jezet]

Hoi Wim,

Ook dat kan:

=INDIRECT(="'\\server\schijf\subdir\subdir\subdir\subdir"&B1&"\[Omzet per vestiging "&C1&" "&B1&".xls]Tabblad'!"&CEL)

Wat de formule doet is het maakt een tekst die opgebouwd kan worden uit heel veel celverwijzingen. INDIRECT maakt vervolgens van die tekst een verwijzing naar een cel ergens in je werkblad.
Je kunt de formule controleren door eerst de tekst in de cel te zetten en als je dan tevreden bent er INDIRECT() in zetten.

Groet,
Jeroen

 

[WHdeLeeuw]

Jeroen, durf het bijna niet te vragen... Maar ben jij ervan overtuigd dat er geen schrijffout in je formule zit?
Hoe ik ook knoei, maar Excel weigert ;-(.

 

[Jezet]

Hoi Wim,

Ik zie dat er een "=" binnen de () staat die er niet thuis hoort.
Bouw de formule eerst rustig op, dus eerst het stuk ="'Tabblad'!"&CEL

Heet je werkblad Tabblad? Is CEL een verwijzing zijn naar een bepaalde cel od een bereik dat een naam heeft?

Als dat er volgens jou goed uitziet, dan plak je het werkboek er aan en dan de subdirectories er aan en als laatste de INDIRECT() er in.

Groet,
Jeroen

 

[WHdeLeeuw]

Werkt alleen bij Open bestanden

Ha Jeroen, nog even een nabrander. Heb een flinke tijd zitten stoeien met die INDIRECT()-functie, ik bleef maar foutmeldingen krijgen.

Het blijkt dat de INDIRECT()-functie prima werkt als er sprake is van een verwijzing naar andere bestanden.... Op de voorwaarde dat die bestanden geopend zijn.
Op de één of andere manier kan je dus geen informatie trekken uit een spreadsheet dat niet geopend is.

Afijn, weet dus nu in elk geval waarom het niet doet wat ik wilde. Maar feit blijft, bij open bestanden werkt het top!

Wim

 

[Jezet]

Hoi Wim,

Je kunt met VBA (programmeertaal achter Excel) ook andere bestanden openen en weer sluiten. Je gaat dan echter wel een flinke stap dieper dan ik nu inschat dat je kennis reikt.

Groet,
Jeroen

 

[WHdeLeeuw]

Haha, Jeroen, ik denk dat jij mijn kennis goed inschat ;-).

Overigens begrip ik er geen fluit van. Maar vermoedelijk zal het antwoord mijn Excel-pet
ook wel weer te boven gaan ;-)
Als ik de inhoud van een celverwijzing wijzig door middel van zoeken en vervangen, dan wordt
er wel informatie uit niet-geopende bestanden weggetrokken.
Als die inhoud wijzigt door toepassing van de INDIRECT()-functie, dan moeten die bestanden
eerst open gezet worden.

Afijn. Is dit een ingebouwde bescherming om te voorkomen dat je in één worksheet niet
een in beginsel vrijwel onbeperkt aantal verwijzingen naar andere bestanden zou
mogen hebben of zo?

Nou ja, laat maar. Mijn probleem is nog niet opgelost. Ik ga een workaround bedenken!

Dank voor je moeite, ik heb in elk geval weer wat geleerd ;-)
Wim

 

[Jezet]

Hoi Wim,

De INDIRECT-verwijzigingen werken niet, maar directe wijzigingen wel. Dus

='\\server\schijf\subdir\subdir\subdir\subdir2015\[Omzet per vestiging jan 2015.xls]Tabblad'!D4

werkt wel. Wat ook werkt is zoek en vervang.

Met VBA kun je zoek en vervang automatiseren:
Hoe kom je bij VBA? Heel eenvoudig rechter muisklik op het tabje van het werkblad en kiezen voor Programmacode weergeven
Er popt nu een nieuw scherm op dat helemaal leeg is. Daar zet je de onderstaande code in

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1:C1")) Is Nothing Then
    Range("A11:Z999").Replace What:=Range("Z1"), Replacement:=Range("D1"), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Range("Z1") = Range("D1")
End If
End Sub

In Cel D1 van het werkblad staat de formule:

="\\server\schijf\subdir\subdir\subdir\subdir"&B1&"\[Omzet per vestiging "&C1&" "&B1&".xls]Tabblad"

En in Z1 staat de huidige correcte verwijzing

\\server\schijf\subdir\subdir\subdir\subdir2015\[Omzet per vestiging jan 2015.xls]Tabblad

De code doet automatisch zoek en vervang in het gebied A11:Z999 zodra in B1 of C1 een wijziging wordt doorgevoerd.

Groet,
Jeroen

 

[WHdeLeeuw]

Dank je, Jeroen.
Ik ga hiermee aan de slag.
Als ik het begrijp, kom ik terug ;-(.
Wim