Forum Discussion
Flash Fill VLOOKUP
I enter a 3 letter reference in Column E...
Column F performs a VLOOKUP and reports.
Column P has a basic "=" rule applied from E.
After a few rows of doing this, my worksheet has been automatically populating column F and P correctly whenever I enter data into column E.
Both Column Column F and P are blank, (with no formula entered) until Column E is populated.
I assume this is a Flash Fill?
Recently, this has stopped working for Column F. Column P is still working.
I cannot seem to find a solution to get it working again.
I have checked the advanced options to ensure tick boxes are enabled.
Can anyone provide any suggestions or solutions please?
I am aware of the work-around to drag-fill the columnetc, however I would like to know why it has stopped working and if I can activate it again!
(I am using Version 2109 within MS Office Professional Plus 2016)
- Try this: Select a cell in either non-functioning column that contains the correct formula. Press control+spacebar. Then hit F2 followed by control+Enter. That should convince the table that that formula "belongs" in that column. Repeat for any other column with a formula.
4 Replies
- This is an autocomplete option. If you convert your range (including columns F and P) to a table and make sure all rows of the table for columns F and P contain the formula, it will continue to work as you add new data to the table.
Tip: don't make your table larger than the data. As soon as you type new data immediately beneath or to the right of the table, the table expands automatically.- JKPieterse
Thanks so much for your reponse. I have taken those steps and unfortunately it still will not continue downwards. Even the table does not expand when I go to the next row down.
I did try your method on a brand new workbook and it works fine, so it must be something wrong with my current workbook, rather than a general Excel error... or my error lol- Try this: Select a cell in either non-functioning column that contains the correct formula. Press control+spacebar. Then hit F2 followed by control+Enter. That should convince the table that that formula "belongs" in that column. Repeat for any other column with a formula.
