Forum Discussion
Formulas wile working with few sheets information
#SPILL! error
if it's not enough space (range wit blank cells) to expand the data by formula.
If we check next row(s)
there are some formulas or data in the next cells. Select and clean (Del) them to empty the space for the dynamic array, it works now
djclements and SergeiBaklan , Thank you for your reply, yes it is ment to be A3:D3, in my sheet their is ment to be a space "WT C" "WT P" is having this space ok or should I try dropping it, I have attached a striped back sheet with both fourmla in it, djclements as you may see I use your formula and the cell just looks blank, must be ok tho as its not returning an error, with SergeiBaklan I have also used your formula, thing with this one is it seams to count down column A till it finds what it looking for and then when transferring it over it uses the same amount it counted down to input answer, any other help with them,
It's better to restructure data a bit, but it depends on its logic. In any case you may start with dynamic formulae for intermediate calculations as well.
Tube Size could be return for entire column at once
=LET(
nRows, ROWS( TOCOL($B:$B,1) ),
size, B2:INDEX(B:B, nRows+1),
colour, C2:INDEX(C:C, nRows+1),
TRIM( size
& IF( ISNUMBER(SEARCH("CARDBOARD",colour))*
(
(size="WT")+
(size="NT")+
(size="ET")+
(size="MT")
)," C ", " P ")
& IF( ISNUMBER(SEARCH(" NG",colour))*
(
(size="WT")+
(size="NT")
), "NG","")
) )repeat report data to Carry Over Page with
=LET(
nRows, ROWS('Past Buggy Report'!$A$2# ),
CHOOSECOLS(
'Past Buggy Report'!$A$2:INDEX('Past Buggy Report'!$Q:$Q, nRows+1 ),
XMATCH({"Tube Size","Tube Colour","Stock No","Count"},
'Past Buggy Report'!$A$1:$Q$1)
) )when filter will be as
=IFERROR( FILTER($A$3#, CHOOSECOLS($A$3#,1)=$I$1), "" )Similar with other formulae.
SergeiBaklan thank you for your reply but I'm having bother, I have tried using your fourmla as you have and each time I keep getting "Spill" even tho I am copping your fourmla exactly and iv compared both and are both the same. Any ideas. Bellow I have attached the sheet,
#SPILL! error
if it's not enough space (range wit blank cells) to expand the data by formula.
If we check next row(s)
there are some formulas or data in the next cells. Select and clean (Del) them to empty the space for the dynamic array, it works now
- Sorry for the delay but thank you so much for your patience and help great help thank you.