PDA

View Full Version : [SOLVED] Write an IF statement relating to 2 different worksheets



Laurie
07-27-2017, 01:47 PM
How can I write a IF formula for this scenario:

If worksheet "Demo" cell E4 'Emergency Response' shows I or II or III or IV, place a '1' in "Category worksheet" cell C9. If "1" in the cell C9 already, then add the new 1 to the existing 1 in that said cell.

Please help,

SamT
07-27-2017, 03:57 PM
Can't do that with a formula. That would be a Circular reference, AND a Cell cannot hold both an independent value and a formula.

If E4 and C9 are the only two cells involved, you can do it with a Macro, triggered by by changing B4, or Selecting C9, or by Double Clicking C9.

You can also do it if the location of any desired B cell is always 5 rows above the desired C cell.

Laurie
07-27-2017, 04:02 PM
Thank-you so much for responding. Can you provide an example of this Macro Code in this scenario? I have been studying Macro's and watching many Utube Learn How To's all day. I cannot figure out how to write this macro. I just can't thank-you enough!

SamT
07-27-2017, 05:15 PM
OK, I wil assume that Br and C9 all the only two cells to worry about. I will also assume that you want to change C9 automatically when B4 changes.

First Right Clcik on the Demo Sheet Tab and select "View Code." That will open the VBE with the Demo Sheet Code Module open.At the top of the Code Module Pane, you will see a drop down with "General" in it.

Drop it down and select "Worksheet."This will insert the Worksheet Selection Change Stub into the Module.

Now, at the top of the Module, you'll see another Dropdown, with "SelectionChange" in it.Drop it down and select "Change." This will insert the Worksheet Change Stub into the Module. You can delete the Selection Change stub, since we won't be using it.
Private Sub Worksheet_Change(ByVal Target As Range)

End SubEvery time any cell changes it's value, the Worksheet_Change sub is triggered and the cell that changed is referenced by the "Target" Parameter.

Since we only want this sub to use B9, we will check the Target, then decide what the code should do.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target is Range("B4") Then 'Do something
End SubNow, we just need a Sub routine that will check B9 for what you need and modify C9 to suit.with four possibilities, I think a Select Case Statement will work well. it's also easy to maintain


Sub B4ToC9()

Select Case Range("B4").Value
'Two of these lines are wrong. You figger out which two, Note that both types are right in different circumstances
Case Is "I" 'Do Nothing
Case Is "II"
Case = "III" 'Do Nothing
Case = "IV"
Case Else: Exit Sub
End Select

'Ok, we didn't exit the sub
'Now you want to append a "1" to C9, right?
'First, see if it it empty, if so, then just place a "1" in it
If IsEmpty(Range("C9")) Then
Range("C9") = "1"
Else
Range("C9") = 'You figger it out. Hint: google "&"
End If
End Sub
Now go back and change
If Target is Range("B4") Then 'Do somethingto

If Target is Range("B4") Then B4ToC9

Laurie
07-27-2017, 05:38 PM
I will try this and get back to you as soon as can, (possibly tomorrow though) hope that is okay. Thanks again, so much.

