So i'm trying to auto-fill a row based on a column value. The row needs to be filled based on a column value X which was used in previous rows. So for example if I have columns ID, Contact and Company and filled the company cell out it would automatically look for a row that matches that company value and auto-fill the rest of the row. I've tried using some of the autofill functions and I tried a Vlookup sub for VBA but I have been unable to get the result I want.
Thanks
I am assuming that ID is column A, Contact is Column B and Company is Column C, and the current row is row 10. Put the following formula in B10:
=VLOOKUP(A10;A$1:C9;2;FALSE)
And in C10:
=VLOOKUP(A10;A$1:C9;3;FALSE)
Now, as soon as you add an ID to column A, then column B and C are automatically populated. You can copy the formula for columns B and C to later rows.
If you don't want #N/A to appear in cells before an ID is entered, you can use formulae like this in B10 and C10:
=IF(ISERROR(VLOOKUP(A10;A$1:C9;2;FALSE));"";(VLOOKUP(A10;A$1:C9;2;FALSE)))
=IF(ISERROR(VLOOKUP(A10;A$1:C9;3;FALSE));"";(VLOOKUP(A10;A$1:C9;3;FALSE)))
If you are on another row, replace any 10s in the formula with the current row number, and any 9s with the row number - 1.
