Forum Discussion
Formula help
Hi Guys,
So I was able to extract rows of data from 1 sheet with the following formula:
=REPLACE(CELL("filename";A1);1;SEARCH("]";CELL("filename";A1));"")
=COUNTIFS('L Costs'!I3:I30000;B1)+COUNTIFS('D Costs'!I2:I30000;B1)
and an aggregate function
My Intention is to extract rows of data from 2 or even more Sheets by adding them up but it returned to #Value !
Could you please help me to modify the formula in order to extract all the rows in multiple Sheets.
Many thanks in adv.
Hi Fali,
In your sample formula returns #NUM!, the reason is it can't find no one cost center in the table. In your 56 sheet cost center picked-up from sheet name is text "56", and in the table that is number 56.
You shall use in both cases or numbers, or texts.
- I add +0 in the end to convert to number. Then it’s picking up numbers.
Do you have any idea how I can extract the rows from 2 sheets?Hi Fali,
As variant and if expand a bit your formula
- add number of records which match for each table like
=COUNTIFS(R_Costs[Cost Center],B1)
in C2 for R_Costs and in B2 for L_Costs
Array formula for invoices numbers will be
=IF(ROWS(A$3:A3)>$B$2, IF(ROWS(A$3:A3)>$B$2+$C$2,"", INDEX(R_Costs[Invoice Number],AGGREGATE(15,6,(ROW(R_Costs[Invoice Number])-ROW(R_Costs[#Headers]))/(R_Costs[[Cost Center]:[Cost Center]]=$B$1),ROWS(A$3:A3)-$B$2))), INDEX(L_Costs[Invoice Number],AGGREGATE(15,6,(ROW(L_Costs[Invoice Number])-ROW(L_Costs[#Headers]))/(L_Costs[[Cost Center]:[Cost Center]]=$B$1),ROWS(A$3:A3))))
similar for other columns.
Please see that attached.
