Naam van tabblad weergeven als resultaat van een formule?

[Tiene91]

Hallo,

Ik ben bezig met het verder automatiseren van een urenregistratie bestand van een oud-collega. De meeste formules snap ik nu wel hoe ze precies werken, alleen wil ik zelf nog een formule aan dit bestand toevoegen.

In het voorbeeld bestand wil ik in het hoofd-tabblad "projecten" in kolom AI de week van oplevering weergeven.
Door middel van het wekelijks invoeren van de uren aan een betreffend project kan ik zien dat het project afgerond is in bijvoorbeeld weeknummer 4 (in mijn voorbeeld).
De tabbladen hebben zodoende allemaal een weeknummer die samengevat zijn met een naam (weeknr_nacalc) zoals ik geloof ik terug kan vinden in de totalisering van het aantal gemaakte uren.

Is het mogelijk door middel van de projectnaam in cel B7 (tabblad "projecten") alle tabbladen te doorzoeken. Vervolgens aan de hand van het vinden van de tabbladen waarin de projectnaam voorkomt (in het voorbeeld week 1,2 en 4) wil ik de laatste week aangeven (naam van het tabblad) in kolom AI in het tabblad "projecten" --> wk4.

Bekijk bijlage uren_BLANCO (nieuw 2018).xlsm

Bestand werkt niet optimaal meer door het verwijderen van veel tabbladen (anders te groot bestand om te uploaden). Hoop dat dit genoeg is om mijn doel te verduidelijken.

 

[edmoor]

Ophalen bladnaam met een functie:

=DEEL(CEL("bestandsnaam";A1);VIND.SPEC("]";CEL("Bestandsnaam";A1))+1;255)

Voor als je daar genoeg aan hebt.

 

[Tiene91]

Tot op zekere hoogte heb ik het nu kloppend.
Alleen verwijst de formule nu naar het huidige tabblad "projecten" i.p.v. "wk4" waarin het project dan voltooid is.
Ik ben wat aan het puzzelen geweest met dat stukje van de CEL-formule bij A1 om het voor elkaar te krijgen om "wk4" weer te geven als resultaat van de formule door middel van de INDIRECT-formule, maar ik krijg het niet voor elkaar om de formule te laten verwijzen naar tabblad "wk4".

Is daar een mogelijkheid voor of is dit niet mogelijk?

 

[Tiene91]

Ik heb ergens een functie gevonden (zie onderstaand), maar die geeft mij nog niet het juiste gegeven.

Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, Col_num As Integer, Optional Range_look As Boolean)

''''''''''''''''''''''''''''''''''''''''''''''''
'Use VLOOKUP to Look across ALL Worksheets and stops at the first match found.
'''''''''''''''''''''''''''''''''''''''''''''''''

Dim wSheet As Worksheet
Dim vFound

On Error Resume Next

For Each wSheet In ActiveWorkbook.Worksheets
With wSheet
Set Tble_Array = .Range(Tble_Array.Address)
vFound = WorksheetFunction.VLookup(Look_Value, Tble_Array, Col_num, Range_look)
End With
If Not IsEmpty(vFound) Then Exit For
Next wSheet

Set Tble_Array = Nothing

VLOOKAllSheets = vFound

End Function

Nu wil ik eigenlijk dat deze functie mij het laatste tabblad waar een project in voorkomt als antwoord geeft (dus de naam van het tabblad moet in mijn cel AI7 naar voren komen).
Ik ga er haast van uit dat dat wel mogelijk is, maar ik heb totaal geen verstand van VBA maar dacht dat bovenstaande functie er wel bij in de buurt kwam en dat deze alleen een wijziging nodig heeft om tot gewenste resultaat te komen?

Bekijk bijlage uren_BLANCO (nieuw 2018).xlsm --> voor eventueel de kolommen waar de gegevens zich bevinden.

Ik hoop dat iemand een idee voor een oplossing heeft?

 

[VenA]

Of het lekker werkt betwijfel ik.

Function VenA(r As Range)
Dim j As Long, f As Range
  For j = Sheets.Count To 4 Step -1
    Set f = Sheets(j).Columns(3).Find(r.Value, , , xlWhole)
    If Not f Is Nothing Then Exit For
  Next j
  If Not f Is Nothing Then VenA = Sheets(j).Name Else VenA = "niet gevonden"
