SUM WITH INDEX MATCH ERROR
so i want to sum all of possibility based on criteria to search value that i've adjust. the goal is to create dynamic formula that dont need to adjust per column (that's why i adjust the range formula to sum from D7:O11), so i can use it by changing the criteria only. can you guys help me on this error?
Power Query- filter Table by a keyword list, partial match
Hello, I have been looking for a solution for a while now to filter a table column with text (approx. 2000 rows) using a list (approx. 50) of keywords in Power Query. The list should be editable by the user in Excel and also contains partial words that I want to identify in the table. Texts in the table column are usually companies with different spellings, e.g. Amazon Germany, Amzn US, Bosch, Bsh, Glashuette, glashutte, Search terms: amazon, bsh, bosch, glash I have already found solutions for exact matches. Thank you very much for your help and the great work of the community. Best regards Oliver
Conditional Formatting for Spilled Arrays
I have recently composed a bunch of functions to make using conditional formatting with spilled arrays more manageable. You can check out those formulas at the end of my post. I am curious if anyone has a different way to accomplish this goal. I will briefly describe the overall method and give a use case. In essence, to use conditional formatting you need to 1)specify a range of cells where the rule will be conditionally applied (the "Conditional Formatting Range") and 2) specify the condition for application. In many cases, the formatting is conditioned upon the placement of the cell/column/row/etc. relative to the spilled array which generated it (the "Spilled Array Range"). The problem (so far as I know) is that Excel's conditional formatting manager does not allow dynamic range references to specify the Conditional Formatting Range, so there is a fundamental disconnect between the Conditional Formatting Range and the possible range of the Spilled Array Range you want to format. It occurred to me that one possible solution to this problem is to A) set the Conditional Formatting Range to a defined range that is certain to be larger than your Spilled Array Range and B) create conditions that inherently self limit themselves to the Spilled Array Range regardless of the size of your Conditional Formatting Range. Mindful of the perils of using volatile functions, I wanted to create a solution that avoids turning values (string references primarily) into range references via functions like indirect and offset. That meant that I was limited to selecting a subrange from a larger range and constructing a dynamic range by way of a pattern like "index(array1, starting_row, starting_col):index(array2, ending_row, ending_col)" where the first index returns a single cell range reference to the first cell of the target range and the second index returns a single cell range reference to the last cell of the target range. This idea sort of distills down to the last few lines of the function I created: result, IF( NOT(base_in_container), NA(), LET( start_ref, INDEX(container_array, idx_base_in_container_first_row, idx_base_in_container_first_col), end_ref, INDEX(container_array, idx_base_in_container_last_row, idx_base_in_container_last_col), start_ref:end_ref ) ), If you name the range created in this way, you can simply enter "=named_range" into the applies to field in the conditional formatting interface and Excel will, at the time you apply the rule, resolve that name into whatever the then current range reference is of named_range, e.g. "A$7$:$G$52". Assuming your spilled array is contained in that range, your conditional formatting rule will apply to the spilled array. I call this larger containing range something like "CF_OVERSIZED_ARRAY." Once CF_OVERSIZED_ARRAY is set to a rule, you never have to change the Conditional Formatting Range again unless your spilled array size possibly exceeds whatever range you initially selected. (For instance, if your oversized array is 50 rows and 12 columns you need not change its size unless your spilled array suddenly grows from say 7 columns to 14). The elegance of this method over directly hardcoding the value is that if you have many conditional formatting rules, by entering "=named_range" for the applies to range in each rule, you both eliminate the possibility of inconsistent application ranges and have a visual confirmation when entering each rule that the value you are setting is exactly what you intend (rather than something like "=worksheet1!$A$23:$H$79"). Furthermore, by programmatically defining the oversized array, you can make it as "small" as reasonable, thereby avoiding having conditional formatting apply to many unused cells (such as the whole worksheet). At this point, the next computation minimization occurs - a guard clause for each condition is specified such that any cell in CF_OVERSIZED_ARRAY outside of the Spilled Array Range immediately returns a false and no further condition checking is performed. The general formula for checking if a cell is within the Spilled Array Range is as follows along with an example of a guard clause: is_within_array = LAMBDA( range, LET( start_row, ROW(TAKE(range, 1, 1)), start_col, COLUMN(TAKE(range, 1, 1)), AND( ROW() >= start_row, ROW() < start_row + ROWS(range), COLUMN() >= start_col, COLUMN() < start_col + COLUMNS(range) ) ) ); is_in_row_of_array = LAMBDA( range, start_index, [end_index], IF( NOT(is_within_array(range)), FALSE, LET( Now that the basic structure has been established, a number of workhorse functions are established: is_in_row_of_array - cell is within a specified row or range of rows in the spilled array such as "is in row 1" or "is in rows 4 through 6", is_in_col_of_array - cell is within a specified column or range of columns in the spilled array such as "is in column 1" or "is in columns 4 through 6", is_in_slice_of_array - cell is with a specified contiguous portion of the spilled array such as "is between rows 5 through 7 and columns 2 through 12" is_in_interval_of_array - cell is in set of every N rows or N columns such as "is one of every other row" or "is one of every third column" is_in_recurring_band_of_rows - cell is in a recurring grouping of rows such as "is 2nd and 3rd row of every group of 4 rows" is_in_recurring_band_of_cols - cell is in a recurring grouping of columns such as "is last column of every group of 7 columns" Here is an example function: is_in_col_of_array = LAMBDA( range, start_index, [end_index], IF( NOT(is_within_array(range)), FALSE, LET( num_cols, COLUMNS(range), current_col, COLUMN() - COLUMN(TAKE(range, 1, 1)) + 1, start_resolved, IF(start_index > 0, start_index, num_cols + start_index + 1), end_resolved, IF( ISOMITTED(end_index), start_resolved, IF( end_index > 0, end_index, num_cols + end_index + 1 ) ), final_start, MIN(start_resolved, end_resolved), final_end, MAX(start_resolved, end_resolved), AND(current_col >= final_start, current_col <= final_end) ) ) ); On top of the basic structure, convenience functions are created - the names of which should indicate how they solve common formatting needs: alternate_cols alternate_rows is_in_first_row_of_recurring_band_of_rows is_in_last_row_of_recurring_band_of_rows is_in_first_col_of_recurring_band_of_cols is_in_last_col_of_recurring_band_of_cols is_in_header_of_col is_in_last_row_of_col is_in_first_data_row_of_col is_between_first_and_last_row_of_col One major benefit flowing from this design is that these conditions are composable, e.g. alternate_cols * is_in_header_of_col would give you alternating formatting on the headers of a spilled array, such as an array with empty columns between each of the substantive columns. While I do not promise that the following formulas are perfect, what I can say is that they presently permit me to write rules like this: =cf.is_in_row_of_array(ins_rep.dynamic_array, 1)*cf.alternate_cols(ins_rep.dynamic_array,FALSE) =cf.is_in_first_data_row_of_col(ins_rep.dynamic_array,9) =cf.is_between_first_and_last_row_of_col(ins_rep.dynamic_array, 9,TRUE) =cf.is_in_last_row_of_col(ins_rep.dynamic_array,9) Which effectively gives me a rule for all headers (shading with underlining and bold), the ability to set the first data cell in column 9 to be a dollar format, the last cell in column 9 to be a dollar format with a top border, and all of the other cells in column 9 to be simple integers. So something like this (imagine the first cell is shaded as described and the last cell has a top border) is what I get for column 9 of a dynamically generated and spilled array: Fees $175 175 175 175 175 175 175 175 175 $1,575 Please let me know if you have found any other ways to address the problem of the Conditional Formatting Range being disconnected from the Spilled Array Range. I'm happy to answer any questions about my method or formulas, so feel free to ask. I'd also appreciate any feedback/suggestions/improvements on my idea/formulas. Here are the complete formulas (I have them saved within Excel Labs Advanced formula environment in separate modules) // _range module create_expanded_from_subset_of_containing_range = LAMBDA( base_array, desired_height, container_array, [desired_width], LET( req_width, IF(ISOMITTED(desired_width), COLUMNS(base_array), desired_width), /* --- Resolve anchors (references, not values) --- */ base_array_first_cell, INDEX(base_array, 1, 1), base_array_first_row, ROW(base_array_first_cell), base_array_first_col, COLUMN(base_array_first_cell), container_array_first_cell, INDEX(container_array, 1, 1), container_array_first_row, ROW(container_array_first_cell), container_array_first_col, COLUMN(container_array_first_cell), container_array_rows, rows(container_array), container_array_cols, columns(container_array), idx_base_in_container_first_row, base_array_first_row - container_array_first_row +1, idx_base_in_container_first_col, base_array_first_col - container_array_first_col +1, idx_base_in_container_last_row, idx_base_in_container_first_row + desired_height - 1, idx_base_in_container_last_col, idx_base_in_container_first_col + req_width - 1, base_in_container, and( idx_base_in_container_first_row > 0, idx_base_in_container_first_row <= idx_base_in_container_last_row, idx_base_in_container_last_row <= container_array_rows, idx_base_in_container_first_col > 0, idx_base_in_container_first_col <= idx_base_in_container_last_col, idx_base_in_container_last_col <= container_array_cols ), result, IF( NOT(base_in_container), NA(), LET( start_ref, INDEX(container_array, idx_base_in_container_first_row, idx_base_in_container_first_col), end_ref, INDEX(container_array, idx_base_in_container_last_row, idx_base_in_container_last_col), start_ref:end_ref ) ), result ) ); //cf module is_within_array = LAMBDA( range, LET( start_row, ROW(TAKE(range, 1, 1)), start_col, COLUMN(TAKE(range, 1, 1)), AND( ROW() >= start_row, ROW() < start_row + ROWS(range), COLUMN() >= start_col, COLUMN() < start_col + COLUMNS(range) ) ) ); is_in_row_of_array = LAMBDA( range, start_index, [end_index], IF( NOT(is_within_array(range)), FALSE, LET( num_rows, ROWS(range), current_row, ROW() - ROW(TAKE(range, 1, 1)) + 1, start_resolved, IF(start_index > 0, start_index, num_rows + start_index + 1), end_resolved, IF(ISOMITTED(end_index), start_resolved, IF(end_index > 0, end_index, num_rows + end_index + 1)), final_start, MIN(start_resolved, end_resolved), final_end, MAX(start_resolved, end_resolved), AND(current_row >= final_start, current_row <= final_end) ) ) ); is_in_col_of_array = LAMBDA( range, start_index, [end_index], IF( NOT(is_within_array(range)), FALSE, LET( num_cols, COLUMNS(range), current_col, COLUMN() - COLUMN(TAKE(range, 1, 1)) + 1, start_resolved, IF(start_index > 0, start_index, num_cols + start_index + 1), end_resolved, IF(ISOMITTED(end_index), start_resolved, IF(end_index > 0, end_index, num_cols + end_index + 1)), final_start, MIN(start_resolved, end_resolved), final_end, MAX(start_resolved, end_resolved), AND(current_col >= final_start, current_col <= final_end) ) ) ); is_in_slice_of_array = LAMBDA( range, start_row, start_col, [end_row], [end_col], [include_slice_neg1_to_exclude], if( not(is_within_array(range)), FALSE, LET( final_end_row, IF(ISOMITTED(end_row), start_row, end_row), final_end_col, IF(ISOMITTED(end_col), start_col, end_col), row_match, is_in_row_of_array(range, start_row, final_end_row), col_match, is_in_col_of_array(range, start_col, final_end_col), selection, AND(row_match, col_match), mode, IF(ISOMITTED(include_slice_neg1_to_exclude), 1, include_slice_neg1_to_exclude), IF(mode = -1, NOT(selection), selection) ) ) ); is_in_interval_of_array = LAMBDA( range, row_interval, col_interval, [start_at_row], [start_at_col], [include_interval_neg1_to_exclude], if( not(is_within_array(range)), FALSE, LET( row_idx, ROW() - ROW(TAKE(range, 1, 1)) + 1, col_idx, COLUMN() - COLUMN(TAKE(range, 1, 1)) + 1, start_row, IF(ISOMITTED(start_at_row), 1, start_at_row), start_col, IF(ISOMITTED(start_at_col), 1, start_at_col), row_match, IF(row_interval <= 1, TRUE, MOD(row_idx - start_row, row_interval) = 0), col_match, IF(col_interval <= 1, TRUE, MOD(col_idx - start_col, col_interval) = 0), selection, AND(row_match, col_match), mode, IF(ISOMITTED(include_interval_neg1_to_exclude), 1, include_interval_neg1_to_exclude), IF(mode = -1, NOT(selection), selection) ) ) ); alternate_cols = lambda( array, [start_with_even_df_TRUE], is_in_interval_of_array(array,1,2,,1+if(isomitted(start_with_even_df_TRUE),1,start_with_even_df_TRUE)) ); alternate_rows = lambda( array, [start_with_even_df_TRUE], is_in_interval_of_array(array,2,1,1+if(isomitted(start_with_even_df_TRUE),1,start_with_even_df_TRUE)) ); is_in_recurring_band_of_rows = LAMBDA( range, rows_in_pattern, first_row_in_band, [band_thickness], [include_selected_df_TRUE], IF( NOT(is_within_array(range)), FALSE, LET( relative_row, ROW() - ROW(TAKE(range, 1, 1)), row_in_pattern, MOD(relative_row, rows_in_pattern) + 1, actual_thickness, IF(ISOMITTED(band_thickness), 1, band_thickness), is_in_band, AND( row_in_pattern >= first_row_in_band, row_in_pattern <= (first_row_in_band + actual_thickness - 1) ), include_mode, IF(ISOMITTED(include_selected_df_TRUE), TRUE, include_selected_df_TRUE), IF(include_mode, is_in_band, NOT(is_in_band)) ) ) ); is_in_first_row_of_recurring_band_of_rows = lambda( range, rows_in_pattern, [include_selected_df_TRUE], is_in_recurring_band_of_rows(range, rows_in_pattern, 1, 1, include_selected_df_TRUE) ); is_in_last_row_of_recurring_band_of_rows = lambda( range, rows_in_pattern, [include_selected_df_TRUE], is_in_recurring_band_of_rows(range, rows_in_pattern, rows_in_pattern, 1, include_selected_df_TRUE) ); is_in_recurring_band_of_cols = LAMBDA( range, cols_in_pattern, first_col_in_band, [band_thickness], [include_selected_df_TRUE], IF( NOT(is_within_array(range)), FALSE, LET( relative_col, COLUMN() - COLUMN(TAKE(range, 1, 1)), col_in_pattern, MOD(relative_col, cols_in_pattern) + 1, actual_thickness, IF(ISOMITTED(band_thickness), 1, band_thickness), is_in_band, AND( col_in_pattern >= first_col_in_band, col_in_pattern <= (first_col_in_band + actual_thickness - 1) ), include_mode, IF(ISOMITTED(include_selected_df_TRUE), TRUE, include_selected_df_TRUE), IF(include_mode, is_in_band, NOT(is_in_band)) ) ) ); is_in_first_col_of_recurring_band_of_cols = LAMBDA( range, cols_in_pattern, [include_selected_df_TRUE], is_in_recurring_band_of_cols(range, cols_in_pattern, 1, 1, include_selected_df_TRUE) ); is_in_last_col_of_recurring_band_of_cols = LAMBDA( range, cols_in_pattern, [include_selected_df_TRUE], is_in_recurring_band_of_cols(range, cols_in_pattern, cols_in_pattern, 1, include_selected_df_TRUE) ); is_in_header_of_col = LAMBDA( array, [column_no], IF(ISOMITTED(column_no), is_in_row_of_array(array, 1), is_in_slice_of_array(array, 1, column_no) ) ); is_in_last_row_of_col = LAMBDA( array, [column_no], IF(ISOMITTED(column_no), is_in_row_of_array(array, -1), is_in_slice_of_array(array, -1, column_no) ) ); is_in_first_data_row_of_col = LAMBDA( array, [column_no], IF(ISOMITTED(column_no), is_in_row_of_array(array, 2), is_in_slice_of_array(array, 2, column_no) ) ); is_between_first_and_last_row_of_col = lambda( array, [column_no], [exclude_first_data_row_df_FALSE], is_in_slice_of_array( array, if(isomitted(exclude_first_data_row_df_FALSE),FALSE,exclude_first_data_row_df_FALSE)+2, if(isomitted(column_no),1,column_no), -2, if(isomitted(column_no),-1,column_no), ) );
how do I disable Date and Time by ankurjain
I am using Office 2019 32 bit desktop version on a Windows 10 Pro 64 bit machine. I was looking for a date picker and found a date picker in the Microsoft store. I added it but now want to disable it. I have tried using the developer add-ins area but it doesn't appear as an excel add in or com add in. Yet whenever I start Excel a box on the right side of my sheet shows New Office Add-In with this add in info. I have clicked on trust this add in to see if I did that it would appear in my add in list so I could then try to delete it, but when I do that I get an error message saying Error 403 - This web app is stopped. The web app you have attempted to reach is currently stopped and does not accept any requests. Please try to reload the page or visit it again soon. If you are the web app administrator, please find the common 403 error scenarios and resolution https://go.microsoft.com/fwlink/?linkid=2095007. For further troubleshooting tools and recommendations, please visit https://portal.azure.com/.
Finding a "2 cell" name in anther sheet and copying data
Hello- I'm looking for a formula to enter in column D of the Main Sheet. I would like to find the "Total" in sheet: 2023 for the name in cells A&B, and copy them into the Main Sheet. The names are in a different order in each sheet. I've tried XLOOKUP, but couldn't make it work. Any ideas?
Excel Paste as Image Issue
Hello, apologies if this is not the best place to post this. Whenever I special paste, as a picture, it shows my selected excel cells as a tiny top left section of the whole pasted object. I then have to crop the “image” to only show the actual section of excel cells and remove the clear components. Any assistance with this would be greatly appreciated as I havent had much luck finding this same problem online. I experience it whenever I paste from excel to powerpoint, excel to excel, or excel to powerpoint, but only as an image. Please let me know if this is not allowed on here!
No puedo abrir mi Excel, ningún archivo.
No puedo abrir ningún archivo de excel, se queda cargando pero no abre. Solo puedo abrirlo en modo seguro. He quitado los complementos y aún sigue sin abrir. He desinstalado e instaldo Office 365 y aún sigue sin abrir, se queda cargando. Espero que alguien pueda tener la solución a este incoveniente. Los demas programas me abren (Word, PPT...)
