Forum Discussion

Fali Chen's avatar
Fali Chen
Copper Contributor
May 22, 2018

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;B...
Excel Online
Formulas & Functions
Fali Chen's avatar
Fali Chen
Copper Contributor
May 23, 2018
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?
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 28, 2018

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.

Resources