Help with a possible filter formula (no VBA no macros)

[GeisonGDC]

Hello and thank you in advance. 

I want to know if is possible to change the part of the formula highlighted here so that instead of Table1[Validation] (which i chose for the example) the column that is in the formula changes based on the U4 column selection. I have 0 experience with macros and VBA, so i apologize in advance if not possible.

[#2 · Accepted solution]

@GeisonGDC 

It looks like you're trying to create a dynamic formula that references a column based on the selection in cell U4. In Excel formulas alone (without VBA or macros), you can achieve this using the INDIRECT function. However, keep in mind that the INDIRECT function is volatile, meaning it can impact performance, especially in large datasets.

Here's a basic example of how you might modify your formula (i can not see your picture correctly on my pc):

Assuming your current formula is something like:

=SUMIFS(Table1[Validation], Table1[Category], U4)

You can make it dynamic based on the value in U4 using INDIRECT:

=SUMIFS(INDIRECT("Table1[" & U4 & "]"), Table1[Category], U4)

This assumes that the value in U4 matches the column header in Table1. The INDIRECT function is used to dynamically reference the column based on the value in U4.

However, note that this approach has some limitations and potential issues:

1. Volatile Function: INDIRECT is a volatile function, meaning it recalculates every time anything changes in the workbook. This can impact performance, especially in large datasets.
2. Error Handling: If the value in U4 doesn't match any column header in Table1, it will result in an error. You might want to add some error-checking or validation to handle such cases.
3. Refactoring with VBA: For more robust and flexible solutions, especially if dealing with larger datasets or dynamic column selections, VBA (macros) would be a more suitable option.

If you're interested in a VBA solution, let me know, and I can provide some guidance on how to achieve this with a simple macro. The text was revised with the AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

[#3]

Thanks a lot, it totally worked!

[#4]

@GeisonGDC Another option is to use the XLOOKUP function to return the desired table column based on the value in cell U4. For example:

=XLOOKUP(U4, Table1[#Headers], Table1)

In the formula above, the lookup_value is cell U4, the lookup_array is the header row of Table1, and the return_array is Table1 (the entire data range). If the value in cell U4 matches with one of the table headers, the entire corresponding column is returned.

This method can then be combined with the FILTER function to return only the records where the selected column matches the value in cell Y4:

=FILTER(Table1[[name]:[Contact ID]], XLOOKUP(U4, Table1[#Headers], Table1)=Y4, "No records found")