Formula Assistance Not working

[chrisguk]

Hi All,

My guess is that this is going to be a very easy question to answer, but my brain is fried over this.

I have two columns as follows

Column B3 (Could have values like 00,03,04,05,06,07,08)

Column D3 has a date value

I need various combinations to be something like:

if b3 is 03 then output "GB 03 DOM" but if the column D3 has a date then output "GB AMR"

I guess thats simple but in the same formula statement i need it to check the other values in B3 too. I am thinking an If AND OR

Help is appreciated

 

[sykes]

You've not specified where you want the result to be displayed, but I put this into C3, then copied down with the fill handle...
=IF(D3<>"","GB AMR","GB " & B3 & " DOM")

 

[sykes]

PS
This relies on there being something in col D (we can only assume it's a date) or that the other col D cells are blank - which is what you've inferred.

Let us know if it works.....

 

[chrisguk]

Hi there,

Many thanks for your reply and I like your solution. I have pasted below an example of what I am attempting to achieve:

<TABLE style="WIDTH: 355pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=472><COLGROUP><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4900" width=134><COL style="WIDTH: 153pt; mso-width-source: userset; mso-width-alt: 7460" width=204><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4900" width=134><TBODY><TR style="HEIGHT: 30pt; mso-height-source: userset" height=40><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #4f6228; WIDTH: 101pt; HEIGHT: 30pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2792077 class=xl32910 height=40 width=134>Profile</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #4f6228; WIDTH: 153pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32910 width=204>Classification</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #4f6228; WIDTH: 101pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32913 width=134>AMR Active date</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911 height=20>03</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911>GB 03 (Non Domestic)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32912> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911 height=20>03</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911>GB 03 (Non Domestic)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32912> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911 height=20>03</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911>GB 03 (Non Domestic)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32912> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911 height=20>04</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911>GB 04 (Non Domestic)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32912> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911 height=20>03</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911>GB 03 (Non Domestic)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32912> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911 height=20>03</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911>GB 03 (Non Domestic)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32912> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911 height=20>03</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911>GB 03 (Non Domestic)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32912> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911 height=20>03</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911>GB 03 (Non Domestic)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32912> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911 height=20>03</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911>GB 03 (Non Domestic)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32912> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911 height=20>03</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911>GB 03 (Non Domestic)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32912> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911 height=20>03</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911>GB 03 (Non Domestic)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32912> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911 height=20>03</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911>GB 03 (Non Domestic)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32912> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911 height=20>03</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911>GB 03 (Non Domestic)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32912> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911 height=20>03</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911>GB 03 (Non Domestic)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32912> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911 height=20>08</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911>GB Remotely Read AMR Meters</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32912>30/11/2007</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911 height=20>00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911>GB 00 (HH Metered)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32912> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911 height=20>08</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911>GB 08 (Non Domestic)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32912> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911 height=20>06</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32911>GB 06 (Non Domestic)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl32912> </TD></TR></TBODY></TABLE>

So what Im looking for is the classification column to auto populate

 

[Peter_SSs]

Is this what you mean? Formula in B2 copied down.

Excel Workbook
	A	B	C
1	Profile	Classification	AMR Active date
2	03	GB 03 (Non Domestic)
3	03	GB 03 (Non Domestic)
4	03	GB 03 (Non Domestic)
5	04	GB 04 (Non Domestic)
6	03	GB 03 (Non Domestic)
7	03	GB 03 (Non Domestic)
8	03	GB 03 (Non Domestic)
9	03	GB 03 (Non Domestic)
10	03	GB 03 (Non Domestic)
11	03	GB 03 (Non Domestic)
12	03	GB 03 (Non Domestic)
13	03	GB 03 (Non Domestic)
14	03	GB 03 (Non Domestic)
15	03	GB 03 (Non Domestic)
16	08	GB Remotely Read AMR Meters	30/11/07
17	00	GB 00 (HH Metered)
18	08	GB 08 (Non Domestic)
19	06	GB 06 (Non Domestic)
chrisguk

 

[chrisguk]

Absolutely perfect many thanks for your help Peter

 

[Peter_SSs]

Absolutely perfect many thanks for your help Peter

Cheers, thanks for the feedback.

One other option is to provide a small table elsewhere on the sheet (you could hide the column if you want) then use this shortened formula.

Excel Workbook
	A	B	C	D	E
1	Profile	Classification	AMR Active date		Formula Data (Hide?)
2	03	GB 03 (Non Domestic)			GB Remotely Read AMR Meters
3	03	GB 03 (Non Domestic)			GB ^ (HH Metered)
4	03	GB 03 (Non Domestic)			GB ^ (Non Domestic)
5	04	GB 04 (Non Domestic)
6	03	GB 03 (Non Domestic)
7	03	GB 03 (Non Domestic)
8	03	GB 03 (Non Domestic)
9	03	GB 03 (Non Domestic)
10	03	GB 03 (Non Domestic)
11	03	GB 03 (Non Domestic)
12	03	GB 03 (Non Domestic)
13	03	GB 03 (Non Domestic)
14	03	GB 03 (Non Domestic)
15	03	GB 03 (Non Domestic)
16	08	GB Remotely Read AMR Meters	30/11/07
17	00	GB 00 (HH Metered)
18	08	GB 08 (Non Domestic)
19	06	GB 06 (Non Domestic)
chrisguk

 

[chrisguk]

Hi Peter,

Thats another great example, I wonder if you were able to help me learn the code you have provided by breaking it down and explaining each element so that hopefully I can offer assistance in the future.

Chris

 

[Peter_SSs]

Hi Peter,

Thats another great example, I wonder if you were able to help me learn the code you have provided by breaking it down and explaining each element so that hopefully I can offer assistance in the future.

Chris

I'll try

=IF(C2, ...

In an IF statement the first part performs a test and what happens after that depends on whether the test is TRUE or FALSE. In such a test if the result is numerical, a zero converts to FALSE and any other number converts to TRUE. So my test is to look at column C. If column C is empty this evaluates to a 0 and therefore FALSE. If column C contains a date then this is a number other than 0 (Excel stores dates as numbers) and therefore converts to TRUE.

So if col C is a date (TRUE) then the part that gets executed is E$2 or "GB Remotely Read AMR Meters", as you see in row 16.

If col C is empty (FALSE) then the part that gets executed is
SUBSTITUTE(IF(A2="00",E$3,E$4),"^",A2)

SUBSTITUTE takes a string, looks for another string within the original string and, if found, replaces the searched for string with another.

In our case the original string depends on another IF statement - whether the col A value is "00" or some other 'number'. If col A "00" the original string is E$3 "GB ^ (HH Metered)" otherwise the original string is E$4 "GB ^ (Non Domestic)".

Whichever of these becomes the 'original string' the SUBSTITUTE searches for "^" and replaces that with the 'number' from col A.

Hope that makes some sense to you.

 

[chrisguk]

Hi Peter,

Wow yes I get and finally I have learned something useful. Many thanks