ISB:
Remove Formulas from All Sheets
Insert a new module: Go to Insert > Module.
Paste this code:
Sub RemoveFormulasAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.UsedRange.Value = ws.UsedRange.Value
Next ws
End Sub
Press F5
Fac State HR
=COUNTIFS(Faculty!$E$2:$E$969,'Fac State HR'!$D$3,Faculty!
$H$2:$H$969,'Fac State HR'!$D$4,Faculty!$I$2:$I$969,'Fac State HR'!B5)
Desig List-Fac
=CHOOSECOLS(FILTER(Faculty!B2:L969,(Faculty!E2:E969='Desig List-Fac'!
A1),""),1,7,8,10,11)
=OFFSET('Deptt List-Fac'!$A$2:$H$2,0,0,2+COUNTA('Deptt List-Fac'!$G:
$G),8)
Deptt List-Fac Inc Age
=CHOOSECOLS(FILTER(Faculty!B2:P969,Faculty!I2:I969='Deptt List-Fac Inc
Age'!A1),1,3,4,7,10,11,15)
=OFFSET('Deptt List-Fac Inc Age'!$A$2:$I$2,0,0,2+COUNTA('Deptt List-Fac
Inc Age'!$H:$H),9)
Deptt List-Fac
=CHOOSECOLS(FILTER(Faculty!B2:L969,Faculty!I2:I969='Deptt List-Fac'!
A1),1,3,4,7,10,11)
VB Code Highlighted Cell
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
' Clear previous highlights on the active sheet
Sh.Cells.Interior.ColorIndex = 0
' Highlight the row and column of the selected cell
If Not Intersect(Target, Sh.UsedRange) Is Nothing Then
Target.EntireRow.Interior.Color = RGB(255, 255, 153) ' Light yellow for
the row
Target.EntireColumn.Interior.Color = RGB(204, 255, 255) ' Light blue for
the column
End If
End Sub
VBA Code AutoFitt
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim cell As Range
Dim row As Range
Dim lastRow As Long
' Set the worksheet
Set ws = ThisWorkbook.Sheets("Deptt List-Fac")
' Check if the change occurred in cell A1
If Not Intersect(Target, ws.Range("A1")) Is Nothing Then
' Find the last row with data in column B
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
' Loop through each row with values in column B
For Each row In ws.Range("B1:B" & lastRow).Rows
' Set minimum row height to 30
row.EntireRow.RowHeight = 30
' Reference the "Remarks" column (Column H)
Set cell = ws.Cells(row.Row, 8)
' Enable Wrap Text and AutoFit for rows with content that doesn't fit
cell.WrapText = True
row.EntireRow.AutoFit
' Ensure the row height is not less than 30
If row.EntireRow.RowHeight < 30 Then
row.EntireRow.RowHeight = 30
End If
Next row
End If
End Sub
=OFFSET('Deptt List-Fac'!$A$2:$H$2,0,0,2+COUNTA('Deptt List-Fac'!$G:
$G),8)
Deptt List-Offr&Stf
=CHOOSECOLS(FILTER(Admn!B2:O773,Admn!O2:O773='Deptt List-Offr&Stf'!
A1),1,4,5,6,12,13)
=OFFSET('Deptt List-Offr&Stf'!$A$3,0,0,2+COUNTA('Deptt List-Offr&Stf'!$G:
$G),8)
Desig List-Offr&Stf
=CHOOSECOLS(FILTER(Admn!$B$2:$O$773,(Admn!$E$2:$E$773='Desig
List-Offr&Stf'!A1)+(Admn!$E$2:$E$773='Desig List-Offr&Stf'!I1),""),1, 4, 5, 6,
12, 13, 14)
=OFFSET('Desig List-Offr&Stf'!$A$2:$H$2,0,0,2+COUNTA('Desig List-
Offr&Stf'!$H:$H),8)
Cont Expiry Fac-Main
=CHOOSECOLS(FILTER(Faculty!B2:L969,Faculty!L2:L969='Cont Expiry Fac-
Main'!A1),1,3,4,7,8,10,11)
=OFFSET('Cont Expiry Fac-Main'!$A$2,0,0,2+COUNTA('Cont Expiry Fac-Main'!
$H:$H),9)
RWP:
Deptt List-Fac RWP
=OFFSET('Deptt List-Fac RWP'!$A$2,0,0,3+COUNT('Deptt List-Fac RWP'!$G:
$G),8)
RC’s:
Camp Deptt List-Fac
=OFFSET('Camp Deptt List-Fac'!$A$2:$J$2,0,0,1+COUNTA('Camp Deptt List-
Fac'!$I:$I),10)
