[Excel] Het grote Excelvragentopic #43

Ik heb een spreadsheet waar ik datums onder elkaar invul met daarachter een aantal kolommen met data relevant voor die dag.
Nu zou ik graag alleen de weekdagen er in hebben en dan vervolgens 2 witregels (zaterdag/zondag) en na dan weer gewoon verder gaan. Het is namelijk zo dat ik een deel van de data in dit formaat aangeleverd krijg en anders iedere keer als ik nieuwe data aanmaak handmatig iedere 6e en 7e rij moet wissen.

Mijn vraag is dus eigenlijk: Kan ik automatisch elke 6e en 7e rij wissen met een standaard functie? Ik heb de witregels namelijk nodig voor overzicht en om handmatig een optelsommetje ergens te kunnen maken van de week.

Voorbeeld van de spreadsheet in de spoiler, kolommen heb ik even bij elkaar getrokken om de informatie te wissen:

SPOILER

Om spoilers te kunnen lezen moet je zijn ingelogd. Je moet je daarvoor eerst gratis Registreren. Ook kun je spoilers niet lezen als je een ban hebt.

Maak even een hulpkolom (A) met 'gewone' getallen waarvan de eerste overeenkomt met de eerste datum in de lijst. In de kolom voor de datums komt

=ALS(REST(A1;7)<2;"";A1)

En stel je de celeigenschappen van de datumkolom in op het juiste datumformat. Zou goed moeten gaan.

-Edit- even herlezend: dit levert een lijst met datums met weekdagen en witregels voor de weekenddagen. Maar als je elke zesde en zevende regel helemaal leeg wil hebben, dan moet je een macro gaan gebruiken. Daar ben ik niet zo goed in, dus daar moet dan maar even iemand anders bij helpen.

quote:

Op donderdag 16 april 2015 08:24 schreef Janneke141 het volgende:
Maak even een hulpkolom (A) met 'gewone' getallen waarvan de eerste overeenkomt met de eerste datum in de lijst. In de kolom voor de datums komt

=ALS(REST(A1;7)<2;"";A1)

En stel je de celeigenschappen van de datumkolom in op het juiste datumformat. Zou goed moeten gaan.

-Edit- even herlezend: dit levert een lijst met datums met weekdagen en witregels voor de weekenddagen. Maar als je elke zesde en zevende regel helemaal leeg wil hebben, dan moet je een macro gaan gebruiken. Daar ben ik niet zo goed in, dus daar moet dan maar even iemand anders bij helpen.

Een macro hoeft niet per se, in mijn financiële overzicht maak ik automatisch een witregel na de zondag, en stop ik na 31 december.
Mijn datums beginnen in A3 met 1 januari
Daarna in A4: =ALS(WEEKDAG(A3+1;2)=1;"";A3+1)
A5 en verder (doortrekken naar beneden): =ALS(OF(A4=DATUM(JAAR($A$3);12;31);A4=" ");" ";ALS(A4="";A3+1;ALS(WEEKDAG(A4;2)=7;"";A4+1)))

Met wat aanpassingen kan je volgens mij wel bereiken wat Barcaconia wil

Vraagje,

Als ik in Tabblad-1 een V zet in een cel, wil ik graag dat ik in Tabblad-2 een verwijzing kan maken naar die cel met de V. Dat kan in nog wel, maar eigenlijk moet die V dan veranderen in de datum dat ik die V daar neer heb gezet in Tabblad-1.

Dus op 29-3-15 zet ik een V in Cel-A1 op Tabblad-1, dan moet op Tabblad 2 in cel huppeldepup de datum 29-3-15 en dat moet ook die datum blijven.

Enig idee?

quote:

Op zondag 19 april 2015 14:44 schreef Crimineel87 het volgende:
Vraagje,

Als ik in Tabblad-1 een V zet in een cel, wil ik graag dat ik in Tabblad-2 een verwijzing kan maken naar die cel met de V. Dat kan in nog wel, maar eigenlijk moet die V dan veranderen in de datum dat ik die V daar neer heb gezet in Tabblad-1.

