How to choose to reference a previous sheet or not

[sassriverrat]

Hello-

I have a macro that allows me to reference a cell in a previous sheet in formulas. However, I’d like to know if anyone has any ideas on how to write the code for the following-

My workbook creates one of two sheets based on user selection. If sheet one is written, the formulas in the second sheet can easily reference the first sheet (=if(prevsheet(A2)=“”,’voyage’!A1,prevsheet(A1) for example). However, if the second sheet is created, there isn’t a first sheet to reference in the formula. So basically the formula would be just =‘voyage’!A2 on sheet2 but I’m not sure how to tell the cell to use on formula versus the other. A simple “IF Then” would work IF sheet1 had one static name, but technically speaking, sheet1 goes by “Noon” and then a number, based on the number of times the original sheet is copied. The last “Noon” is the previous sheet to reference, but because it can be Noon (if only one sheet1 is created) or Noon10, I don’t know how to tell it to choose the formula based on a “Noon” sheet existing. Thanks. And I can clarify if someone needs it.

 

[The_Macrotect]

Hi sassriverrat,

What you want to do can probably be done, but I'm having trouble following. Can you post the macro code that you have so far?


-Matt

 

[sassriverrat]

Hey Matt- Thanks for taking a look at it. Ok So here is the code for the PrevSheet. This allows me to use "PrevSheet()" within a formula to reference a cell from the sheet before

Function PrevSheet(RCell As Range)    Dim xIndex As Long
    Application.Volatile
    xIndex = RCell.Worksheet.Index
    If xIndex > 1 Then
        PrevSheet = Worksheets(xIndex - 1).Range(RCell.Address)
    End If
End Function

So I'd like a macro that will fill cell("A1") with formula1 if sheet1,2,or3 is the previous sheet and the macro will use formula2 to fill cell("A1") if sheet1,2,or3 is not the previous sheet. I think i just answered my own question

 

[sassriverrat]

hmmm ok maybe not quite figured out yet. So I need a macro that basically says:

If the previous sheet is not named "Noon" or "Noon" + "#" (like "Noon10"), use formula 1, otherwise use formula 2. It should be a simple if then case but I'm not sure how to do the dynamic part (i.e. the number part of naming the previous sheet).

Private Sub Formula_Chooser()

Dim form1 As Double
Dim form2 As Double

form1 = Sheets("Voyage Specifics").Range("C11") 
form2 = PrevSheet("N11")

ActiveSheet.Range("N8") = form1
ActiveSheet.Range("N8") = form2

'This is where I get stuck

If (the previous sheet) = noon# Then
ActiveSheet.Range("N8") = form2
Else: ActiveSheet.Range("N8") = form1
End If

End Sub

 

[The_Macrotect]

Ok, I think this should work:

Private Sub Formula_Chooser()

Dim form1 As Double
Dim form2 As Double
Dim ws As Worksheet

form1 = Sheets("Voyage Specifics").Range("C11")
form2 = PrevSheet("N11")
Set ws = ActiveSheet.Previous

ActiveSheet.Range("N8") = form1
ActiveSheet.Range("N8") = form2

If ws.Name Like "Noon*" Then
ActiveSheet.Range("N8") = form2
Else: ActiveSheet.Range("N8") = form1
End If

End Sub

 

[sassriverrat]

Awesome! I’ll give it a shot. So in theory, if I change this from a sub to a function, it should run automatically (I.e. no button or anything to set it off, otherwise as a sub it needs an action)? If this is the case, can a specified function be saved to work when the sheet doesn’t exist? My thought being that I’ll specify ws to be worksheet(“Arrivals”) and the function only needs to run in that sheet, but when the workbook is first created typically it might be days before the “Arrival” sheet is generated, therefore meaning it doesn’t exist initially. I don’t want that to bug out the whole workbook.

Thanks again!

 

[sassriverrat]

Ok so that worked like a champ- and now building on this (for something else though) - taking this idea

Now if I wanted this thing to add N17 from each sheet named "Noon#" if "Noons" exist- and I'll just add R9 if "Noons" don't exist.

Thanks