Fill values on the basis of Column header and row header

Brass Contributor

Column Header: Months

Row Header: Employee Names

Data: Present Days

Sheet1: Data

Sheet2: Custom

Get data from Sheet1 and fill Shee2 with intersecting cell value based on Column Header and Row Header.

 

Plz remember that Sheet1 is just and small example of data this can increased up to thousands of employees.

4 Replies

@Vimal_Gaur 

 

Hi, 

 

I think you can use INDEX together with MATCH functions for this.

 

I have attached an excel based on your example file.

 

Screenshot 2023-10-21 at 1.24.12 PM.png

Yes, it is working, great thanks.
why is in your sheet the C column is N/A?
I would be grateful and appreciate if your goodself can please explain the following.

Sub SortWorksheetsTabs()
Application.ScreenUpdating = False
Dim ShCount As Integer, i As Integer, j As Integer
ShCount = Sheets.Count

For i = 1 To ShCount - 1
For j = i + 1 To ShCount
If UCase(Sheets(j).Name) < UCase(Sheets(i).Name) Then
Sheets(j).Move before:=Sheets(i)
End If
Next j
Next i

Application.ScreenUpdating = True
End Sub

@Vimal_Gaur 

 

It is "#N/A" because cell C1 in "Custom" sheet is 2/3/2024, while cell N1 in "Data" sheet is 1/3/2024:

Screenshot 2023-10-21 at 2.11.42 PM.png

 

I think your VBA code is just trying to loop through all sheets in an excel and re-arrange those sheets in an alphabetical order (based on sheet name).

@Vimal_Gaur 

I've created dynamic items for all relevant ranges in your workbook.

This formula will spill a matrix. No fill handle needed.

=LET(
    r, XMATCH(CEmployees, Employees),
    c, XMATCH(CDates, DateHeader),
    IFNA(INDEX(MonthlyData, r, c), 0)
)

.