Dus op 29-3-15 zet ik een V in Cel-A1 op Tabblad-1, dan moet op Tabblad 2 in cel huppeldepup de datum 29-3-15 en dat moet ook die datum blijven.

Enig idee?

Volgens mij houdt Excel niet bij wanneer je wat doet, dit zal je hem dus zelf moeten vertellen.. Je zou het met een macro kunnen proberen, maar zelf de datum invullen lijkt mij makkelijker..

quote:

Op zondag 19 april 2015 16:09 schreef qu63 het volgende:

[..]

Volgens mij houdt Excel niet bij wanneer je wat doet, dit zal je hem dus zelf moeten vertellen.. Je zou het met een macro kunnen proberen, maar zelf de datum invullen lijkt mij makkelijker..

Sub vandaag()
'
' vandaag Macro
'
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.FormulaR1C1 = "=TODAY()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

quote:

Op maandag 20 april 2015 10:14 schreef Twentsche_Ros het volgende:

[..]

Sub vandaag()
'
' vandaag Macro
'
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.FormulaR1C1 = "=TODAY()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

En dat dan ook nog naar sheet 2

Ik ben in mijn chronologische lijst op zoek naar de waarden die horen bij iedere eerste dag van het jaar. Voor 2010 is dat 15 augustus, voor 2011, 2 februari, etc. Hetzelfde voor de laatste dag van het jaar. VERGELIJKEN() met de -1 of 1 parameter aan het einde geeft een #N/B of ik krijg de laatste cel uit mn range. Als ik dan eenmaal de juiste dag heb hoef ik alleen nog maar een paar cellen naar rechts op te schuiven voor de juiste waarde, maar dat lukt me wel

Iemand enig idee hoe ik verder kan?

-edit-
Laatste dag van het jaar is me ondertussen gelukt:
VERT.ZOEKEN(INDEX(A3:A999;VERGELIJKEN(DATUM(U15;12;31);A3:A999;1));A3:E999;4;WAAR)

-edit2-
d'oh!
Als ik de laatste kan vinden dan is de volgende natuurlijk de eerste:
=VERT.ZOEKEN(INDEX(A3:A999;VERGELIJKEN(DATUM(U15;1;1);A3:A999;1)+1);A3:E999;4;WAAR)

-edit3-
Fuck it, het werkt toch niet

[ Bericht 10% gewijzigd door qu63 op 26-04-2015 19:47:51 ]

Ok, nu wel: VERT.ZOEKEN(INDEX($A$3:$A$999;SOMPRODUCT(VERGELIJKEN(1;--($A$3:$A$999>DATUM(U$15;1;1));0));1);$A$3:$E$999;5;WAAR) voor de eerste datum in een jaar en VERT.ZOEKEN(INDEX($A$3:$A$999;SOMPRODUCT(VERGELIJKEN(1;--($A$3:$A$999>DATUM(U$15+1;1;1));0))-1;1);$A$3:$E$999;4;WAAR) voor de laatste.

Beste Forumleden,

Ik wil in excel een optie toevoegen waarbij als XFC1 de waarde van cel C5 of hoger behaald, XFC4 met 1 waarde omhoog gaat. Ik heb het volgende gedaan:
1. Bij XFC5 heb ik waarde 0 ingevoerd (hier begin ik mee)
2. Bij XFC4 heb ik de volgende formule ingevoerd: =IF(XFC1>=C5;XFC5+1;XFC5)

Ik krijg inderdaad dat als XFC1 de waarde van cel C5 of hoger behaald dat XFC4, 1 wordt. Het probleem is alleen dat XFC4 steeds 1 wordt elke keer als XFC1 de waarde van cel C5 of hoger behaald, terwijl ik juist wil dat XFC4 elke keer met 1 omhoog gaat als XFC1 de waarde van cel C5 of hoger behaald.

