Dynamic vlookup

[BMD4]

Hello All,

I am using vlookup in excel to to get the target values.

I do not want the users to delete the formula by mistake. so, I am looking for an option to perform vlookup through VBA.

When SourceCoCo is given, the target value should come automatically.

Please advise. Attached the file.

[dflak]

Your requirement COULD be accomplished using VBA, but it does not have to be. You could protect the sheet and this would prevent people from changing the formula. Simply unprotect those cells in which you want the users to enter data.

Attached is a workbook that does both.

First, I changed the lookup range to an Excel Table. You can see by the formulas and the VB code how much easier that is to work with. However, I commend you use of a dynamic range.

The code works on detecting a change on the source sheet:

Please Login or Register  to view this content.

I could have put all the code in this module, but I prefer to do the "heavy lifting" in a regular module.

Here is the working code that does the assignments.

Please Login or Register  to view this content.

The VB code uses the FIND command to find the value. I use offset to put the result one column to the right of the entered value. If you want to put it to the left (and replace the formula), then replace the 1 in the offset command with -1.

[BMD4]

Thank you for the reply. I tried to add another value in Source CoCo (col C) Row 10. But, I am unable to do it.

Can we have this column unlocked.

[dflak]

I suggest you do this the "original way," copy the VLOOKUP formula down column B as far as you want. The IFERROR will fill this in with "" for rows without data in column D.

Then select all the cells you want to be able to do data entry into in column C and unlock the cells. Copy the VLOOKUP formula to the rows you want. Protect the sheet.

There is no need to get more complicated than that. I would not mess with VBA. Use your original workbook.

[BMD4]

Thank you.

The problem I see with formula in original workbook is, if I copy paste some random value in col C (Src CoCo), It is messing the vlookup formula by replacing cell reference with #REF. Is there a way we can avoid this.

Thanks in advance

[dflak]

Here is your original file back.

I made the data into a table since it is my life's mission to get Excel users to use tables . It does make it easier to write formulas.

Then I copied down the formulas in column B as far as row 100. Then I highlighted all the cells from C8 on down and selected Home > Format > Format Cells > and unchecked the Locked checkbox.

Then I went to Review > Protect Sheet. The only thing I left checked was the Select Unlocked Cells. Take a look of the other options available to see what you can allow your users to do.

I can see no reason to get a #Ref error. The only way that error occurs is when you have a formula set up that references a cell or cells and then you delete the row and/or column on which that formula is based.