Nesting Formulae with INDIRECT Function, Across Dynamic Sheets

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
714
Office Version
  1. 365
Platform
  1. Windows
I'm trying to build a formula (in column G) that basically says, "If the worksheet that is named with the value of C2 on this worksheet has "Late" in the 2nd to Last Row, return Late. Otherwise, return the value of the Last Row."

My challenges are:
  • The sheets that I'd be searching on are dynamic, meaning they get created and named when a larger set of code runs.
    • I'd like to add the desired formula into this code set. That way, the formula is added when the record is created, and updates as the workbook is updated.
  • I'll ultimately want to expand this formula to 2 other columns (H & I), pulling in financials. Those formulas would be slightly different in, I would want them to say, "If "Late" is found, pull the 2nd to Last Row, pull an amount from a certain column in that row. If not, pull an amount from the Last Row."

I do have some code that gets me pieces of the data, but I'm at a loss on how to pull it all together.

Hopefully this helps show what I'm trying to do:
[TABLE="class: grid, width: 1281"]
<tbody>[TR]
[TD]Column
[/TD]
[TD]Desire
[/TD]
[/TR]
[TR]
[TD]ws3.Range("G"&LastRow+1)
[/TD]
[TD]If column CB on the dynamic sheet (named after ws3.Range("C"&LastRow) has the value "Late", return Late. Otherwise, return "Current".
[/TD]
[/TR]
[TR]
[TD]ws3.Range("H"&LastRow+1)
[/TD]
[TD]If "Late" was found in the formula above, return the value of range ("CA" &LastRow-1) on the dynamic sheet. Otherwise, return the value of range ("CA"&LastRow) from the dynamic sheet.
[/TD]
[/TR]
[TR]
[TD]ws3.Range("I"&LastRow+1)
[/TD]
[TD]Return the value of range ("CA"&LastRow) from the dynamic sheet.
[/TD]
[/TR]
</tbody>[/TABLE]


While it serves a different purpose, I think this code can be leveraged (somehow), as it pulls the MAX value from a target column, on the dynamic sheet.
Code:
ws3.Range("J" & LastRow3 + 1).FormulaArray = "=MAX(IF(INDIRECT(""'""&RC[-7]&""'!D:D"")='SUMMARIES'!RC[-7],INDIRECT(""'""&RC[-7]&""'!I:I"")))"

I also have this code (again, serving a different purpose) that looks for the word "Late" in the correct column, on the dynamic sheet. If "Late" is found, it targets that row. If it's not found, the Last Row is the target.
Code:
Dim ws3, ws4, ws5 As Worksheet
Dim CSht As String
Dim LastRow As Long
Dim UpdateRow As Long
Dim SummaryFindRow, BioFindRow, PymtFindRow As Range
Dim SummaryTargetRow, BioTargetRow, PymtTargetRow As Long

CSht = Me.txt_ClientID
Set ws3 = ThisWorkbook.Sheets("Summaries")
Set ws4 = ThisWorkbook.Sheets("Bios")
Set ws5 = ThisWorkbook.Sheets("Stats")
Sheets(CSht).Activate
With ActiveSheet
    LastRow = Sheets(CSht).Cells(.Rows.Count, "D").End(xlUp).Row
    Set FindRow = Range("CB:CB").Find(What:="Late", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
    If Not FindRow Is Nothing Then
        UpdateRow = FindRow.Row
    Else
        UpdateRow = LastRow
    End If
End With

Hopefully that all makes sense. I only learned about the INDIRECT function a week or two ago, so nesting other formulae into it AND dealing with the dynamic sheets is proving difficult for me.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
I found another (more obvious) solution to this challenge. Essentially, I'm tracking how many days the Client is delinquent on the same target sheet, so I just added an if statement to return "Late", if one of those values is > 0.

I'm trying to build a formula (in column G) that basically says, "If the worksheet that is named with the value of C2 on this worksheet has "Late" in the 2nd to Last Row, return Late. Otherwise, return the value of the Last Row."

I can solve for the other 2 issues with this code, but I would need to understand how to have it run through every single record on ws3.

Code:
Sub Macro3()
Dim cSht As String
Dim ws3 As Worksheet
Set ws3 = ThisWorkbook.Sheets("Summaries")
LastRow3 = ws3.Range("C" & Rows.Count).End(xlUp).Row
cSht = ws3.Range("C" & LastRow3).Value
ThisWorkbook.Sheets(cSht).Activate
With ActiveSheet
    CSLastRow = Sheets(cSht).Cells(.Rows.Count, "D").End(xlUp).Row
End With
ws3.Range("H" & LastRow3).Value = Sheets(cSht).Range("CA" & CSLastRow - 1).Value
ws3.Range("I" & LastRow3).Value = Sheets(cSht).Range("CA" & CSLastRow).Value
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,217
Members
452,619
Latest member
Shiv1198

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top