Excel - updaten formules sheets werkt niet?

[witchdoc]

Ik gebruik momenteel powerquery om vanuit een andere source records in te lezen naar sheet 1 van mijn excel workbook.
Daarna heb middels formules output op sheet 2 en sheet 3.

De input van sheet 1 kan ik updaten d.m.v. CTRL-ALT-F5.
Research zegt me dat ik met F9 de resultaten van de formules over alle sheets (dus ook mijn sheet 2 en 3) zou moeten kunnen refreshen.

Nu merk ik echter dat wanneer mijn input op sheet 1 van b.v. 10 lijnen naar 20 lijnen groeit, dit niet zichtbaar wordt op sheet 2 en 3. Hier blijf ik maar 10 lijnen zien terwijl toch echt in de eerste 100 lijnen/cellen formules staan.

Als ik de formules handmatig weer over die 100 lijnen kopiëer, dan krijgt ik wél alle 20 records te zien.

Iemand een idee hoe ik sheet 2 en 3 fatsoenlijk geupdate krijg zonder die formules opnieuw te moeten pushen?

[GRDavies75]

Misschien is het handiger om je formules dan hier neer te zetten, dat praat stukken makkelijker.

Maar mijn initiële verwachting is, dat jouw probleem met celverwijzingen (bijv A2 oid) te maken heeft. Misschien heb je de verwijzing gefixeerd? Dus bijvoorbeeld je range was A2:D10 en dat heb je geschreven als $A$2:$D$10.
Dat terwijl PowerQuery altijd een Tabel teruggeeft en kan je (mits mogelijk) beter werken met Tabelverwijzingen (bijv TabelNaam[Kolomnaam]). Tabellen hebben als voordeel dat de range van een tabel automagisch meegroeit.

Edit: Wat ik me ook besef is dat je F9 gebruikt om je formules te verversen? Normaliter worden formules automatisch bijgewerkt als er ergens in de keten waardes veranderen. Dit kan je inderdaad uitzetten en moet je wel via F9 de formule(resultaten) bijwerken. Dit doet men vaak voor als de berekeningen heel intensief zijn (bij hele grote sheets aan data en veel volatile formules). Heb jij dit uitgezet? Zo niet, dan wordt het scenario van 'foutieve' verwijzingen (niet conform verwachting) alleen maar aannemelijker.

[ Voor 44% gewijzigd door GRDavies75 op 25-11-2022 10:15 . Reden: extra verwijzing uitleg en nieuwe vraag ]

[witchdoc]

GRDavies75 schreef op vrijdag 25 november 2022 @ 10:02:
Misschien is het handiger om je formules dan hier neer te zetten, dat praat stukken makkelijker.

Maar mijn initiële verwachting is, dat jouw probleem met celverwijzingen (bijv A2 oid) te maken heeft. Misschien heb je de verwijzing gefixeerd? Dus bijvoorbeeld je range was A2:D10 en dat heb je geschreven als $A$2:$D$10.
Dat terwijl PowerQuery altijd een Tabel teruggeeft en kan je (mits mogelijk) beter werken met Tabelverwijzingen (bijv TabelNaam[Kolomnaam]). Tabellen hebben als voordeel dat de range van een tabel automagisch meegroeit.

Edit: Wat ik me ook besef is dat je F9 gebruikt om je formules te verversen? Normaliter worden formules automatisch bijgewerkt als er ergens in de keten waardes veranderen. Dit kan je inderdaad uitzetten en moet je wel via F9 de formule(resultaten) bijwerken. Dit doet men vaak voor als de berekeningen heel intensief zijn (bij hele grote sheets aan data en veel volatile formules). Heb jij dit uitgezet? Zo niet, dan wordt het scenario van 'foutieve' verwijzingen (niet conform verwachting) alleen maar aannemelijker.

mmm, er lijkt inderdaad iets mis te gaan met celverwijzingen.

de query op sheet 2:

code:

