Worksheet change code

[JonnyB]

Hi,

Please could some one help with the code I need for the following

I have dropdowns (data validation list) in B2 & C2 - When I select 952 in B2 & 5064 in C2 I need D2 to automatically be updated to 1

I'm assuming I would just repeat the code to get other results from inputs to cells B2 & C2?

any help appreciated

many thanks
Jon

[Yongle]

Use this formula in the cell

=AND(B2=952,C2=5064)*1

And format the cell to surpress zeros
Select the cell
Right click and select Format Cells
In the Category list, click Custom.
In the Type box, type 0;-0;;@

How it works
The formula returns the Value = TRUE
Multiplying it by 1 turns True into the value 1
If the condition is not satisfied, it retuns FALSE
False is Zero X 1 = 0
Which is surpressed by the formatting

Other Cells
Just put the same formula with different values in the other cells

[Yongle]

Or

=IF(B2=952,IF(C2=5064,1,""),"")

Or

=IF(AND(B2=952,C2=5064)=TRUE,1,"")

Or

=IF(AND(B2=952,C2=5064)<>FALSE,1,"")

Or ( I have no idea why you would want to use this one....)

=IFERROR(IF(B2=952,IF(C2=5064,TRUE(),""),"")*1,"")



None of these require formatting because nothing in the cell if condition not met

[JonnyB]

Thanks for reply - maybe didnt explain properly.

Need some VBA code as there will be multiple results e.g. 952 & 5064 returns 1, 952 & 5067 returns 2, 953 & 5064 returns 3 etc

just need to start and then buils all possible results

[Yongle]

No you did not explain properly :saywhat:
Here is your chance:

1 How many conditions will there be <10, 50+, 100+ ?

2 Will the condition always be :
If B2 = x and C2 = y ?

We may not VBA, let's see.
thanks
Yon

[mperrah]

There is most likely a way to code for all the combinations you have in mind,
Are the number of combinations resolute or changing?
Are the locations of the changes needed resolute or changing?
There are formulas that can populate data validations, as well as macros that can.

Would you post a workbook with ten rows or columns of sample data,
then another sheet with end results after code or formulas do the magic.

for example, if you click in B2 the formula will be inserted...
if the values match you get a 1, if they don't you get nothing


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Row = 2 And _
Target.Column = 2 Then
With Target
.Offset(0, -1).Formula = "=IF(B2=952,IF(C2=5064,1,""""),"""")"
ActiveSheet.Calculate
End With
End If

If Target.Column = 2 And Target.Row = 2 Then
ActiveSheet.Calculate
End If
End Sub

Dream big,
explain in as much detail as you can what your goal is,
then let us go to bat for you.
-mark

[JonnyB]

There are 8 codes (bank a/cs so obviously cant post) and each can interact with one of the others either way so 952 to 5064 would be 1 and 5064 to 952 could be 2. Each scenario would need its own unique reference (not sure how many in total think its 56 conditions - 28 either way) -dont think I can upload a test sheet as I havent posted enough? Thanks

[JonnyB]

I also tried a lookup table but struggled with it - not sure possible?

mperrah -vba code work ok for one scenario

need up to 14 lines so also b3 = 952 c3 = 5064 returns 1 in d3 (same as it would for b2 & b3 = 1 in d2) etc

[mperrah]

There are many options as Yongle pointed out.
the code i posted can be added to an entire column,
and other conditions can be added to the formula,

if the method works we can add the specifics.
We dont need to know what the 8 codes mean, just all the combinations.
We can make preloaded array with all the first column items,
and a second array with second column items and do a comparison.
Or use a temp column for calculating
or make a new sheet in a master workbook that has a list of combination values and build a vlookup
or use index, match and offset so if the list grows you wont need to alter working code.

bottom line, the 56 conditions or 28 interactions will be needed to help you much further.
you should be able to make a test sheet with dummy data that matches your project without revealing personal info.
use x's or something but leave meaningful clues for yourself and enough for us to code solutions.

[JonnyB]

Hi,

I click reply to thread but no icon for attaching test sheet - but here's the first set (have been looking at INDEX/MATCH function but cant quite figure it out.




952

5064

1



952

5067

2



952

5070

3



952

5073

4



952

5075

5



952

5080

6



952

949

7



5064

952

8



5067

952

9



5070

952

10



5073

952

11



5075

952

12



5080

952

13



949

952

14




col B col C col D (starting from row 2)

thanks

[Yongle]

Hi JonnyB

To attach workbook click on GoAdvanced and click on the paperclip.

Attached is what I put together quickly based on VlookUp (for testing my understanding of your problem only ). Vlookup requires first column to be sorted ascending sequentially, but should be ok. Index and match will also work.

Try attached workbook
I have used VLOOKUP to match a concatenation of the 2 numbers (with a hyphen) to a table of “rules” (Row 19 onwards)
Try a few scenarios.
Change the numbers L1 to L9 to your own numbers
Change D19 to D82 to put in your conditions
Named ranges used are “Numbers” for dropdown and “MyLookUp”
The table of rules could be set out in any sequence etc - or even driven thru' set of rules in VBA

Hopefully the method is correct
The table of rules may be the tricky bit and where you may prefer VBA. I will send you something to help later if you prefer VBA route to setting up table

Let me know

Forgot to say - when you change L1 to L9 to your own numbers, may be helpful to sort A19 to D92, based on column C , ascending (for LookUp to work properly)

Yon

[Yongle]

Just noticed a pattern in your table of numbers
952/5064 =1, mirror ie 5064/952 is 1 + 7 = 8
952/5067 =2 mirror is 2 + 7 = 9
etc
Is this something that applies to all valid combinations?


And ran your data in post#10 through the workbook attached in post#11 - it seems to do exactly what you want.

[JonnyB]

Hi Yon,

Pattern is just a coincidence - the final template numbers are being supplied by someone else - I just needed the logic to work - which it
does appear to do. I will tinker with your attachment a little to put unique numbers in for each combination.

Will leave thread open for a little while as i need to build a full table (the 952 is only part of a bigger piece of text)

Your help is much appreciated

Best regards
jon

[Yongle]

Glad it seems to be giving you what you need.
And without VBA too - I always try to make Excel do the job before resorting to VBA.
Keep us posted - it is satisfying to know that a suggested solution works in practice for the end user.
And we always appreciate satisfied customers returning to mark their thread as "Solved" (under Thread Tools at top of Thread)
Regards
Yon

[JonnyB]

Hi again Yon,

Code works fine thanks.
Please could you help further - I need to hide some columns in the activesheet and also one of the other sheets as i protect with toggle - and then unhide
as i unprotect

regards
jon

[Yongle]

Column hiding syntax, adapt to suit


'To hide single columns
Sheets("Sheet1").Columns("F").EntireColumn.Hidden = True
'or if the activesheet
ActiveSheet.Columns("F").EntireColumn.Hidden = True
'For a range of columns
Sheets("Sheet1").Columns("C:F").EntireColumn.Hidden = True

'To unhide all the columns use
Sheets("sheet1").Cells.Columns.EntireColumn.Hidden = False

[Yongle]

If you are hiding several columns that are not next to each other, this is useful

ActiveSheet.Range("A1,B1,D1,E1,G1,Q1").EntireColumn.Hidden = True

[JonnyB]

Hi Yon,

Again - works fine

many thanks for your help

regards
Jon

[Yongle]

Glad to have a happy customer who was very easy to please. :beerchug: