Condition if Cell contains anything then write formula

[Infine]

Hello,

I want to be able to write out this function: "=VLOOKUP(D2;Databas!$A$2:$O$1048576;8;FALSE)" (which will automatically change "D2 to D3, D4 etc depending on which cell you write in on D)


So if D2 contains ANYTHING I want "=VLOOKUP(D2;Databas!$A$2:$O$1048576;8;FALSE)" be typed next to it (on E2). If it does not contain anything, I want E2 be empty. Is this possible? Can I put this through some Rule?

If D3 contains ANYTHING I want "=VLOOKUP(D3;Databas!$A$2:$O$1048576;8;FALSE)" be typed next to it (on E3) etc.

Appreciate all help.

 

[EFANYoutube]

Hi Infine
Put this in the sheets module and save the workbook as .xlsm

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 4 Then
        If Target = "" Then
            Target.Offset(, 1) = ""
        Else
            Target.Offset(, 1).Formula = "=VLOOKUP(" & Target.Address(0, 0) & ",Databas!$A$2:$O$1048576,8,FALSE)"
        End If
    End If
End Sub

 

[Infine]

Is there no other way than using VBA as I do not want to run a SUB everytime as people who will use this will not be able to do this without causing anything major.

I was more thinking for a Conditional rule which says if Cell D2 contains anything, write out in E2 "VLOOKUP bla bla". Is there a way?

 

[Fluff]

Maybe

=if(D2="";"";VLOOKUP(D2;Databas!$A$2:$O$1048576;8;FALSE))

 

[Infine]

How exactly should I do this? What condition should I use, is this below correct? I don't seem to make it work

I am thinking: IF D2 contains anything then E2 Types VLOOKUP code

 

[Fluff]

You cannot do that with conditional formatting.
You either put the formula into E2 & copy down as far as needed & it will return "" until a value is put into col D, or you use as macro to add the formula when needed.

 

[Infine]

Darn, this is not good news :P 2021 and Excel has not made this possible...

We drag it at the moment but this is not optimal. VBA is not optimal either as there is a risk with this data.

 

[Fluff]

Fraid you don't have much choice.

By the way, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[EFANYoutube]

Infine
The Vba Above runs very quick (as in no one would evein know its running and if a cell is changed that is not in "D" column, the code does nothing
Up to you but seems the best solution

 

[Infine]

I see too many risks with a function/Makro for the Database.

However, I might add this on the future. What would be "Target"? Could you describe how I would use the function you have written? I am more familiar with using loops. If I use this script I would add more things to automatically write:

If I type something in D2 or B2 I want in A2: "CTRL + SHIFT ;" / Today() (Todays date in text).
I want that VLOOKUP in E2 and F2 and G2.

And if I type something in D3 or B3 I want the same again for A3, E3, F3, G3 and I want to create a Makro button which every user will have to click to run the Sub.