automatisch tellen

[Mr. Tape]

L.s.

Voordat ik een bestand(je) plaats probeer ik de vraagstelling zo al wat duidelijker te stellen.

Mogelijk slaat iemand al op onderstaande aan.

Ik heb een groot rooster bestand.
Elke periode van 28 dagen heeft een eigen tabblad. Deze zijn ook als zodanig genummerd, dus 1; 2; 3; 4; enz.
In een tabblad met de naam telling wordt er zoals de naam al zegt geteld wat er in een bepaalde periode is weggezet.
Nu laat ik de telling doen mbv de formule index (zoeken op naam en datum) en met aangemaakte* werknamen* van bv de periodes tabellen, echter dat is dan fysiek vastgezet op puur en alleen die ene naam (lees tabblad). Bij het willen tellen van een ander werkblad, moet je de vastgezette namen in dat blad aanpassen en de cellen doorvoeren.

Is het mogelijk om dit dynamisch te maken.
Bv je werkt in tabblad van periode 1, en in het tabblad telling worden de waardes geteld uit het actieve werkblad ( 1 dus).
Klik je in periode 2 om daar wat in te veranderen, moet indien mogelijk terstond de telling worden bijgehouden met de waardes in tabblad 2.

Je kan natuurlijk de telling ook op elk tabblad wegzetten, doch Excel 2007 is nu al best traag bij het automatisch tellen. Ik denk dat het een hoop formules ( dus gereken) scheelt, mocht je dit centraal ergens op één tabblad kunnen doen.

Is dit mogelijk met een formule of moet er een stukje vba geschreven worden.

Grt. Ernie

 

[bandito bob]

Ik snap je vraag nog niet helemaal, maar ik denk dat je veel kan bereiken met de indirect() functie.

 

[Mr. Tape]

Ik denk dat ik mijn vraag niet duidelijk genoeg heb gesteld.

Daarom hierbij ook een testbestandje en een nadere uitleg.

Bestand heeft de tabbladen 2-3-4-5-6 en 7, zijnde fictieve periodes.
Tabblad instelling spreek voor zich.
In het tabblad telling wordt aan de hand van de datum en de naam (nr) van de persoon de code geplaatst. In het tabblad heb ik nu een fysieke verwijzing naar het tabblad 2.
Ik heb bv een drietal tabelverwijzingen aangemaakt. (periode2-naam2 en datum2)

Ik wil eigenlijk dat de cellen (in tabblad telling) van c8:q47 automatisch gevuld worden.
En dan zodanig dat indien je op tabblad 2 aan het werken bent, de gevevens van dit tabblad worden getoond (berekend) en zodra ik klik naar een andere periode excel automatisch omschakeld en dan de gegevens uit het actieve tabblad weergeeft.
Dus niet dan ik voordat ik in een andere periode ga werken, de naamsverwijzing in de formule moet gaan aanpassen. (nadat ik de andere namen heb aangemaakt)
Natuurlijk kan je deze telling ook meteen op het desbetreffende tabblad wegzetten. Maar dan wordt volgens dan excel 2007 nog langzamer. In het orginele bestand zitten nl heel veel formules extra in, alsmede via een macro een kleurtoebedeling, etc etc etc.
Bestand is thans zo'n 12MB groot.

Ik kan er ook naast zitten, maar dacht dat het op één plaats berekenen, sneller is dan op 13 tabbladen dezelfde tabelberekening weg te zetten.

grt. Ernie

 

[bandito bob]

En kan je niet op de bladden een simpele somproduct formule zetten om de totalen te verkrijgen?
Dus b.v: op blad 2 in cel C2 de volgende formule:

=SOMPRODUCT((REST($A$8:$A$47;4)=2)*(C$8:C$47="o"))

de rest()=2 kijkt of het een Z betreft (zuid). Het 2e gedeelte kijkt of eronder een "o" staat.

Als het echt gaat om het overzicht op blad telling, dan zou ik gaan werken met de indirect() functie.
Als je in cel B7 een keuze zet voor de periode (1 t/m 12), dan kan je in cel C8 b.v. de volgende formule plaatsen:

=ALS(ISFOUT(instellingen!$C1&VERSCHUIVING(INDIRECT(telling!$B$7&"!"&"B7");telling!$A8;VERGELIJKEN(telling!C$7;INDIRECT(telling!$B$7&"!"&"C7:Q7");0)));"";instellingen!$C1&VERSCHUIVING(INDIRECT(telling!$B$7&"!"&"B7");telling!$A8;VERGELIJKEN(telling!C$7;INDIRECT(telling!$B$7&"!"&"C7:Q7");0)))

Deze formule kan je vervolgens kopieeren naar beneden en rechts.
Afhankelijk van de keuze krijg je je overzicht.
Tevens zou je de data in C7 t/m Q7 afhankelijk kunnen maken van de periode keuze.

 

[Mr. Tape]

Beste Bob,

Vraagje voor bij de eerste formule: ik neem aan dat dan 1 noord, 0 oost en 3 west is, indien Zuid 2 is. Dmv proberen hier achter gekomen, de logica ontgaat me nog.

Indien op het tabblad telling in cel b7 een getal komt te staan dat een relatie heeft mbt een periode lukt het me wel om de data e.d. te laten vullen.
Maar je kan dus niet deze cel laten vullen met een getal afhankelijk van het actieve tabblad.

Maar het scheelt wel veel formules aanpassen c.q. het aanmaken van nieuwe (tabel)namen.
De indirect formule ingevoerd en uitgetest (ook met enkele aanpassingen) en inderdaad dit werkt!!!.
Maar het lukt me nog niet de werking van de formule te doorgronden.

