User Profile
OliverScheurich
Gold Contributor
Joined Oct 04, 2021
User Widgets
Recent Discussions
Re: Display Last Value in Column when certain criteria match
=INDEX($D$4:$K$50,LARGE(IF(INDEX($D$4:$K$50,, MATCH(TRUE,$D$1:$K$1&$D$2:$K$2&$D$3:$K$3=O2&P2&Q2,0))<>"",ROW($D$4:$D$50)-3),1), MATCH(TRUE,$D$1:$K$1&$D$2:$K$2&$D$3:$K$3=O2&P2&Q2,0)) This formula works in my sample file and in modern and legacy Excel. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2024. The formula is in cell R2 and filled down.Re: How to create a multi-tiered percentage-based bar chart?
I've applied Power Query to convert the blue table into the green result table. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table. The stacked bar chart is updated dynamically.Re: Index & Match Formula Not Working
=INDEX('Sheet 1'!C:C,MATCH(1,('Sheet 2'!A1='Sheet 1'!B:B)*('Sheet 2'!A2='Sheet 1'!A:A),0)) This formula works in my sample file and in modern and legacy Excel. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2024.Re: Different functions for each column in a subtotal
With modern Excel and with legacy Excel such as Excel 2013 you can use Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table. The data layout in the screenshot and in the attached file is for illustration. You can place the green result table in another worksheet as well.Re: Calculate hours using pivot table
An alternative could be Power Query that works in Excel 2013 and more recent versions of Excel. In the attached file you can add data to the blue dynamic tables. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table. The data layout in the screenshot and in the attached file is for illustration. You can place the green result table in another worksheet and the blue tables horizontally.Re: Need assistance to correct a formula
=IF(B2="Sun",IFERROR(SUM(INDEX($C$2:C2,LARGE(IF($B$2:B2="Mon",ROW($B$2:B2)-1),1)): INDEX($C$2:C2,LARGE(IF($B$2:B2="Sun",ROW($B$2:B2)-1),1))), SUM(INDEX($C$2:C2,1):INDEX($C$2:C2,LARGE(IF($B$2:B2="Sun",ROW($B$2:B2)-1),1)))),"") This formula works in my sample file in Excel 2013 and in modern Excel. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2024. The formula is in cell I2 and filled down.Re: Index and match mystery (for me that is...)
=INDEX(C21:C36,MATCH(1,(A21:A36<=D48)*(B21:B36>=D48),0)) This formula works in my sample file and in modern and legacy Excel such as Excel 2013. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2024.Re: Logical test for same text string existing anywhere in both ranges.
=IF(SUMPRODUCT(COUNTIFS(Tabelle1[@[Wri1]:[Wri4]],Tabelle1[@[Dir1]:[Dir3]]))-MMULT(MMULT(N(Tabelle1[@[Dir1]:[Dir3]]="(n/a)"),ROW($1:$3)^0),MMULT(N(Tabelle1[@[Wri1]:[Wri4]]="(n/a)"),ROW($1:$4)^0)),"Y","") This formula returns the expected result according to what i understand.Re: Excel
Thank you! I'm using Edge and Windows 11 as well and i didn't remember the gear menu item that is available for me as well. I was only looking for the menu (three dots) as it's shown when replying to a discussion. I've tried attaching a file several times to the discussion i've started but the attachment is removed unfortunately. EDIT: I was able to attach a small file ( ~11 KB) to my initial post of this discussion. However this isn't reliable since all other files were deleted. It seems that only small attachments aren't deleted no matter if i reply to a discussion or if i've started the discussion. Another EDIT: It seems that .xlsx files with up to 75 MB can be attached and aren't deleted and that all macro-enabled files .xlsm can't be attached from my PC.Re: Excel
If i've started the discussion i don't have a menu where i can choose to edit or delete the original message. The menu is only shown for the answerer. If i've replied to a discussion then there's a menu where i can edit the message and attach a file. To the discussion below i've replied yesterday and my attached file didn't stick. Today i've edited the message and attached the sample file and it's working for me.Re: pulling changing number of rows into another sheet, where you have categories in rows
=IFERROR(INDEX(A$2:A$32,SMALL(IF($C$2:$C$32="Shenzhen",ROW($C$2:$C$32)-1),ROW(A1))),"") Is this similar to what you are looking for? The formula is in cell I2 and dragged across range I2:N10 and works in all versions of Excel. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2024. The formula works across two sheets as well. For illustration i've made an example within one sheet. With recent versions of Excel you can use the FILTER function to simplify the solution.