End Function

Aanroep

=VenA(B7)

 

[HSV]

Zo wordt het ook nog bijgewerkt.

Function VenA(r As Range)
Dim j As Long, f As Range
Application.Volatile
  For j = Sheets.Count To 4 Step -1
    Set f = Sheets(j).Columns(3).Find(r.Value, , , xlWhole)
    If Not f Is Nothing Then
      VenA = Sheets(j).Name
      Exit For
    Else
      VenA = "niet gevonden"
    End If
  Next j
End Function

Of de lichtere variant (alhoewel het bestand niet erg vriendelijk meewerkt).

Function hsv(r As Range)
Dim j As Long, f
Application.Volatile
  For j = Sheets.Count To 4 Step -1
    f = Application.Match(r.Value, Sheets(j).Columns(3), 0)
    If Not IsError(f) Then
      hsv = Sheets(j).Name
      Exit For
    Else
      hsv = "niet gevonden"
    End If
  Next j
End Function

 

[Tiene91]

Ahaa geweldig, dit zocht ik, ze werken allemaal perfect.

Maar als ik nu aan wil geven wanneer een project begonnen is in plaats van afgelopen is, welk deel van de code moet ik dan precies aanpassen, of is daarvoor een hele andere code voor nodig?
Ik heb wel een beetje geprobeerd, maar gaat me zo 1,2,3 niet lukken.
Ik heb de laatste code gebruikt in mijn bestand.

 

[VenA]

Deze

For j = Sheets.Count To 4 Step -1

even omdraaien.

for j = 4 to sheets.count

 

[Tiene91]

Oke super, weer een stap verder.
Ik had al zo'n vermoeden dat het daar in zat en wat geprobeerd, maar deze was ik niet op gekomen.

Bedankt tot zover.

 

[Tiene91]

Ik stuit toch nog op een klein probleempje.
De functies werken prima, alleen wanneer ik in een ander bestand te werk ga en een wijziging aanbreng in 1 van die cellen (het aangeven wanneer het project afgelopen is) dan wijzigt de functie in het uren-bestand van mij naar de waarde "niet gevonden".
Zo moet ik iedere keer als ik de afloop van het project wil inzien, de gegevens weer vernieuwen om de weeknummers weer zichtbaar te krijgen.

Is hier iets in mogelijk in de functie of iets dergelijks om de gegevens er definitief weer te geven (maar wel automatisch aanpassen wanneer er een nieuwe week ingevoerd wordt zoals nu van toepassing) zonder dat deze waarden iedere keer aanpassen?

 

[HSV]

Zit je in twee bestanden te werken?
Ik begrijp helaas niet veel van het verhaal.

Misschien moet je een 'With Thisworkbook' statement maken.

 

[Tiene91]

Klopt, zijn vaak twee bestanden geopend.
Ik heb het uren-bestand waarin die functies verwerkt zijn, daarnaast heb ik een nacalculatie waarin ik de uren moet raadplegen.
Die functies die nu verwerkt zijn moeten het zoeken naar de gezochte weken makkelijker maken zodat niet het hele bestand doorgeploegd hoeft te worden.

Door het werken met twee bestanden wordt nogal eens verandert van bestand waardoor, wanneer in het andere bestand gewerkt wordt, de waarde van de functie ONWAAR gegeven is.



Bovenstaande afbeelding... Zo krijg ik de waarden wanneer ik 1 wijziging maak in mijn nacalculatie-bestand.
Hieronder zoals het dan zou moeten blijven staan wanneer ik een wijziging aanbreng in nacalculatie-bestand.

 

[HSV]

Geen idee wat je aan het doen bent.

Function hsv(r As Range)
Dim j As Long, f
Application.Volatile
with thisworkbook
  For j = 4 to .sheets.Count 
    f = Application.Match(r.Value, .Sheets(j).Columns(3), 0)
    If Not IsError(f) Then
      hsv = .Sheets(j).Name
      Exit For
    Else
      hsv = "niet gevonden"
    End If
  Next j
end with
End Function

 

[Tiene91]

Het werkt nu goed.
Had het zelf ook al even doorgevoerd, maar toen ging het niet goed. Lag er vast aan dat "thisworkbook" op de verkeerde plek stond.

Ik kan in ieder geval nu weer verder, bedankt voor de oplossing.