grt. Ernie

 

[bandito bob]

Op blad instellingen zie je dat het getal 1 overeenkomt met Noord, 2 met zuid, 3 met west en 4 met oost. Dit herhaald zich vervolgens (5 is dus weer Noord).
Dus als je neemt: =rest(getal;4), is de rest bij noord altijd 1, bij zuid 2, bij west 3 en bij oost 0. Vandaar dat je dan in cel C2 (hoorend bij Zuid) van blad 2 moet zetten:

=SOMPRODUCT((REST($A$8:$A$47;4)=[B]2[/B])*(C$8:C$47="o"))

Voor noord zal het een 1 moeten zijn.
Je had het dus inderdaad goed.

Over het actieve blad:
Als de formules afhankelijk moeten zijn van het actieve blad, hoe wil je die formules dan zichtbaar hebben? Dit kan alleen door dat blad te selecteren/activeren. Hierdoor zullen de formules (volgens mij) niet werken of het gewenste resultaat geven.

Voor de werking van de indirect formule zou je kunnen kijken naar de laatste helpmij nieuwsbrief. Hierin staat een speciaal excel stukje over deze functie.

 

[snb]

Vergeet al die formules.
verwijder alle formules uit het gebied A8:Q48 van werkblad 'telling'

Met deze code wordt iedere keer als er geschakeld wordt tussen een periodewerkblad het resultaat daarvan naar werkblad telling geschreven
Hiermee voorkom je ook dat bij iedere wijziging in een cel alle formules worden herberekend.
Je zult zien dat de omvang van het bestand - dat waarschijnlijk ook nog andere oorzaken heeft- zal afnemen.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
  if instr("instellingentelling",sh.name)=0 Then
    st = Sh.Range("A7:Q48")
    sn= Sheets("instellingen").[COLOR="Blue"]Range("A1").resize(ubound(st),3)[/COLOR]
    For j = 2 To ubound(st)
      For jj = 3 To ubound(st,2)
        st(j, jj) = sn(j, 3) & st(j, jj)
      Next
    Next
    Sheets("telling").Range("A7:Q48") = st
  End If
End Sub

Ook allerlei andere formules kunnen waarschijnlijk eenvoudig vervangen worden door een compacte VBA-code.

 

[bandito bob]

Ik krijg de foutmelding: End With zonder With (regel 5)

 

[Mr. Tape]

Beste snb

Ik denk dat je stukje VBA zeker zal helpen.
Maar zoals Bandito Bob al aangaf, missen we start van de With instructie.
We krijgen nu een foutmelding.

Zou je die willen toevoegen.

bv.
grt.Ernie

 

[Mr. Tape]

Onderstaande werkt inderdaad als op het tabblad instellingen de vermelding N/Z/W/O in kolom C cyclisch staan vermeld.
Hoe werkt het indien dit niet zo is en de volgorde echt willekeurig is?

Werkt dit wel met het stukje VBA van snb?
Zij het dan aangevuld.

grt.

 

[snb]

De overbodige End With uit mijn suggestie verwijderd.

 

[Mr. Tape]

@snb bedankt voor je laatste reactie.

Zoals beschreven heb ik de formules verwijderd en daarna in alle tabbladen onderstaande stukje vba gezet.

Als ik dan switch van tabblad naar tabblad krijg ik Fout 9 tijdens uitvoering: het subscript valt buiten het bereik.
Als ik dan op foutopsporing klik dan wordt deze regel geel
st(j, jj) = sn(j, 3) & st(j, jj)

Doe ik iets verkeerd??

 

[snb]

Zoals beschreven heb ik de formules verwijderd en daarna in alle tabbladen onderstaande stukje vba gezet

Da's nie best.

De code moet slechts 1 keer in de module Thisworkbook gezet worden.
Het bespaart je dus niet alleen veel formules, maar ook code.

 

[Mr. Tape]

snb

Alle vba code uit de andere tabbladen verwijderd en alleen maar in Thisworkbook laten staan.
Het resultaat is echter hetzelfde. Dezelfde foutmelding in dezelfde regel.

Is het mogelijk om jouw vba in mijn oefenbestandje te verwerken.

grt.

 

[snb]

Ik heb de code in #7 nog iets 'robuuster' gemaakt.

 

[Mr. Tape]

In de aangereikte casus werkt het nog niet honder procent.
Op het tabblad worden niet de goede waarde's mbt NZOW weggezet.
Piet 1 heeft bv de kwalificatie N, op het tabblad telling krijgt hij echter steeds de kwalificatie Z mee.
Ik kwam er wel achter dat je altijd na het aanklikken van een periode tabblad, meteen op het tabblad telling moet klikken, gevolgd door het beoogde periode tabblad.
Dit moet je ook doen nadat je iets hebt ingevoerd in een periode tabblad.
Het wordt niet *online* bijgewerkt. Ik weet niet of een vereiste van de VBA code is.
Ik hoop dat er een optie is om dit real time te laten bijwerken.

Ik denk je de verwijzing A7:Q7 door iedere willekeurige reeks kan vervangen.
Mits op alle tabbladen alles in dezelfde cel begint (staat).

Maakt het dan nog uit of er lege regels tussen de *plan* regels staan.
Of wordt automatisch het gehele tabblad gevuld met elke willekeurige vulling op het periode blad.

Nu staan de kwalificaties op het tabblad instellingen in een vaste volgorde.
Maakt het een verschil als deze ad random zijn weggezet?

grt. Ernie