Waarde weergeven met verschuivende kolommen en rijen

[Ientje74]

In de bijlage heb ik een tabblad "Kubus Diensten" welke rechtstreeks maandelijks uit Cognos komt.
In de kolommen heb ik een aantal diensten staan met daaronder in de volgende regel per dienst, de uitgaven van de dienst zelf (kolomkop Leeg), de uitgaven van die dienst voor projecten (nr.) en de kolom Totaal.
In de rijen staan alleen de rekeningen vermeld waarvoor kosten zijn gemaakt.

In het tabblad Rapport moet ik per dienst de waardes vermelden welke komen uit de kolom Totaal van die betreffende dienst.
De moeilijkheidsgraad voor mij is dat dus de kolom Totaal verspringt in de kubus, doordat er in een (nieuwe) periode opeens kosten worden geboekt voor een (nieuw) project.
Of dat er op rekeningen worden geboekt, welke voorheen niet werden gebruikt.

In het tabblad Kubus Diensten kan ik geen aanpassingen maken, omdat ik dit tabblad elke keer ga verversen met nieuwe gegevens.

Met welke formule krijg ik nu toch de kosten voor huisv 4042 huren van dienst C in cel E5 (antwoord moet zijn de inhoud uit cel J8 op tabblad kubus diensten 16.750)?
Ik loop er elke keer tegenaan dat steeds de waardes uit de eerste kolom van een dienst worden weergegeven i.p.v. de cijfers uit de kolom totaal.

 

[VenA]

Als iets periodiek uit een systeem komt dan kan het ook periodiek in het gewenste resultaat gepresenteerd worden. Wat jij een kubus noemt is niets anders dan een draaitabel met een 'tussenlaag' waarin de eventuele projectnummers staan. Laat deze laag weghalen en je bent klaar. Is een eenmalige aanpassing en je hebt geen enkele formule nodig. Als het perse met formules moet dan kan je eens kijken naar INDEX(), MATCH() en OFFSET().

 

[Ientje74]

Helaas VenA,

Bedankt voor het meedenken, heb ik aan gedacht maar is helaas voor mij niet de oplossing.
De projectkolommen heb ik in hetzelfde bestand nog nodig voor een andere view op de rapportage (en analysedoeleinden).
Maar de overige bewerkingen heb ik weggelaten in dit vereenvoudigde bestand.
En ik zou alle kubussen van alle diensten apart kunnen downloaden, maar dan krijg ik in een rapportage bestand zo'n 52 tabbladen met gegevens.
Dat is wat ik juist wilde voorkomen.

Wellicht heb je (of iemand anders) nog een andere oplossing voor mij?
Alvast hartelijk dank

 

[popipipo]

Voldoet deze oplossing?

 

[VenA]

Dan vraag je toch al dan niet geaggregeerde data op in tabelvorm en maak je je eigen rapportages dmv draaitabellen. Waarom altijd moeilijk als de onderliggende data aanwezig is?:d

Al gekeken naar de genoemde functies?

 

[HSV]

Misschien doe ik het morgen geheel anders, maar voor nu enig resultaat.

Sub hsv()
Dim sv, i As Long, j As Long, mtr, mtc
With Sheets("kubus diensten")
sv = Sheets("rapport").Range("B3:H21")
For i = 3 To UBound(sv)
  For j = 2 To UBound(sv, 2)
   mtr = Application.Match(sv(i, 1), .Cells(8, 1).CurrentRegion.Columns(3), 0)
    If IsNumeric(mtr) Then
      mtc = Application.Match(sv(1, j), .Cells(8, 1).CurrentRegion.Rows(6), 0)
    If IsNumeric(mtc) Then sv(i, j) = .Cells(6, mtc).MergeArea.Cells(1, .Cells(6, mtc).MergeArea.Cells.Count).Offset(mtr - 6)
    End If
   Next j
  Next i
End With
Sheets("rapport").Range("B3:H21") = sv
End Sub

 

[Ientje74]

Voldoet deze oplossing?

Hallo Popipipo,

Helaas met jouw formule kijkt hij nu naar de tweede kolom van een dienst.
Daarmee kijkt hij bij dienst C, E en F alsnog naar de verkeerde kolommen.
Maar toch bedankt voor je reactie, mogelijk dat ik hier toch iets mee kan.

 

[Ientje74]

Dan vraag je toch al dan niet geaggregeerde data op in tabelvorm en maak je je eigen rapportages dmv draaitabellen. Waarom altijd moeilijk als de onderliggende data aanwezig is?:d

Al gekeken naar de genoemde functies?

Hallo VenA,
Kun je mij misschien een voorbeeld geven in het voorbeeld Excelbestand?
Wellicht dat ik jouw oplossing dan beter begrijp.

Zelf zit ik nog te denken aan een oplossing met verschuiven index en vergelijken.
Waarbij ik eerst de startkolom bepaal door te zoeken naar de dienst.
En vervolgens in de regel daaronder (regel 7 in het voorbeeld) met index en vergelijken zoeken naar de kolom waarboven Totaal staat.
Mogelijk dat iemand hier iets mee kan?

 

[Ientje74]

Misschien doe ik het morgen geheel anders, maar voor nu enig resultaat.

Hallo HSV,
Helaas heb ik geen enkel idee hoe ik dit in Excel kan opslaan.
Is dit VBA of zoiets? Ik heb daar nog geen kaas van gegeten...
Wellicht dat je jou oplossing in mijn voorbeeld bestandje wilt plakken.
Mogelijk dat ik het dan weer kan kopieren in het originele bestand.

 

[VenA]

bv

=ALS.FOUT(INDEX('Kubus Diensten'!$D$8:$X$18;VERGELIJKEN($B5;'Kubus Diensten'!$C$8:$C$18;0);VERGELIJKEN("Totaal";VERSCHUIVING('Kubus Diensten'!$C$7;;VERGELIJKEN(C$3;'Kubus Diensten'!$D$6:$X$6;0);;5);0)+VERGELIJKEN(C$3;'Kubus Diensten'!$D$6:$X$6;0)-1);"")

 

[Ientje74]

Hartelijk dank VenA,

Deze formule doet exact wat ik wil!!! Helemaal blij mee.:thumb:
Ondertussen heb ik de formule toegepast in het oorspronkelijke bestand en de uitkomst is helemaal hetgeen ik verwacht.
Super!!!!
Op een later moment ga ik de formule nog eens helemaal voor mezelf opsplitsen in kleine delen, zodat ik precies zie wat waar gebeurd, maar daar ontbreekt mij nu de tijd voor.
Maar het werkt en dat is voor nu het belangrijkste.

Iedereen hartelijk dank voor de reacties.