User Profile
Riny_van_Eekelen
Platinum Contributor
Joined 7 years ago
User Widgets
Recent Discussions
Re: How to filter visible cells by formula after applying filter to a dataset.
I would add a helper column that check if the row is visible or not and then a regular FILTER formula as demonstrated in the picture below. In your real model, move the FILTER formula away from the data table as it will otherwise be partially hidden when you apply a filter to the data table.Re: Bank Reconciliations
I'm guessing that you are NOT working with a structured table. Let's assume that you have a Total formula =SUM(G1:G49) in G50. Then you can't insert row 50 and expand regular SUM function automatically to include the new row 50. One way would be allow for a growing sum range by including an empty row. So, in the above, example, row 49 would be empty and then you insert a row above it. Then the sum range becomes G1:G50. A formula solution would be to insert an OFFSET in the SUM function like this: =SUM(G1:OFFSET(G50,-1,0)) Now you can insert a row above row 50 and the OFFSET will make sum range to end one row above (the -1) the row where the formula sits. Alternatively, use a structured table with a Total row. That will allow you to insert a row below the last row with data and the sum will update automatically.Re: max corresponding to a value which may be in 1 or multiple sets
That formula should work, provided that your objective to find an exact match of -917 in both ranges for column D. There is no match in both you'll get #N/A. Use the optional argument [march_mode] to indicate your intentions is an exact match is not found as illustrated in the screenshot below.Re: F/S presentation
jmarin1949 Just seeing you post now. Three days after you posted it. From what you describe you want something like this: Then use a custom format like this: # ##0_)"$";(# ##0)"$" I don't know how you want to show negatives. I assumed within parentheses. You can always change that of course.Re: Long data copied to wide data
I assume you open the XLSX file in Numbers and that you do not have Excel on your Mac. I haven't used Numbers for years so I don't really know it all that well. But if you can get the data in the form demonstrated in the picture below "Table1" with candidate names on all rows and remove rows with only the names, you can create a pivot table from it (Rows: candidate, Columns: question, Values: sum of Raw). and make sure the raw and max values are numbers not texts. This will do the transposition for you. Now I think you can't do much more with that pivot table in Numbers, other than to copy it and paste it as a regular table. Then you can insert your sub-total columns, percentages and the extra header row for the Max scores for each question. If you have Excel on your Mac, I would skip Numbers altogether. But that's just my personal opinion.Re: Data validation with multiple columns
I would like to add to Hans' solution that you need to create a named range for the first column in the Product table, and point the data validation list to that named range. Then it will automatically expand with the Product table when it is NOT in the same sheet as the cells with data validation. See attached.