=IF(ISBLANK(Source!A2);IF(COUNTIF(A1;"*description*");CONCAT("  </NadClients>";"  <TagDictionaries>";"    <TagDictionary allowMultiple=""true"" mandatory=""false"" dataType=""String"" attributeName=""Location"" entityName=""Device""/>";;"    <TagDictionary allowMultiple=""true"" mandatory=""false"" dataType=""String"" attributeName=""sysLocation"" entityName=""Device""/>";;"    <TagDictionary allowMultiple=""true"" mandatory=""false"" dataType=""String"" attributeName=""LocationID"" entityName=""Device""/>";"  </TagDictionaries>";" </TipsContents>");"");IF(Source!$G$2="";"MISSING SHARED SECRET";CONCAT("    <NadClient description="""" name=""[";Source[@LocationID];"] ";SUBSTITUTE(TRIM(CLEAN(StripAccent(Source[@LocationName])));" (Beo)";"");"""";" coaPort=""3799"" radsecEnabled=""false"" coaCapable=""true"" vendorName=""Meraki"" tacacsSecret=""";Source!$G$2;""" radiusSecret=""";Source!$G$2;""" ipAddress=""";Source!C27;"/";32-LEN(SUBSTITUTE(TEXT(DEC2BIN(MID(Source[@MgmtNetmask];1;FIND(".";Source[@MgmtNetmask])-1));"00000000")&TEXT(DEC2BIN(MID(Source[@MgmtNetmask];1+FIND(".";Source[@MgmtNetmask]);FIND(".";Source[@MgmtNetmask];FIND(".";Source[@MgmtNetmask])+1)-FIND(".";Source[@MgmtNetmask])-1));"00000000")&TEXT(DEC2BIN(MID(Source[@MgmtNetmask];1+FIND(".";Source[@MgmtNetmask];FIND(".";Source[@MgmtNetmask])+1);FIND(".";Source[@MgmtNetmask];FIND(".";Source[@MgmtNetmask];FIND(".";Source[@MgmtNetmask])+1)+1)-FIND(".";Source[@MgmtNetmask];FIND(".";Source[@MgmtNetmask])+1)-1));"00000000")&TEXT(DEC2BIN(RIGHT(Source[@MgmtNetmask];LEN(Source[@MgmtNetmask])-FIND(".";Source[@MgmtNetmask];FIND(".";Source[@MgmtNetmask];FIND(".";Source[@MgmtNetmask])+1)+1)));"00000000");"1";""));"""";"     <NadClientTags tagName=""Location"" tagValue=""";SUBSTITUTE(TRIM(CLEAN(StripAccent(Source[@LocationName])));" (Beo)";"");"""/>""";"     <NadClientTags tagName=""LocationID"" tagValue=""[";Source[@LocationID];"]""/> </NadClient>")))

de query op sheet 3:

code:

=IF(ISBLANK(Source!A2);"";CONCAT("[";Source[@LocationID];"] ";SUBSTITUTE(TRIM(CLEAN(StripAccent(Source[@LocationName])));" (Beo)";"")))

Sheet 1: Source
Sheet 2: devices
Sheet 3: options

Die van sheet 3 is misschien het makkelijkst om te beoordelen wat ik fout doe?

Troubleshooting

1. Source tabel is eindigt op lijn 355
   formule in sheet 3 lijn 355 matched en checked Source!A355:
   
   code:
   

   =IF(ISBLANK(Source!A355);"";CONCAT("[";Source[@LocationID];"] ";SUBSTITUTE(TRIM(CLEAN(StripAccent(Source[@LocationName])));" (Beo)";"")))

2. CTRL-ALT-F5 pulled extra info in die source tabel die nu naar 370 lijnen groeit.
3. mm, hier is iets fout.. formule in sheet 3 lijn 355 gaat nu ineens in Source!A370 kijken?!
   
   code:
   

   =IF(ISBLANK(Source!A370);"";CONCAT("[";Source[@LocationID];"] ";SUBSTITUTE(TRIM(CLEAN(StripAccent(Source[@LocationName])));" (Beo)";"")))

Je eerste idee lijkt dus wel correct, maar als ik die Source!A2 aanpas naar Source[@LocationID], dan gaan de cellen van andere sheets inderdaad correct mee, MAAR wordt de overflow niet meer gevuld met blanco lijnen.


Source!A2: foutieve waardes, maar overflow is netjes blanco ("")


Source[@LocationID]: correcte waardes, maar callen 371 t/m 1000 geven nu value errors.


Iemand een idee hoe ik dat kan fixen?

[ Voor 9% gewijzigd door witchdoc op 25-11-2022 17:47 ]

[GRDavies75]

Waarom de verwijzing wordt aangepast is, omdat je feitelijk regels invoegt en dan past Excel de verwijzingen aan. Dat is standaard Excel-gedrag (misschien voelt het niet helemaal logisch aan, voor je gevoel voeg je regels aan het oude resultaat toe, maar blijkbaar als het op het nitty gritty mechanische aankomt)

Speculatief of misschien beter verwoord een guestimation is dat het @-teken naar de huidige regel kijkt, maar (mechanisch) waarschijnlijk het resultaat van Row() gebruikt. Dus op regel 371 en verder zeg je feitelijk Source[LocationID] van het 371ste record (en die is er niet).

Waarom maak je van je sheet 2 en sheet 3 ook gewoon tabellen? Mijn vermoeden is dat die ook automatisch meegroeien.
Het alternatief is als je toch al via Powerquery gegevens ophaalt, waarom dan ook niet de resultaten produceren via PQ?

[witchdoc]

GRDavies75 schreef op zaterdag 26 november 2022 @ 00:08:
Waarom maak je van je sheet 2 en sheet 3 ook gewoon tabellen? Mijn vermoeden is dat die ook automatisch meegroeien.
Het alternatief is als je toch al via Powerquery gegevens ophaalt, waarom dan ook niet de resultaten produceren via PQ?

Waarom geen tabellen voor sheet 2 en 3..
omdat mijn zoektocht naar een oplossing voor mijn issue (een .xml maken van records in een andere excel) niks zei over tabellen.

Beide sheets zijn net bedoeld om de values die ik uit de andere excel haal te cleanen en via concat en diverse andere functies om te zetten naar wat een 'domme' gebruiker kan copy/pasten in kladblok en weer importeren in andere software als .xml.
Telkens wordt alle data via concat etc tot een enkele tekst in de eerste cell herleid.


Een tabel klinkt dan ook niet logisch voor mij.. als ik hier fout in ben, hoor ik het graag trouwens.

Sheet 3 zou nog gaan, da's gewoon een lijst waar ik wat specifieke opmaak riond en tussen moet hebben.
Sheet 2 is echter anders.
1e lijn is nu de xml header
2e tot en met lijn 1000 is de xml body (met alle records en) OF de xml footer (als alle records verwerkt zijn) OF een blanco lijn indien de vorige record de xml footer of een blanco was.

dus sheet 2 zou ik dan lijn 1 buiten tabel moeten doen
dan tabel starten voor hoeveel records er zijn
en daarna achter de tabel nog de footer plaatsen. Kan dit uberhaupt?


Duidelijk is dat ik door de tabel verwijzing ipv de cel verwijzing te grbruiken tenminste mijn resultaat correct is. Dat ik dan value error ipv blanco cellen krijg.. uch.. gaat me mateloos frustreren maar is uiteindelijk ook weer niet het einde van de wereldin feite.
Gaat me wel nog weken irriteren dus ik ga toch nog even kijken of jouw tabel op sheet 2 mogelijk een oplossing is.
Jouw/jullie Input is uiteraard ook zeker welkom.

[GRDavies75]

witchdoc schreef op zondag 27 november 2022 @ 12:10:
[...]


Een tabel klinkt dan ook niet logisch voor mij.. als ik hier fout in ben, hoor ik het graag trouwens.

Sheet 3 zou nog gaan, da's gewoon een lijst waar ik wat specifieke opmaak riond en tussen moet hebben.
Sheet 2 is echter anders.
1e lijn is nu de xml header
2e tot en met lijn 1000 is de xml body (met alle records en) OF de xml footer (als alle records verwerkt zijn) OF een blanco lijn indien de vorige record de xml footer of een blanco was.

dus sheet 2 zou ik dan lijn 1 buiten tabel moeten doen
dan tabel starten voor hoeveel records er zijn
en daarna achter de tabel nog de footer plaatsen. Kan dit uberhaupt?

Duidelijk is dat ik door de tabel verwijzing ipv de cel verwijzing te grbruiken tenminste mijn resultaat correct is. Dat ik dan value error ipv blanco cellen krijg.. uch.. gaat me mateloos frustreren maar is uiteindelijk ook weer niet het einde van de wereldin feite.
Gaat me wel nog weken irriteren dus ik ga toch nog even kijken of jouw tabel op sheet 2 mogelijk een oplossing is.
Jouw/jullie Input is uiteraard ook zeker welkom.

Nee, het is geen moetje, eerder een smaak dingetje. De voorkeur gaat er bij mij uit vanwege consistentie ,automatische verwijzingcorrectie en kunnen werken met namen bij het gebruik van tabellen.

Maar je hele probleem klinkt alsof je zelf een xml parser oid aan het bouwen ben, volgens mij haal je heel veel werk op de hals en ik heb geen alternatief, maar de vraag is of Excel dan wel geschikt is. Maar ik denk wel dat je de hoop moet opgeven om 'perfecte' informatie aan de eindgebruiker kan opleveren, dus excelfoutmeldingen op regels die toch niet relevant zijn, moet je gewoon voor lief nemen bijvoorbeeld.

Succes ermee, xml heb ik weinig mee gewerkt (lees geproduceerd dan wel parsen e.d.), dus daar ga ik denk ik je niet veel verder mee kunnen helpen.