Automatic cell info based on another cell

[Wawinator]

Hello,

I am trying to have one cell automaticall input infromation based on another cell. For example, if A1 = 10, then I want B1 to automatically equal 20.

The formula I am using in B1 is as follows - =if(A1="10","20","")

I have 2 problems though:

1st - I don't want to put the furmula in any cell because other information is put in there also. I tried putting it in conditional format but I don't think it is meant for such usage. Also, I already have something in data validation. so I can't put anything there because to my knowledge you can only put one validation per cell.

2nd - I have multiple numbers (around 7) that I need to be automatically inputed along the columns.

Your help is appreciated and thank you for sharing with me,

regards,

Wawinator

 

[Teeroy]

I have 2 problems though:

1st - I don't want to put the furmula in any cell because other information is put in there also. I tried putting it in conditional format but I don't think it is meant for such usage. Also, I already have something in data validation. so I can't put anything there because to my knowledge you can only put one validation per cell.

2nd - I have multiple numbers (around 7) that I need to be automatically inputed along the columns.

Problem 1 What do you mean other information is there? Is a value typed in or is there an existing formula?
Problem 2 ???

I think you need to expand your problem statement or show some data to help clarify your requirement.

 

[Wawinator]

Problem 1 What do you mean other information is there? Is a value typed in or is there an existing formula?
Problem 2 ???

I think you need to expand your problem statement or show some data to help clarify your requirement.

1. By other informtion i mean, other values, for example "35" or "78" may be put in cell A1, I dont need an auto input in B1 in that case.
However if "10" is put in A1, then I want 20 to automatically be put into B2.

For example:

A1	B1	LIST	AUTO
10	20 (AUTO)	10	20
35		30	40
78		50	60
50	60 (AUTO)	70	80

<TBODY>
</TBODY>

There is an exising formula in 'data validation' that displays a warning for users. so i am not sure that I can put anything else in 'data validation'.

Can this be done without putting the formula in teh cell, for example via conditional formatting?

thanks for your help,

regards,

Wawinator




dfg

 

[Teeroy]

Try the following formula, if you have excel 2007 or later:

B1: =IFERROR(INDEX({20,40,60,80},MATCH(A1,{10,30,40,60,70},0)),"")

pre excel 2007 use:

B1: =IF(ISERROR(MATCH(A1,{10,30,40,60,70},0)),"",INDEX({20,40,60,80},MATCH(A1,{10,30,40,60,70},0)))

 

[Wawinator]

ADVERTISEMENT

Try the following formula, if you have excel 2007 or later:

B1: =IFERROR(INDEX({20,40,60,80},MATCH(A1,{10,30,40,60,70},0)),"")

pre excel 2007 use:

B1: =IF(ISERROR(MATCH(A1,{10,30,40,60,70},0)),"",INDEX({20,40,60,80},MATCH(A1,{10,30,40,60,70},0)))

Hello Teeroy,

Thank you very much for your help and time.

The formula works perfectly when I put it in cell B1 however i don't want to put it in any cell, I need it to be in the background and to work within the entire "B" column. I tried to put it in conditional formatting but it didn't work.

Any other suggestions?


Thank you again,

Wawinator

 

[Teeroy]

I need it to be in the background and to work within the entire "B" column.

Before I make another suggestion I need you to be explicit about what is supposed to be in "B" if the condition is not triggered?

 

[Wawinator]

ADVERTISEMENT

The idea is this, we have many trailers at the company I work for, however some trailers need to go together as a front trailer and back trailer. it is fine if other trailers are mixed, but some need top go together.


For example, trailer 10 (front trailer) needs to go with trailer 20 (back trailer), and trailer 30 (front) needs to go with trailer 40 (back trailer). so when 10 is entered in a cell in column "A", i want the adjacent cell in column "B" to be 20. However, I don't want the formula written in the cell where the 20 will appear because most of the time other trailer numbers are typed there manually (the trailers that don't need to be together). The problem is that staff keep forgetting to put the necessary trailers together and this is causing problems.

For example,

Day 1.

A1	B1
10	20 (auto)
350 (manually typed)	54 (manually typed)
67 (manually typed)	43 (manually typed)
30	40 (auto)

<TBODY>
</TBODY>

Day 2 -

A1	B1
345	342
56	876
10	20
98	65

<TBODY>
</TBODY>

As you can see from the above example, the trailer possition changes and where the trailers 10 and 20 where entered in day changes on day 2. This is why i can't have the formula in any cell.


The reason why I don't want to put the formula in the cell is because when people manually type a trailer number the formula will be deleted, and people type in both columns "A" and "B".


Is there a way to put the formula that you gave me in conditional formatting or possibly VBA, so that it runs in the background and people can't delete it be typing over it?

regards,

Allan

 

[Teeroy]

Thanks Allan, that was the necessary information to suggest a solution. Since you want either manual or auto entry you will need VBA as you surmised (Conditional Formatting only changes the appearance of a cell, not the contents).

In the Sheet Code Module of the sheet which contains the data put in the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim aFrontTrailer, aRearTrailer
If Target.Column <> 1 Then Exit Sub
aFrontTrailer = Array(10, 30, 50, 70) 'These arrays need to have the same number of elements
aRearTrailer = Array(20, 40, 60, 80)
'On Error GoTo safeExit
If IsError(Application.Match(Target.Value, aFrontTrailer, 0)) Then
    Exit Sub
Else
    Target.Offset(0, 1).Value = aRearTrailer(Application.Match(Target.Value, aFrontTrailer, 0) - 1)
End If
End Sub

The only downside of this type of control is that you lose undo capability after cells which trigger a value change in column B (You can get this back but as I recall it is quite complex to code).

Hope this helps.

Teeroy.

 

[Wawinator]

Thanks Allan, that was the necessary information to suggest a solution. Since you want either manual or auto entry you will need VBA as you surmised (Conditional Formatting only changes the appearance of a cell, not the contents).

In the Sheet Code Module of the sheet which contains the data put in the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim aFrontTrailer, aRearTrailer
If Target.Column <> 1 Then Exit Sub
aFrontTrailer = Array(10, 30, 50, 70) 'These arrays need to have the same number of elements
aRearTrailer = Array(20, 40, 60, 80)
'On Error GoTo safeExit
If IsError(Application.Match(Target.Value, aFrontTrailer, 0)) Then
    Exit Sub
Else
    Target.Offset(0, 1).Value = aRearTrailer(Application.Match(Target.Value, aFrontTrailer, 0) - 1)
End If
End Sub

The only downside of this type of control is that you lose undo capability after cells which trigger a value change in column B (You can get this back but as I recall it is quite complex to code).

Hope this helps.

Teeroy.

Hello Teeroy,

Thank you so much for your time and assistence, very much appreciated. The VBA code works perfectly.

best regards,

Allan

 

[Teeroy]

You are welcome.
BTW you can remove the commented line "On Error GoTo safeExit"; I changed that way I was error trapping and didn't completely clean up the code.