Vlookup table Formula and IF formula help

[Minty5490]

Hi,

I am using a vlookup formula. Which works well to pull data from my other sheet. But I want it to just pull the colour only from the cell

Example: Data (jan) from cell a1 is a Blue colour, and has a number example 5. Vlookup sheet (e4), overwrites from the information of the data sheet with the number, but not the colour? I only want the colour and keep the information already in the cell on the vlookup sheet e4.

Formula I am using: =VLOOKUP($E$4,Jan,3,1)

Can anyone help, not sure if I need to put a If or AND, but how?

Thanks

 

[DanteAmor]

To obtain the color of a cell and put it in another cell must be done with a macro.

 

[Minty5490]

How do i add a macro in my formula. Not sure on macros. I only know a little on formulas. ?

 

[DanteAmor]

Well, macros do not go in a formula.
The macros are executed when you press a button or when you modify a cell.
Explain with real data and real examples what you want to do and I prepare the macro. I also explain how to execute the macro.
But you must explain in detail and with examples what you need.

 

[t0azt]

Is your original cell blue (or whatever color) due to conditional formatting or is it done manually?

 

[Minty5490]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Calendar Sheet[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 500"]
<tbody style="border-collapse: collapse; width: auto;">[TR]
[TD]Staff name (which is a drop down list)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Hi,

I was looking a conditioning formatting, but still cant get it. I have a drop down list in the Cell "Staff Name", then when I change to a different staff name. the formation from the other Months sheet. So if staff 1 has a holiday on the 1, 2 , 3. I want it to still say 1, 2, 3, but on the month sheet is says H - Holiday and is in Blue, So I want this sheet with the drop down to change to the colour blue, but keeping 1, 2, 3 in the cell as these dates off the month. I have the months sheet, when you put a H in the cell, it changes to the H and shows blue, which is ok.

in the calendar sheet the formula I am using is =VLOOKUP($E$4,Jan,3,1), which changes the cell to the colour, but put is the H and I want it to only change the colour and keep the date of 1,2,3.

Really, sorry but my knowledge is minimum, but I get stuck to extend something like and can be done?? I wish I can attach my worksheet for you to see both of the worksheet I have done.

Thanks

 

[DanteAmor]

Better upload your book and on the sheet you explain step by step what you want to achieve.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Minty5490]

Hi

I have attached the workbook to the dropbox for sharing. I have added a note on the Calendar sheet, which you see the date has been replaced with a B - for bank holiday from the Jan Sheet and I want the colour, but not the B and to still show 1 on the Calendar.

Many Thanks for your help

https://www.dropbox.com/s/nppii65pdtnn0s6/Holiday Chart 2019 DUMMY.xlsx?dl=0

 

[DanteAmor]

Ready.
Summary
The formula buscav is to return value, you can not return colors.
In a cell you can not have a formula and also a value.

To achieve what you need is with a macro.
I give you a macro, do the following:
- Determine the sheet of the month, with row 7.
- Search in the month sheet.
- Find the staff in column B
- Find the day in row 4
- Get the letter according to the intersection of staff and day.
- Find the letter in row 1 and get the color
- Put the color in the day.


You can change the status and you will see that the automatic macro changes the values ​​of the days according to the data in the month sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, c As Range, b As Range, mes As String, col As Long
    Dim staff As String, meses As Variant, m As Long, letra As String
    
    If Not Intersect(Target, Range("E4")) Is Nothing Then
        If Target.Count > 5 Then Exit Sub
        staff = Target.Cells(1).Value
        
        meses = Array("E8:I14", "K8:O14")       'add range for each month
        
        For m = 0 To UBound(meses)
        
            Set r = Range(meses(m))
            mes = r.Offset(-1).Cells(1)
            For Each c In r
                If c.Value <> "" Then
                    Set b = Sheets(mes).Rows(4).Find(c.Value, LookIn:=xlValues, lookat:=xlWhole)
                    If Not b Is Nothing Then
                        col = b.Column
                        Set b = Sheets(mes).Columns("B").Find(staff, LookIn:=xlValues, lookat:=xlWhole)
                        If Not b Is Nothing Then
                            letra = Sheets(mes).Cells(b.Row, col).Value
                            Set b = Sheets(mes).Rows(1).Find(letra, LookIn:=xlValues, lookat:=xlWhole)
                            If Not b Is Nothing Then
                                c.Interior.Color = Sheets(mes).Cells(1, b.Column).Interior.Color
                                If c.Interior.ColorIndex = 2 Then
                                    If Not WorksheetFunction.IsEven(c.Column) Then
                                        c.Interior.Color = 15921906
                                    End If
                                End If
                            Else
                            End If
                        End If
                    End If
                End If
            Next
        Next
    End If
End Sub

---
The macro is in the events of your sheet.
SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

---
Test file

https://www.dropbox.com/s/h9szv2gsqexx5a6/Holiday Chart 2019 DUMMY dam.xlsm?dl=0

 

[Minty5490]

Hi

Many thanks for the test file. I can see it working, but when i create another month sheet for March and right click, it pops open the marco, I pasted the code above, but nothing on the calendar sheet, nothing appear in that month for March?

Really sorry, thank you for having a go for me

I will try to figure something else out, Just not into Macro`s. Thank you