Add a row depending on cell value

[excel]

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

 

[Guest]

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?

 

[excel]

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









- 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

 

[micha_delle]

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

 

[micha_delle]

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

 

[excel]

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- Hide quoted text -

- Show quoted text -

Thank you Micheal. firstly dont you mean CTRL/SHIFT/ENTER??
secondly, I can see the first part of the formula, your right, I would
only need it in c1, but the a7,a8 & a9 formulas dont seem to work,
comes up with error saying that >=1, 2 or 3 part of the formula is in
error??

TIA Micheal

 

[excel]

Thank you Micheal. firstly dont you mean CTRL/SHIFT/ENTER??
secondly, I can see the first part of the formula, your right, I would
only need it in c1, but the a7,a8 & a9 formulas dont seem to work,
comes up with error saying that >=1, 2 or 3 part of the formula is in
error??

TIA Micheal- Hide quoted text -

- Show quoted text -

Thank you anyway, I have worked it out, I appreciate the help.

)

 

[micha_delle]

Thank you anyway, I have worked it out, I appreciate the help.

)

i assume it was about the comma instead of a semicolon, wasn't it?

it was a pleasure - M.