User Profile
Rodrigo_
Steel Contributor
Joined Nov 10, 2022
User Widgets
Recent Discussions
Re: Help with formula in Exc
As long as you put it right it should work by now.. Put the sheet name on cell N1, then the formula must have a bracket {=formula} at the beginning and at the end, like this: {=IFERROR(INDEX(Master!$A$2:$H$100,SMALL(IF(Master!$B$2:$B$100=$N$1,ROW(Master!$B$2:$B$100)-ROW(Master!$B$2)+1),ROW()-ROW($A$2)+1),MATCH(A$1,Master!$A$1:$H$1,0)),"")} then drag the formula across the table Master Sheet: Lead Sheet: Install Sheet:Re: Help with formula in Exc
Yeah, did not consider if you're using an older version. Do this instead: Type manually the sheet name on the cell of that sheet wherever you like, in my case it's on cell N1 of Lead sheet, Use this array formula on cell A2 (double click the cell to enter in Edit mode) of Lead sheet: =IFERROR(INDEX(Master!$A$2:$H$100,SMALL(IF(Master!$B$2:$B$100=$N$1,ROW(Master!$B$2:$B$100)-ROW(Master!$B$2)+1),ROW()-ROW($A$2)+1),MATCH(A$1,Master!$A$1:$H$1,0)),"") Upon putting the formula on the cell press CTRL + SHIFT + ENTER, instead of pressing enter. Then drag that formula to column H and then drag it again to bottom of your table.Re: Help with formula in Exc
The "Type" column from master sheet must be match with the other sheet's name, Then, use mid and cell function to extract your sheet names, and use filter to return dynamically the data from master sheet, and will update whenever you input more data on the master sheet. Put this formula to cell A2 of the other sheets. =FILTER(Master!A2:H15,Master!B2:B15=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),"No data")Re: Pivot many to many relationships
Late reply, see the sample file here> https://filetransfer.io/data-package/7YLnIWr5#link Content: (EST1) (EST2) Formula: =LET( header,{"Date","Gain","Expense"}, dateCol,SORT( UNIQUE( VSTACK( FILTER(Table1[date], Table1[est]=L1, ""), FILTER(Table2[date], Table2[est]=L1, "") ) ) ), gainCol,IFERROR(VLOOKUP(dateCol,Table1,3,FALSE),0), totalGain,SUM(gainCol), expenseCol,IFERROR(VLOOKUP(dateCol,Table2,3,FALSE),0), totalExpense,SUM(expenseCol), gainexpense, (totalGain-totalExpense), totalrow, HSTACK("",totalGain,totalExpense,gainexpense), combinedData,HSTACK(dateCol,gainCol,expenseCol), IFERROR(VSTACK(header,combinedData,totalrow),"") )Re: Help with SUMIFS formula
Or use this sumproduct like this: =SUMPRODUCT((A2:A100=$H$2)*(C2:C100="Incremental")*(F2:F100="Y")*(D2:D100<=B2:B100+90)*( (D2:D100<B2:B100)+(D2:D100>B2:B100) )*(E2:E100)) to be more flexible, and ensure the date in column D is within 90days before or after in column B by row.
