Enter Data With 2 Way-Lookup

[Dtex20]

Hi guys,

Looking for some help with a formula i'm using, i've got a Two-Way Lookup: Column = Name, Row = Date

=(VLOOKUP(E2,Service!A1:NB35,MATCH(B2,Service!A1:NB1,0),FALSE))

It works great for reading data from the table, however i want to reverse this process. I want it to add data instead of reading it...

So if the name is Matthew and the date is 14/03/2018, it finds where the row and column intersects and it adds a specified value to where the column and row Intersect. The two integers are "40" & "100" These are stored in Cell D2 of the sheet.

E2 = name
B2 = Date

Thanks!

 

[Marcílio_Lobão]

Dtex20, Good morning.

In the cell found, D2, are there any such values there?

How exactly is the content of cell D2?
Are TEXT or NUMBER?

Give us an example of the cell's content and what its expected result is.
This makes it easier to help you.

 

[Dtex20]

=IF(C2="Weekend","100",IF(C2="Rota","40",IF(C2="Night","40")))

Is the Formula in D2, it's a Number either 40 or 100. I basically just want that value added the Column Name, And Row Date meet.

C2 just either says, Night,Weekend,Rota.

I will add this formula to a button eventually, so someone comes along Puts in a name, puts in a date Hits the button and the Value in D2 is entered into where the Date and name intersects on the table.

Trying to reverse the current Vlookup i've got, so it doesn't display the cell. It inputs the D2 value into that cell.

 

[louisH]

Hi,

The bit of code below should do the trick even if it's not optimised (no
You should maybe add it to a button.

Sub test()

Dim col, ro As Double
col = 0
ro = 0
Dim cel As Range

For Each cel In Worksheets("Service").Range("A1:NB1")

If cel.Value = Range("E2").Value Then
col = cel.Column
Exit For
End If

Next cel

If col = 0 Then
MsgBox "Name not found"
Exit Sub
End If

For Each cel In Worksheets("Service").Range("A1:A35")

If cel.Value = Range("B2").Value Then
ro = cel.Row
Exit For
End If

Next cel

If ro = 0 Then
MsgBox "Date not found"
Exit Sub
End If

Worksheets("Service").Cells(ro, col).Value = Range("D2").ValueEnd Sub

 

[Dtex20]

Hi,

I've had no luck with that code, it doesn't find the name. I thought i may have to use a macro, to get what i desired but i'm not sure.

Thanks!

 

[louisH]

Hi,

I've had no luck with that code, it doesn't find the name. I thought i may have to use a macro, to get what i desired but i'm not sure.

Thanks!

If you want to permanently write 40 or 100 in the right cell automatically you have to use a macro.

If it doesn't find the name or the name, you want to make sure :

1-The name in cell E2 is correctly spelled.

 

[louisH]

1-The name in cell E2 is correctly spelled.

2- Names are in range A1:NB1 (first row) and dates in range A1:A35 (first column) - If not, change the corresponding values in the code

3- You are running the macro from the right sheet (the one you wrote the name and date in, and where you put your button)

 

[Dtex20]

Hi,

Indeed the ranges were wrong, so i swapped them. however it adds the value in "D2", to cell A1.

I will try and investigate more, any suggestions.

The name Matthew is in cell "A13", the date is "05/01/2017" in Cell "E1" they meet at point. "E13"

Thanks

 

[louisH]

I tried it on my computer and it worked just fine.

You can try to run the macro in step-by-step mode using F8 in the VBA Editor to see what happens.

It's probably just a Range problem. Verify that you're working in the right sheets, cells etc. and change the values in the code.
Good Luck

 

[Dtex20]

I'm an absolute Moron!

I worked it out, thanks so much for your help!

HAHA!