Cubevalue and spilled range

%3CLINGO-SUB%20id%3D%22lingo-sub-2906199%22%20slang%3D%22en-US%22%3ECubevalue%20and%20spilled%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2906199%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20CUBERANKEDMEMBER()%20calculation%20which%20I%20found%20will%20spill%20nicely%20over%20a%20CUBESETCOUNT()%20sequence.%20I%20now%20want%20to%20spill%20a%20CUBEVALUE()%20calculation%20based%20on%20the%20generated%20list.%20It%20just%20throws%20a%20non%20spilled%20single%20cell%20%23N%2FA.%20Should%20I%20expect%20the%20CUBEVALUE%20to%20spill%20or%20is%20this%20something%20that%20wont%20work%3F%20The%20formula%20works%20as%20a%20single%20cell%20so%20its%20all%20okay%20from%20that%20POV%2C%20its%20just%20the%20spillability%20of%20it%20that's%20not%20working.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2906199%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2908141%22%20slang%3D%22en-US%22%3ERe%3A%20Cubevalue%20and%20spilled%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2908141%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F319018%22%20target%3D%22_blank%22%3E%40samdthompson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20didn't%20test%20myself%2C%20but%20based%20on%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fblog.crossjoin.co.uk%2F2021%2F07%2F28%2Fexcel-cube-functions-dynamic-arrays-and-lambdas-part-1-getting-all-the-items-from-a-set%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EChris%20Webb's%20BI%20Blog%3A%20Excel%20Cube%20Functions%2C%20Dynamic%20Arrays%20And%20Lambdas%2C%20Part%201%3A%20Getting%20All%20The%20Items%20From%20A%20Set%20Chris%20Webb's%20BI%20Blog%20(crossjoin.co.uk)%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fblog.crossjoin.co.uk%2F2021%2F08%2F01%2Fexcel-cube-functions-dynamic-arrays-and-lambdas-part-2-returning-tables%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EChris%20Webb's%20BI%20Blog%3A%20Excel%20Cube%20Functions%2C%20Dynamic%20Arrays%20And%20Lambdas%2C%20Part%202%3A%20Returning%20Tables%20Chris%20Webb's%20BI%20Blog%20(crossjoin.co.uk)%3C%2FA%3E%3C%2FP%3E%0A%3CP%3Eand%20comments%20to%20above%2C%20you%20can't%20do%20spill%20with%20CUBEVALUE()%2C%20only%20using%20MAKEARRAY()%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a CUBERANKEDMEMBER() calculation which I found will spill nicely over a CUBESETCOUNT() sequence. I now want to spill a CUBEVALUE() calculation based on the generated list. It just throws a non spilled single cell #N/A. Should I expect the CUBEVALUE to spill or is this something that wont work? The formula works as a single cell so its all okay from that POV, its just the spillability of it that's not working.

2 Replies
Ah, cheers for that Sergei . I'll have to hold off then, I run insider channel for me but work limits me to the monthly enterprise so no makearray or lambda solution for this specific problem. I cant quite wrap my head around why I cant do it. I had got to the point that Chris mentions using cubesetcount to govern the spill range anyway so was surprised that cubemember wouldn't do the same. In my mind they are linked in function and form so should. I imagine that it just wasn't thought about when spill ranges were developed. Curses.