# Newbie Excel Question

#### Romer

##### New Member
Hi guys,

Im new here and not an expert in Excel and i have a simple question hoping you guys can answer.

 SAP Quote Number​ Previous Quote Number Active Quotes Start Date End Date HSG/12345678 Inactive 1/7/2015 2/7/2016 HSG/12345679 HSG/12345678 Inactive 1/8/2015 2/7/2016 HSG/12345680 HSG/12345679 Active 4/8/2015 2/7/2016 HSG/12345681 25/06/2015 2/7/2016

<tbody>
</tbody>

Im trying to automate the Active Quotes column by doing a formula but i cant seem to figure it out.

Basically if the Column A has a new Quotation it will be active on the start date and it will be deactivated once a new quotation has been created to replace the first quote or if the end date has reach.

Cant seem to figure it out. this is the formula that im using but i dont get the right answer.
=IF(B6>=1,"Active",IF(AND(TODAY()>=N6,TODAY()<=O6),"Active","Inactive"))

Hope you guys can help me.

Thanks & Peace out,

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### gaz_chops

##### Well-known Member
What is B6>=1 for?

#### Romer

##### New Member
Im just trying out if column B has a value then it will be inactive.

I was trying this formula also but its also wrong
=IF(VLOOKUP(B2,A:A,1,FALSE),IF(AND(TODAY()>=N2,TODAY()<=O2),"Active","Inactive"))

#### gaz_chops

##### Well-known Member
Try

=IF(OR(ISNUMBER(MATCH(A2,\$B\$2:\$B\$30,0)),TODAY()<N2,TODAY()>O2),"Inactive","Active")

The "match" checks if the SAP Quote No. appears in Column Previous Quotes.

#### Romer

##### New Member

Thank your for your quick reply gaz but when i tried it it became all inactive. I tried to play around with the dates and still inactive

#### gaz_chops

##### Well-known Member
In the sample you gave in 1st post they are all inactive!

Change the date on the last record, it becomes Active.

Code:
``````[TABLE="width: 466"]
<tbody>[TR]
[TD]SAP Quote Number[/TD]
[TD]Previous Quote Number[/TD]
[TD]Active Quotes[/TD]
[TD][/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]HSG/12345678[/TD]
[TD][/TD]
[TD]Inactive[/TD]
[TD][/TD]
[TD="align: right"]01/07/15[/TD]
[TD="align: right"]02/07/16[/TD]
[/TR]
[TR]
[TD]HSG/12345679[/TD]
[TD]HSG/12345678[/TD]
[TD]Inactive[/TD]
[TD][/TD]
[TD="align: right"]01/08/15[/TD]
[TD="align: right"]02/07/16[/TD]
[/TR]
[TR]
[TD]HSG/12345680[/TD]
[TD]HSG/12345679[/TD]
[TD]Inactive[/TD]
[TD][/TD]
[TD="align: right"]01/07/15[/TD]
[TD="align: right"]02/07/16[/TD]
[/TR]
[TR]
[TD]HSG/12345681[/TD]
[TD][/TD]
[TD]Active[/TD]
[TD][/TD]
[TD="align: right"]01/06/15[/TD]
[TD="align: right"]02/07/16[/TD]
[/TR]
</tbody>[/TABLE]``````

#### Romer

##### New Member

I think i confused you with my example sorry for that mate,

If the quotation date is earlier than today and expire after today it will be active
If the Quotation date is later than today it will be inactive and will be active on the start date
If the SAP Quotation number has been replaced by a new SAP Quote Number then the old Quote number will be inactive and the new SAP Quote number will be active

#### gaz_chops

##### Well-known Member
LOL now I am confused, as that is what my formula should do. Can you post a few more examples with what you expect as Active/Inactive?

#### Romer

##### New Member

Im not sure if you can see my pic. but this is the link

Imgur

http://imgur.com/xfXEGJ5

Thanks alot gaz

#### gaz_chops

##### Well-known Member
All of the Start Dates in your pic are greater than today, or they appear in the Prev Quotes column?

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,331
Messages
5,836,683
Members
430,444
Latest member
WrenchBoy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back