Daarnaast vraag ik mij ook af hoe ik in excel voor elkaar krijg dat als ik dubbelklik op een cel de waarde 1 wordt en als ik er weer op dubbelklik het 0 wordt en omgekeerd. Deze functie zat namelijk in een standaard format wat ik heb gebruikt, maar heb ik perongeluk weggehaald en ik kan niet meer vinden hoe ik dit terug krijg.

Zou iemand mij kunnen helpen?

Ik heb excel 2013 (Engels)

quote:

Op dinsdag 28 april 2015 16:24 schreef Anatomy01 het volgende:
Beste Forumleden,

Ik wil in excel een optie toevoegen waarbij als XFC1 de waarde van cel C5 of hoger behaald, XFC4 met 1 waarde omhoog gaat. Ik heb het volgende gedaan:
1. Bij XFC5 heb ik waarde 0 ingevoerd (hier begin ik mee)
2. Bij XFC4 heb ik de volgende formule ingevoerd: =IF(XFC1>=C5;XFC5+1;XFC5)

Ik krijg inderdaad dat als XFC1 de waarde van cel C5 of hoger behaald dat XFC4, 1 wordt. Het probleem is alleen dat XFC4 steeds 1 wordt elke keer als XFC1 de waarde van cel C5 of hoger behaald, terwijl ik juist wil dat XFC4 elke keer met 1 omhoog gaat als XFC1 de waarde van cel C5 of hoger behaald.

Dat zou met een macro kunnen, de standaard formules doen alleen wat jij hierboven al beschreven hebt.

quote:

Daarnaast vraag ik mij ook af hoe ik in excel voor elkaar krijg dat als ik dubbelklik op een cel de waarde 1 wordt en als ik er weer op dubbelklik het 0 wordt en omgekeerd. Deze functie zat namelijk in een standaard format wat ik heb gebruikt, maar heb ik perongeluk weggehaald en ik kan niet meer vinden hoe ik dit terug krijg.

Zou iemand mij kunnen helpen?

Ik heb excel 2013 (Engels)

Dat zal dan een formulier of een macroknop geweest kunnen zijn

@qu63 De optie met dubbelklikken was inderdaad een macro. Deze heb ik gekopieerd uit het format en nu werkt het .

Ik ben zelf niet zo bekend met macro's. Hoe kan ik mijn andere vraag met een macro voor elkaar krijgen?

quote:

Op dinsdag 28 april 2015 18:10 schreef Anatomy01 het volgende:
@qu63 De optie met dubbelklikken was inderdaad een macro. Deze heb ik gekopieerd uit het format en nu werkt het .

Ik ben zelf niet zo bekend met macro's. Hoe kan ik mijn andere vraag met een macro voor elkaar krijgen?

Macro's zijn helaas niet mijn sterkste kant, maar het zou iets moeten als:
wanneer XFC1 veranderd én hoger is dan C5, dan de huidige waarde van XFC4, +1.

quote:

Op dinsdag 28 april 2015 18:10 schreef Anatomy01 het volgende:
@qu63 De optie met dubbelklikken was inderdaad een macro. Deze heb ik gekopieerd uit het format en nu werkt het .

Ik ben zelf niet zo bekend met macro's. Hoe kan ik mijn andere vraag met een macro voor elkaar krijgen?

quote:

Private Sub Worksheet_Change(ByVal Target As Range)
'Hier plaats ik code zodat alleen wijzigingen
'plaatsvinden bij het veranderen van de cel XFC1 of C5.
'Dit om te voorkomen dat je macro op hol slaat bij elke wijziging.

'Let op, mochten de getallen in C5 of XFC1 dus afhankelijk zijn van
'andere cellen moet je dit wel toevoegen

'Target is hier de cel die wijzigt! Dit kan dus elke cel in je werkblad zijn
If Target.Address = "$XFC$1" Or Target.Address = "$C$5" Then
'deze code wordt alleen uitgevoerd wanneer de gewijzigde cel XFC1 of C5 is.
'de dollar tekens zijn verplicht!
If Range("XFC1").Value >= Range("C5").Value Then
Range("XFC4").Value = Range("XFC4").Value + 1
End If
End If

