| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add a row depending on cell value
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add a row depending on cell value
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add a row depending on cell value
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add a row depending on cell value
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add a row depending on cell value
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add a row depending on cell value
On Feb 19, 11:47 pm, wrote:
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- 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add a row depending on cell value
On Feb 20, 12:30 am, "excel" wrote:
On Feb 19, 11:47 pm, wrote: 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-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- Hide quoted text - - Show quoted text - Thank you anyway, I have worked it out, I appreciate the help. :)) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add a row depending on cell value
On Feb 20, 1:03 am, "excel" wrote:
On Feb 20, 12:30 am, "excel" wrote: On Feb 19, 11:47 pm, wrote: 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-Hidequoted 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- Hide quoted text - - Show quoted text - 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| colour of a row depending on cell value | Excel Discussion (Misc queries) | |||
| Round one cell depending on value of another | Excel Worksheet Functions | |||
| Copy content of cell to another depending on value of third cell(between worksheets) | Excel Worksheet Functions | |||
| How can i change cell colour depending on month of date in cell? | Excel Discussion (Misc queries) | |||
| How do I modify a row depending upon what a cell value is? | Excel Worksheet Functions | |||
Linear Mode