Laurie
07-28-2017, 06:11 AM
The Macro needs to identify different Worksheets as (example, if Demo Worksheet - cell E4 - shows I or II or III or IV place a 1 in the cell C9 or D9 or E9 or F9 accordingly in the neighboring Worksheet <call it the Category Worksheet>. If there is a number in the cell (in C9 or D9 or E9 or F9) on the Category WS, then sum the new total in that corresponding cell.

SamT
07-28-2017, 06:39 AM
For a Procedure this short I would just use the full Sheet reference in front of the Range(s)


Sheets("Demo").Range("XYZ")

If I was going to have to refer to them many times in the code, I would set a short Variable to the sheets, 'cuz I be a lazy typist

Dim Demo As Worksheet
Dim Cat As Worksheet
Set Demo = Sheets("Demo")
Set Cat = Sheets("Category")

If Cat.Range("XYZ") = "Say What?" Then
Note how you always put the sheet name in double quotes, but never the Variable name. Later in your career, you will learn of a special case where the Sheet Object CodeName isn't quoted. For now, you can think of the Sheet name as the "Tab Name" and always quote it.

So... Let's see your code to date.

Laurie
07-28-2017, 07:40 AM
Here it is. I have all the sheets and pertinent cells in the Macro Code. Not working. I'm just not getting it right!


Dim Demo As Worksheet
Dim Soil As Worksheet
Dim Move As Worksheet
Dim General As Worksheet
Dim Surface As Worksheet
Dim Intermediate As Worksheet
Dim Production As Worksheet
Dim Category As Worksheet

Set Demo = Sheets("Demo")
Set Soil = Sheets("Soil")
Set Move = Sheets("Move")
Set General = Sheets("General")
Set Surface = Sheets("Surface")
Set Intermediate = Sheets("Intermediate")
Set Category = Sheets("Category")

If Demo.Range("O4-O86")End(xlUp).Offset(1) = "I,II,III,IV" Then 'mark 1'
If Soil.Range("O4-O72")End(xlUp).Offset(1) = "I,II,III,IV" Then 'mark 1'
If Move.Range("O4-O34")End(xlUp).Offset(1) = "I,II,III,IV" Then 'mark 1'
If General.Range("O4-O67")End(xlUp).Offset(1) = "I,II,III,IV" Then 'mark 1'
If Surface.Range("O4-O14")End(xlUp).Offset(1) = "I,II,III,IV" Then 'mark 1'
If Intermediate.Range("O4-O13")End(xlUp).Offset(1) = "I,II,III,IV" Then 'mark 1'
If Production.Range("O4-O1")End(xlUp).Offset(1) = "I,II,III,IV" Then 'mark 1'

Select Category.Range("c5,f5").Value
Case Is "I" 'Do Nothing
Case Is "II"
Case Is = "III" 'Do Nothing
Case Is = "IV"
Case Else: Exit Sub
End Select

If IsEmpty(Range("c5,f5")) Then
Range("c5,f5") = "1"

Else
Range("c5,f5") = 'blank'

End If


End Sub

SamT
07-28-2017, 08:30 AM
There is no such thing as Range("O4-O86"), Range("number - number"), of even Range("number")
End(xlUp).Offset(1) always returns the last used cell in a column
End(xlUp).Offset(1) returns the empty cell below that last used cell, so it will never have any value in it.
Do those ranges really have all 4 Roman Numerals in them?
Everything after a single Quote mark is a comment and not part of the Code. ex 'Mark 1' and 'blank'

Place Option Explicit at the top of the Code Page. Then, with the Cursor inside the sub, you can press F8 repeatedly and when you get to an error line, the VBE will tell you what the error is about.

Laurie
07-28-2017, 09:17 AM
Yes, so what this spreadsheet (ie., Demo) is, is 4 columns have different roman numerals I,II,III,IV in them, identifying what rating a certain category is (ie., Budget variances / overruns gave a rating of IV). So I need that new specific rating, added into the, 'keep the totals updated' 'Category' worksheet, under the column of example f5. So f5 had a number of 3 in it, so now we need to +1 so f5 would now equal 4.
My excel worksheet looks like this: c2 says Warehouse Demo (merged to f2)~ c3 says Risk Ranking (merged to f3) ~ C4 has headings that say I, C5 say II, C6 say III, C7 say IV. (so C5 currently says 3) A3 is the category title cell, titled Category. A5 is the first audit item that will currently be ranked. (The phrase in this A5 cell is Budget variances / overruns).
So now because the budget variances / overruns gave another new reading of IV, I need the f5 to now say 4 (as it had a 3 in there and now 3 + 1=4)
Is this so totally confusing or what. I cannot express my gratitude enough for your help.

SamT
07-28-2017, 05:35 PM
Laurie,
Is this so totally confusing or what.Yes!


Why don't you sanitize the workbook of all confidential information then use the Go Advanced button to use the Advanced Editor. Under the Advanced Editor is a Button that says Manage Attachments. Please upload the sanitized workbook.

You know the old Saying, " A Workbook is worth a million words." Or something like that. :D

Laurie
07-29-2017, 06:49 AM
Hi VBAX Wizard,
Attached is the sanitized document for which I am working on. I need the VBA Codes for both the If Statement and the automatic update to master sheet. You can see on the VBA editor where I was trying to get these write. Please, please, please help me ~ thank-you~

SamT
07-29-2017, 10:50 AM
On the Category Risk Ranking sheet, do you want something like this


Category
Warehouse Demo

Contam Soil


Pre
Mitigation
Risk
Residual
Risk Ranking

Pre
Mitigation
Risk
Residual
Risk Ranking




I
II
III
IV


I
II
III
IV


Budget variances / overruns

0
5
1
0
0







Rail line - falling debris, railcar derailment







22
57
0
3



Contractor Management (prequal, avail, training)













Emergency access for Injuries, medical treatment














You are lucky... I have a little experience in the industry.

In all the data sheets, Column A has a Rather meaningless set of numbers. What are hey for? AND. are they supposed to be updated on the sheets somehow?

I am assuming that the data flow is from User to Data sheet, (ex: Warehouse Demo,), then from data sheet to Master list, while updating the Category Risk Ranking sheet. Is that so?

Laurie
07-29-2017, 11:10 AM
Hi, yes, all rows have to stay as is, and yes, how you have it looks good. I have removed all confidential information and just placed letters in accordingly in various cells. For the IF Statement problem I have, what I need is, the Category Risk Ranking sheet to automatically update, according to the Categories when Rank entries are placed on the Warehouse Demo Sheet, Contam soil Sheet, Mobilization Sheet, General Sheet, Surface Sheet, Intermediate Sheet and Production Sheet in the Column 'O' 'Rank' sections. If this can't be done, because it actually is a huge pain, (I really have no idea how to write a macro for this scenario) I'm actually okay with that then.

The most important issue that I have to complete is to get the Macro written for the Master List to automatically update from any and all entries entered in on the Warehouse Demo Sheet, Contam Soil Sheet, Mobilization Sheet, General Sheet, Surface Sheet, Intermediate Sheet and Production Sheet. These sheets will be constantly active with new line enteries , so I need the Master List sheet to update automatically and include the new line enteries.

SamT
07-29-2017, 11:34 AM
yes, all rows have to stay as is,
Hunh! What is that about?


These sheets will be constantly active with new line enteries
While there are many ways to do that, from what it sounds like the safest way is:

When sheet Activated, Save last Row number
When sheet is deactivated, Save the new last Row number

For those operations, I would place identical Procedures and Variables in each sheet to count the rows. then, in a standard module, place the code to do the inserting into Master list. The Sheet would call that procedure and pass the Range of Rows to be inserted. This also means that the Master list is not getting completely replaced each time it is updated. By perfoming the operation Before deactivation, all changes in the new Rows are passed to the Master list

This also means the the Category Risk Ranking sheet gets only the required cels updated. Note that I haven't thought about that algorithm yet.

Important. Un merge the cells on one sheet, say Warehouse Demo, then use Horizontal Alignment = "Center Across Selection" instead of Merge Cells to make it pretty. set all cells' width, WordWrap, Borders, fonts etc, until that set of headers looks exactly the way you want. Then copy those top three rows and on all the other sheets, Paste All and Paste Column Widths. It is important that all data sheets be identical in structure.

Laurie
07-29-2017, 01:01 PM
Hello SamT, I have formatted the document some, but will complete to perfection once I have the Macro all in place. I am still struggling with getting the macro completed to have the Master List updated automatically with the push of the ActiveButton 'update Master List'. I cannot get the update to include the last occupied row in the various sheets, (Demo Sheet, Contam Soil Sheet, Mobilization Sheet, General Sheet, Surface Sheet, Intermediate Sheet and Production Sheet). Can you please help me write this whole VBA statement. ~thank-you~

SamT
07-29-2017, 04:24 PM
:banghead::banghead::banghead::banghead::banghead::banghead::banghead::bang head::banghead::banghead::banghead:


It compiles. It runs without error. It doesn't do a damme thing (on my machine.)


Sub Initial_Creation_of_MasterList()
'
'This copys all the sheets' contents onto Master List.
'..
'This Procedure should only be run once and only on a Blank Master List sheet

Dim ML As Worksheet
Set ML = Sheets("Master List")
'
Application.ScreenUpdating = False
Dim x
x = ML.Range("A1:C3").Address
Sheets("Warehouse Demo").UsedRange.Copy Destination:=Sheets("Master List").Range("A1")
x = Sheets("Warehouse Demo").UsedRange.Address
Sheets("Contam Soil").UsedRange.Copy Destination:=ML.Cells(Rows.Count, "A").End(xlUp).Offset(3)
Sheets("Mobilization-Rig Move").UsedRange.Copy Destination:=ML.Cells(Rows.Count, "A").End(xlUp).Offset(3)
Sheets("General Drilling").UsedRange.Copy Destination:=ML.Cells(Rows.Count, "A").End(xlUp).Offset(3)
Sheets("Surface Hole").UsedRange.Copy Destination:=ML.Cells(Rows.Count, "A").End(xlUp).Offset(3)
Sheets("Intermediate Hole").UsedRange.Copy Destination:=ML.Cells(Rows.Count, "A").End(xlUp).Offset(3)
Sheets("Production Hole").UsedRange.Copy Destination:=ML.Cells(Rows.Count, "A").End(xlUp).Offset(3)
Application.CutCopyMode = False

Application.ScreenUpdating = True
End Sub


Ignore any line with an "x" in it. those are just for troubleshooting. They actually do nothing

Check the Sheet names against the code. also I found one sheet name with a trailing space. Best Practice is to copy the Name from the Properties Window and paste it into the code

Laurie
07-30-2017, 06:01 AM
It Works!!!! :hi: Thank you all so much. Absolutely amazing. I am so very grateful.
The update Master List from multiple worksheets problem is closed.

SamT
07-30-2017, 01:29 PM
Good, now back to the original Category Risk Ranking sheet problem.

Once you have all the data sheets in a common configuration.Structure, I think you can use CountIfs formulas to keep it updated.

I am not a Formula guy, so you might want to start a new thread about using formulas for this

Possible Formula for Category Risk Ranking sheet, Cell C5 of your Laurie - Sanitized_Update Automatically WW Risk Registry.xlsm attachment
=CountIfs('Warehouse Demo'!I:I=$C4, 'Warehouse Demo'!C:C=$A5)
The formula as above would then be copied across to all the Rank columns and down to the bottom of the used range.

This does depend on all the Words in the Data Sheets Categories column match all the words used in the Category Risk Ranking sheet Categories column.

Since the use of VBA and Formulas depends on exact matches to work. I suggest a Data Entry UserForm to insure that no User Typos and or personal word choices are possible. Before creating said UserForm it is imperative that you get the worksheets up to standard.

I generally use a Very Hidden sheet to hold lists for the User to choose from on the UserForm. In this case, I think "UserFormLists" would be a good name for the sheet.

The UserFormLists sheet would contain

List of possible Risk Ranks, (I, II, etc)
Lists of Categories. This can be a all inclusive list, but for better User Experience, I would break it down to several lists with only (all) those applicable to each Data Sheet, Not all three Drilling Ops sheets wold probably use the same or identical lists. If we go with identical lists, two can be mere formulas. (=Cell in primary List)
a List of all the Data Sheet Names
List of Receptors
Basically a list for each possible standard choice a Worksheet User must make when filling out any given Data sheet.

Laurie
07-30-2017, 01:42 PM
Thank-you SamT. So greatly appreciate everything you have done for me.

SamT
07-30-2017, 03:11 PM
YW