End Sub

Plaats de code wel in de VB editor van je betreffende werkblad! Geen aparte modules oid aanmaken.

quote:

Op woensdag 29 april 2015 08:44 schreef Basp1 het volgende:

[..]

En dat doe je door met je rechtermuistoets op de tab onderaan te klikken en uit het menu programma code weergeven te kiezen.

ALT+F11

quote:

Op dinsdag 28 april 2015 22:48 schreef snabbi het volgende:

[..]

[..]

Plaats de code wel in de VB editor van je betreffende werkblad! Geen aparte modules oid aanmaken.

Heel erg bedankt voor het schrijven van de code! Door de uitleg die je erbij hebt gezet, snap ik ook wat je doet . Ik heb het in de VB editor van het betreffende werkblad gezet en hij werkt inderdaad als cel C5 veranderd, maar niet als XFC1 veranderd en dit is nu juist wat ik heel graag wil. Ik denk dat ik ook snap wat er fout gaat, maar ik weet niet precies hoe ik dit moet oplossen. In XFC1 staat namelijk een formule (=SUMIFS(G:G;B:B;"="&1;H:H;"="&TODAY()) en hier wordt dus de waarde mee berekent. In het stukje zeg je dat als de waarde XFC1 (of C5) afhankelijk is van andere cellen, je dit moet toevoegen, maar het gaat hier om kolommen.
Hoe kan ik dat aangeven in de code?

PS: (ik heb het volgende geprobeerd, maar toen kreeg ik een error: If Target.Address = "$XFC$1" Or Target.Address = "$C$5" Or Target.Address = "$C:$C" Or Target.Adress = "$H:$H" Then )

- Win7-Excel 2013/Engelstalig

Ik ben bezig met het identificeren van toeleveringen van artikelen en loop tegen wat problemen.
Een uniek item (heeft gelukkig een unieke barcode) komt binnen en ik wil graag de datum van dat ding hebben wanneer die voor het eerst binnenkomt.
Doordat we die dingen laten repareren komt hetzelfde artikel uiteindelijk weer opnieuw binnen met dezelfde boekingscode.
Dus komt hij 2x binnen.
Ik ben op dit moment niet geïnteresseerd in wanneer die voor de tweede keer binnenkomt (of zelfs 3 of 4de keer wanneer er iets mis is gegaan) maar wanneer die voor het eerst is binnen gekomen.

Nu kan ik filteren op oudste bovenaan (als ik het via een VLookUp doe) maar wil het dummy-proof maken want voor het geval die sortering vergeten wordt dan moet het nog blijven werken.

Nu heb ik een sumproduct formule gevonden die voor mij per unieke ID een sorteringsnummer zet qua datum.
Dit is de formule die ik heb
=IF(G2="","",SUMPRODUCT(--(G:G=G2)*(M2>M:M))+1)
Met in kolom G de unieke ID's en in kolom M de datums.

De formule doet wat ie moet doen maar sumproduct is met 180.000 records erg traag.

Iemand anders een idee?

quote:

Op donderdag 30 april 2015 15:36 schreef Basp1 het volgende:

[..]

Werkt "If Target.Address = "$XFC$1" Then" dan niet zonder de overige or regels erbij?

Nee, er gebeurt dan niks.

quote:

Op donderdag 30 april 2015 14:23 schreef nils7 het volgende:
- Win7-Excel 2013/Engelstalig

Ik ben bezig met het identificeren van toeleveringen van artikelen en loop tegen wat problemen.
Een uniek item (heeft gelukkig een unieke barcode) komt binnen en ik wil graag de datum van dat ding hebben wanneer die voor het eerst binnenkomt.
Doordat we die dingen laten repareren komt hetzelfde artikel uiteindelijk weer opnieuw binnen met dezelfde boekingscode.
Dus komt hij 2x binnen.
Ik ben op dit moment niet geïnteresseerd in wanneer die voor de tweede keer binnenkomt (of zelfs 3 of 4de keer wanneer er iets mis is gegaan) maar wanneer die voor het eerst is binnen gekomen.

Nu kan ik filteren op oudste bovenaan (als ik het via een VLookUp doe) maar wil het dummy-proof maken want voor het geval die sortering vergeten wordt dan moet het nog blijven werken.

Nu heb ik een sumproduct formule gevonden die voor mij per unieke ID een sorteringsnummer zet qua datum.
Dit is de formule die ik heb
=IF(G2="","",SUMPRODUCT(--(G:G=G2)*(M2>M:M))+1)
Met in kolom G de unieke ID's en in kolom M de datums.

De formule doet wat ie moet doen maar sumproduct is met 180.000 records erg traag.

Iemand anders een idee?

Hiermee? http://blog.contextures.c(...)cel-index-and-match/

quote:

Op donderdag 30 april 2015 00:24 schreef Anatomy01 het volgende:

[..]

Heel erg bedankt voor het schrijven van de code! Door de uitleg die je erbij hebt gezet, snap ik ook wat je doet . Ik heb het in de VB editor van het betreffende werkblad gezet en hij werkt inderdaad als cel C5 veranderd, maar niet als XFC1 veranderd en dit is nu juist wat ik heel graag wil. Ik denk dat ik ook snap wat er fout gaat, maar ik weet niet precies hoe ik dit moet oplossen. In XFC1 staat namelijk een formule (=SUMIFS(G:G;B:B;"="&1;H:H;"="&TODAY()) en hier wordt dus de waarde mee berekent. In het stukje zeg je dat als de waarde XFC1 (of C5) afhankelijk is van andere cellen, je dit moet toevoegen, maar het gaat hier om kolommen.
Hoe kan ik dat aangeven in de code?

PS: (ik heb het volgende geprobeerd, maar toen kreeg ik een error: If Target.Address = "$XFC$1" Or Target.Address = "$C$5" Or Target.Address = "$C:$C" Or Target.Adress = "$H:$H" Then )

De oorzaak is inderdaad dat de verandering van de cel in die andere kolommen zit.

We moeten heel nauwkeurig zijn in het bepalen van welke kolommen en rijen wel moeten meetellen en welke niet. Wanneer we dit niet nauwkeurig doen gaat je tellertje immers ook lopen wanneer je iets in kolom A verandert. En dat wil je niet.

De oplossing is zo simpel als complex. De Target.Address verwijst naar 1 enkele cel en niet naar een kolom. Daarvoor gebruiken we Target.Column. Column werkt hier alleen met getallen niet met letters. Kolom C is dus Column 3 en H is 8.

Voor deze cellen geldt natuurlijk wel hetzelfde. Als je berekening in C weer afhankelijk is van B, dan worden die weer niet meegenomen.

Als je overigens gewoon bij elke cel wijziging een verandering in je teller wil, dan laten we die if - then gewoon weg natuurlijk:

Oplossing de volgende 2 regels:
If Target.Address = "$XFC$1" Or Target.Address = "$C$5" Or _
Target.Column = 3 Or Target.Column = 8 Then

PS:
In VBA kan je de _ gebruiken om je code over meerdere regels te schrijven. Het is dus een soort enter maar dat hij wel moet doorlezen op de volgende regel. Zo voorkom je een hele lange regel naar rechts die niet meer te lezen is.

[ Bericht 0% gewijzigd door snabbi op 30-04-2015 23:52:01 ]

quote:

Op donderdag 30 april 2015 16:34 schreef Basp1 het volgende:

[..]

De sub Worksheet_Change werkt helaas niet met formules in cellen

Hier staat de oplossing:
http://stackoverflow.com/(...)changed-by-a-formula

En in de code zou het dan ongeveer er zo uitzien:
[ code verwijderd ]

Waarbij in de module dan nog het volgende staat.

Public PrevVal As Variant

Dat is een goede oplossing voor wanneer het ptobleem te groot gaat worden met het identificeren van welke cellen ja. Wel denk ik dat die iets moeilijker op te zetten is voor iemand die minder ervaring heeft met VBA