Forum Discussion

Vimal_Gaur's avatar
Vimal_Gaur
Brass Contributor
Oct 21, 2023

Fill values on the basis of Column header and row header

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.

BI & Data Analysis
excel
Formulas and Functions
Macros and VBA
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Oct 21, 2023

    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)
)

    .

     

    • Vimal_Gaur's avatar
      Vimal_Gaur
      Brass Contributor
      Oct 21, 2023
      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
      • rachel's avatar
        rachel
        Steel Contributor
        Oct 21, 2023

        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:

         

        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).

         

         

Resources