On 19 Feb., 11:27, wrote:
On 19 Feb., 09:53, "excel" wrote:



On Feb 19, 9:14 pm, ShaneDevenshire

wrote:
As I read your problem, you will need to use VBA, is that acceptable? If so,
do you mean by add a row that you want to insert a row?

--
Thanks,
Shane Devenshire

"excel" wrote:
It would seem I wasnt thorough enough with my question, my apologies.

What I need is this. I have 6 Rows numbered 1-6 and in the adjacent
cells (b1:b6) i enter a value. It may be a number or it may be text.
I need to add a row underneath (numbered 7,8 & 9 as required) if
anything but a numeric value is entered in b1:b6. I hope this example
will help.

A B
1 1 3
2 2 22
3 3 ab
4 4 2
5 5 65
6 6 mon
7 7 << row added awaiting data
8 8 << row added awaiting data

Sorry for not being thorough with it first time

TIA
Esra- Hide quoted text -

- Show quoted text -

I dont need to add a row, I already have the (up to) 3 rows there, I
just need them numbered IN CASE they will need to be used, no
numbering if they dont need to be used.

hope that helps.
TIA

Hi,

a very quick and dirty way would be to enter in cell C1 the formula
=ISTEXT(B1). Drag (auto-fill) it down to C6. You can hide this column
if possible. If you do enter the following formulas it should work:
a7 =IF(COUNTIF($C$1:$C$6;TRUE)=1;A6+1;"")
a8 =IF(COUNTIF($C$1:$C$6;TRUE)=2;A7+1;"")
a9 =IF(COUNTIF($C$1:$C$6;TRUE)=3;A8+1;"")

I hope it works the way you wanted it to work.
It's most likely not the most effective way - let me think about a
more elegant approach (without the ISTEXT-behaviour), ut as i said
"quick-and-dirty".

Michael

and here it goes - sorry, it took a moment:

instead of having the column C you need only one single cell (e.g. c1)
containing the formula {=SUM(--(ISTEXT(B1:B6)))} (enter the formula
using CTRL-ALT-ENTER!). Now change you formulas in a7, a8 and a9
accordingly:
a7 =IF(C1=1;A6+1;"")
a8 =IF(C1=2;A7+1;"")
a9 =IF(C1=3;A8+1;"")

hope this is more suitable...

Michael

http://bereichverschieben.blogspot.com