adres van cel weergeven

[mariannevanh]

In een spreadsheet heb ik in Blad1 en Blad2 een aantal gegevens staan.
In Blad3 ben ik op zoek naar deze gegevens en ik wil graag de cel zien waar dit gegeven genoemd is. Het gewenste resultaat heb ik nu in harde tekst in Blad3!B1.
Welke formule moet ik gebruiken om Excel mij te laten vertellen waar deze informatie staat?
Excel dient dus in alle bladen te kijken naar de gezochte tekst.

Vriendelijke groet,
Marianne

 

[JEC.]

Bvb

Sub j()
 With Sheets("Blad3")
   For Each sh In ThisWorkbook.Sheets
      On Error Resume Next
      a = sh.Name & " " & sh.Range("A1:Z100").Find(.Cells(1, 1)).Address(0, 0)
      If a <> "" Then .Cells(1, 2) = a: Exit Sub
   Next
 End With
End Sub

Of als altijd alles in kolom B staat:

Sub jv()
 With Sheets("Blad3")
   For Each sh In ThisWorkbook.Sheets
     a = Application.Match(.Cells(1, 1), sh.Range("B1:B100"), 0)
     If IsNumeric(a) Then .Cells(1, 2) = sh.Name & " " & "B" & a: Exit Sub
   Next
 End With
End Sub

 

[edmoor]

Of:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 1 Then Exit Sub
    For Each sh In ThisWorkbook.Sheets
        If sh.Name <> "Blad3" Then
            Set c = sh.Cells.Find(What:=Target.Value, After:=sh.Range("A1"), LookIn:=xlFormulas, LookAt:=xlWhole)
            If Not c Is Nothing Then
                Range(Target.Address).Offset(, 1) = sh.Name & "!" & c.Address(0, 0)
                Exit Sub
            End If
        End If
    Next sh
End Sub

 

[mariannevanh]

Hartelijk dank voor jullie hulp.
In mijn topic vroeg ik om een formule. Ik kan me voorstellen dat het met een VBA-tje gemakkelijker kan zijn om het resultaat te krijgen.
Mijn data bevindt zich echter in een spreadsheet met ingebouwde macro's die beveiligd zijn met een wachtwoord. Zonder omwegen kan ik deze dus vooralsnog niet gebruiken.
Is het celadres ook te verkrijgen aan de hand van een formule?

 

[edmoor]

Met een formule kan je niet over werkbladen heen zoeken.

 

[JEC.]

Dit is volgens mij de enige mogelijkheid

Of iets korter

=IFERROR(B1&"!B"&MATCH(A1;INDIRECT("'"&B1&"'!B1:B5");0);"nvt")

 

[AlexCEL]

Zoiets misschien?

=ALS.FOUT(ALS.FOUT("Blad1!B"&VERGELIJKEN(A1;Blad1!B1:B5;0);"Blad2!B"&VERGELIJKEN(A1;Blad2!B1:B5;0));"nvt")

 

[JEC.]

Kan ook inderdaad, maar als er meerdere werkbladen zijn wordt de formule wel lang

 

[VenA]

Het bestand beter opzetten lijkt mij logischer.

 

[HSV]

Niet die ene maar die andere twee mogelijkheden.

Een ander mag het inkorten.

 

[JEC.]

=LET(x;"Blad"&MAX(AANTAL.ALS(INDIRECT("'"&J1:J2&"'!B1:B5");A1)*RIJ(1:2));y;"B"&VERGELIJKEN(A1;INDIRECT("'"&x&"'!B1:B5");0);x&y)

 

[HSV]

Let komt niet voor in Excel 2007.

 

[JEC.]

Inderdaad, misschien in die van TS wel. Vind het een handige functie!

 

[HSV]

Is het ook, Excel wordt er stukken eenvoudiger door; Ik worstel nog om over te stappen.
Ik schat dat 75% of meer van de forumgebruikers nog geen 365 heeft (al worden het er wel steeds meer).

En ik denk dat het net als met Excel 2003-2007 nog een aantal jaartjes gaat duren voor je het universeel kan gebruiken.

 

[JEC.]

Ik zou het je zeker aanraden:thumb:
Ik merk dat er verschillende bugs zijn opgelost maar nog niet alles werkt zoals je wenst.

De filterfunctie kun je bvb nog steeds niet in de lijstvalidatie gebruiken, wat het leven een stuk makkelijker zou maken.

=ADRES(VERGELIJKEN(A1;INDIRECT("'"&"Blad"&MAX(AANTAL.ALS(INDIRECT("'"&J1:J2&"'!B1:B5");A1)*({1;2}))&"'!B1:B5");0);2;4;;"Blad"&MAX(AANTAL.ALS(INDIRECT("'"&J1:J2&"'!B1:B5");A1)*{1;2}))

 

[HSV]

Je formule werkt alleen voor de tekst in A1 die in Blad2 staan.
Alles van Blad1 wordt een #N/B.

Kleinigheidje.

 

[JEC.]

Had hem niet verder getest, ts heeft in ieder geval genoeg opties
Bij mij krijg ik nergens een fout, apart.

 

[HSV]

Zo werkt je formule hier wel @JVeer.

*({1[COLOR=#ff0000]\[/COLOR]2})