If cell is blank Then ...

Just Marty

New Member
Joined
Dec 26, 2010
Messages
13
Been trying to figure this out and just can't seem to get it -

In Col A some rows have an text entry and some are blank
Col B has a numerical value

What I need to do is:

If Col A is blank and Col B has a value then insert in Col A, starting with the number 1, increasing by 1, a sequential number

If Col A is blank and Col B is blank - end of file

Excel 2000 Windows 7
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

kkfighter

New Member
Joined
Mar 10, 2011
Messages
10
Hi,

Imagine from excel, imput at C2 and C3 and the just copy C3 down.

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Col A</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64>Col B</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64>Formulae</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>1</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>2</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>3</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>2</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">End of File</TD></TR></TBODY></TABLE>
First cell = =IF(AND(A2="",B2=""),"End of File",1)
Second cell =IF(AND(A3="",B3=""),"End of File",1+C2)
Smile,
KK
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
If you haven't resolved this, any chance of a small set of 'before' and 'after' data?
My signature block below contains 3 methods for posting small screen shots. Test them in the Test Here forum. That way, if something goes wrong, you won’t be messing up a main forum.
 

Just Marty

New Member
Joined
Dec 26, 2010
Messages
13
I tried the 1st response and did not get what I was wanting - it was not increasing the number in col A and put a number if Col A even if it was not blank

below is a sample of what I have and what I would like

before
Excel Workbook
AB
1
20.004628
30.014563
40.091943
5TEST
60.148121
70.169802
8TEST
90.182649
100.230493
110.2718
120.390628
13
14
Sheet1 (2)
Excel Workbook
AB
1
210.004628
310.014563
430.091943
5TEST
640.148121
750.169802
8TEST
960.182649
1070.230493
1180.2718
1290.390628
13
14
Excel 2000 this is what I would like it to be Sheet1 (2)
Excel 2000



any help would be much appreciated -
I will be back later to check in

Marty
 

Just Marty

New Member
Joined
Dec 26, 2010
Messages
13

ADVERTISEMENT

Even after previewing before I posted there is an error in the sample of what I was wanting

Col A row 2 should be 1

Col A row 3 should be 2

Sorry for the oops!
 

Haseeb Avarakkan

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
Try,

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0.004628</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0.014563</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0.091943</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">TEST</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">4</td><td style="text-align: right;;">0.148121</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">5</td><td style="text-align: right;;">0.169802</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">TEST</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">6</td><td style="text-align: right;;">0.182649</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">7</td><td style="text-align: right;;">0.230493</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">8</td><td style="text-align: right;;">0.2718</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0.390628</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A2</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">N(<font color="Green">B3</font>),B2=""</font>),"TEST",IF(<font color="Red">N(<font color="Green">B2</font>),MAX(<font color="Green">A$1:A1</font>)+1,""</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Copy down...
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Maybe this.

1. Select column A by clicking its heading label.

2. Edit|Go To...|Special...|Blanks|OK

3. Type (or paste) this formula (see Note below)
=COUNT(B$1:B1)
and confirm that formula with Ctrl+Enter, not just Enter

4. Delete the '0' formula in A1 if you want.

Note: If A1 actually contains something (eg heading) then after step 2 the active cell would be A2, not A1. In that case the formula you typed should still refer to the active cell row. So it would be this instead:
=COUNT(B$2:B2)
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Excel Workbook
AB
210.004628
320.014563
430.091944
5TEST*
640.148122
750.169803
8TEST*
960.182649
1070.230493
1180.271812
1290.390628
Sheet1
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,511
Messages
5,602,081
Members
414,500
Latest member
kevdragon1

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top