Formula help

[Fali Chen]

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.

[#2]

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. 

[#3]

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?

[#4]

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.