Forum Discussion
How to filter to include blanks without selecting blanks.....
Good Morning All,
I have a spreadsheet on Excel Online version that people fill in for requesting PPE and it has drop down boxes so they can select what they need. The people filling it in are not the most knowledgeable with computers never mind Excel just FYI for context.
When the spreadsheet gets filtered eg to show only the requests for Bob, it shows the items for Bob and then the row underneath is row 532 which is outside my data and therefore the drop down boxes do not show so when someone comes to fill it in they can't select what they want to order and just end up free typing which defeats the object and means things can get missed.
I want to be able to filter the spreadsheet in any column EG Bob so it shows the items for Bob and then the next row is the next 'available' row which in this example would be row 23 but also people are filling this in daily so row 23 is not set. Tomorrow it will have more data in and the next 'available' row could be row 30 so obviously tomorrow I would like to be able to filter to Bob, see all of Bobs items and then the next row showing is row 30.
This is a test doc:
https://1drv.ms/x/c/15153d41767146da/IQDVK5iPAiw-SoUyrf9ciYdwAb76zwpiF-IAtr6ISry1KwQ?e=v4vo3C
Hope that makes sense and hopefully I am not asking for the impossible!
Many Thanks,
1 Reply
It appears the issue is you have formulas filled down all those rows just to 'accomodate' new data. The way to better handle this is to conver this all to a Table then the Table will automatically expand with a new line and the formulas will automatically be filled down into that new row.
I made that change in your sample sheet so you can check it out.
Also note that best practice would be to update the formulas to use the table based stuctured references. I upgraded the formula in the Total formula accordingly:
=IFERROR([@Quantity]*[@[Price (each excl VAT)]],"£0.00")
It looks 'messy' until you understand it and then it reads much better. Basically the '@' symbol means the corresponding row so simply ... the value in the column called 'Quantity' on this row * the value in 'Price (each excl VAT)' also on this row but if there is an error then return "£0.00" and I "know" this without scanning across the file to file some column E and looking down to the header to see what it is.
As for how to 'update' using the table references (structured reference) you can simply highlight the reference in the formula (e.g. E7) and then click on that corresponding cell(s) and excell will update it for you.
One last note. To update that table, I deleted all the unused rows then clicked on a cell in the table. Then clicked on Home tab and the 'Format as a Table' button and selected a format. It then autoselectes the range and let's you confirm that is the correct range and if the header is in the first row (yes). Lastly, I highlighted those 3 columns with formulas and clicked fill down again to let excel know to use those formulas as the default for those columns (I don't remember needing to do that in the past and/or when using desktop version, but just in case that is what I did here)
