Text to next Cell...

[Stan_C]

I came up with a formula for this and I am probably over thinking this but, it fails about 500 cells down.

Col. (A) I have Item data that are names: Col. (B) I have data but would like b1=a1, b16=a2, b31=a3

So with column (B) +15 rows to = (A) +1

[TABLE="width: 513"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]2% Milk[/TD]
[TD]2% Milk[/TD]
[/TR]
[TR]
[TD]Ambrosia[/TD]
[TD]Regular [/TD]
[/TR]
[TR]
[TD]American Chop Suey[/TD]
[TD]3-4gm Na (NAS)[/TD]
[/TR]
[TR]
[TD]Apple Pie[/TD]
[TD]2gm Na[/TD]
[/TR]
[TR]
[TD]Bacon Strip[/TD]
[TD]Low Fat/Low Chol[/TD]
[/TR]
[TR]
[TD]Baked Beans[/TD]
[TD]Fiber Restricted[/TD]
[/TR]
[TR]
[TD]Baked Chicken Breast[/TD]
[TD]Consistent Carb[/TD]
[/TR]
[TR]
[TD]Baked Ham Pineapple Sauce[/TD]
[TD]Cardiac Diabetic[/TD]
[/TR]
[TR]
[TD]BBQ Pork on Bun[/TD]
[TD]2 gm K[/TD]
[/TR]
[TR]
[TD]Beef Barley & Veg Soup[/TD]
[TD]Lactose Restricted[/TD]
[/TR]
[TR]
[TD]Beef Lasagna[/TD]
[TD]Vegetarian LactoOvo[/TD]
[/TR]
[TR]
[TD]Beef Liver & Onions[/TD]
[TD]Gluten Restricted[/TD]
[/TR]
[TR]
[TD]Braised Cabbage[/TD]
[TD]Renal CSC[/TD]
[/TR]
[TR]
[TD]Bread Pudding[/TD]
[TD]Small Portion[/TD]
[/TR]
[TR]
[TD]Broccoli[/TD]
[TD]Large Portion[/TD]
[/TR]
[TR]
[TD]Broccoli & Cheese Quiche[/TD]
[TD]Ambrosia[/TD]
[/TR]
[TR]
[TD]Brown Bread[/TD]
[TD]Regular [/TD]
[/TR]
[TR]
[TD]Brownie[/TD]
[TD]3-4gm Na (NAS)[/TD]
[/TR]
[TR]
[TD]Brussels Sprouts[/TD]
[TD]2gm Na[/TD]
[/TR]
[TR]
[TD]Carnival Cake[/TD]
[TD]Low Fat/Low Chol[/TD]
[/TR]
[TR]
[TD]Carrot Bisque[/TD]
[TD]Fiber Restricted[/TD]
[/TR]
[TR]
[TD]Carrot Raisin Salad[/TD]
[TD]Consistent Carb[/TD]
[/TR]
[TR]
[TD]Cheese Omelet[/TD]
[TD]Cardiac Diabetic[/TD]
[/TR]
[TR]
[TD]Chicken Patty[/TD]
[TD]2 gm K[/TD]
[/TR]
[TR]
[TD]Chicken Rice Soup[/TD]
[TD]Lactose Restricted[/TD]
[/TR]
[TR]
[TD]Chicken Tenders[/TD]
[TD]Vegetarian LactoOvo[/TD]
[/TR]
[TR]
[TD]Chilled Pineapple[/TD]
[TD]Gluten Restricted[/TD]
[/TR]
[TR]
[TD]Cole Slaw[/TD]
[TD]Renal CSC[/TD]
[/TR]
[TR]
[TD]Coleslaw[/TD]
[TD]Small Portion[/TD]
[/TR]
[TR]
[TD]Cornbread[/TD]
[TD]Large Portion[/TD]
[/TR]
[TR]
[TD]Cottage Cheese Fruit Plate[/TD]
[TD]American Chop Suey[/TD]
[/TR]
[TR]
[TD]Cream of Wheat[/TD]
[TD]Regular [/TD]
[/TR]
[TR]
[TD]Diced Beets[/TD]
[TD]3-4gm Na (NAS)[/TD]
[/TR]
[TR]
[TD]Egg Salad Sandwich[/TD]
[TD]2gm Na[/TD]
[/TR]
[TR]
[TD]Eggplant Roulette w/ Cheese[/TD]
[TD]Low Fat/Low Chol[/TD]
[/TR]
[TR]
[TD]French Dip Sandwich[/TD]
[TD]Fiber Restricted[/TD]
[/TR]
[TR]
[TD]French Fries[/TD]
[TD]Consistent Carb[/TD]
[/TR]
[TR]
[TD]French Toast Slice w/ Syrup[/TD]
[TD]Cardiac Diabetic[/TD]
[/TR]
[TR]
[TD]Fresh Whole Banana[/TD]
[TD]2 gm K[/TD]
[/TR]
[TR]
[TD]Fruit Topping[/TD]
[TD]Lactose Restricted[/TD]
[/TR]
[TR]
[TD]Garden Salad - Italian Dressing[/TD]
[TD]Vegetarian LactoOvo[/TD]
[/TR]
[TR]
[TD]Ginger Cookies[/TD]
[TD]Gluten Restricted[/TD]
[/TR]
[TR]
[TD]Gravy[/TD]
[TD]Renal CSC[/TD]
[/TR]
[TR]
[TD]Green Beans[/TD]
[TD]Small Portion[/TD]
[/TR]
[TR]
[TD]Grilled Cheese Sandwich[/TD]
[TD]Large Portion[/TD]
[/TR]
[TR]
[TD]Ham Salad Sandwich[/TD]
[TD]Apple Pie[/TD]
[/TR]
</tbody>[/TABLE]


I hope this makes sense and sorry for the bad table, my Excel to Forum add on is on my other laptop.

Thank you

 

[Rick Rothstein]

Because I have to go down about 400 times..... some like 6,000 cells and do not want to scroll or input data like that over and over again.

Okay, I'm confused (again). For some reason you and I are on different wavelengths regarding the black text values in Column B, so I'll ask again... where did they come from... another column via a formula or did you type them in? Maybe if I ask this question, I'll get the answer I need in order to answer your original question... select cell B3... look at the Formula Bar... post what you see in the Formula Bar in a response to this thread.

 

[Stan_C]

Okay, I'm confused (again). For some reason you and I are on different wavelengths regarding the black text values in Column B, so I'll ask again... where did they come from... another column via a formula or did you type them in? Maybe if I ask this question, I'll get the answer I need in order to answer your original question... select cell B3... look at the Formula Bar... post what you see in the Formula Bar in a response to this thread.

Okay..... The only cells that will have a formula are the cells b1, b16 etc....... The cells in between are just copied cells, hand typed.

 

[Rick Rothstein]

Okay..... The only cells that will have a formula are the cells b1, b16 etc....... The cells in between are just copied cells, hand typed.

Then there is no formula solution possible besides the one I gave you (there is a VB solution... more about that in a second). Perhaps you are not aware, but a cell can only contain one thing... either a constant (think hand-typed or copy/pasted) or a formula, but not both. This means there is no way to put a formula in a cell and copy it down without destroying the contents of the cell being copied over. So, you cannot have "Regular" in B2, put a formula in B1 that reproduces A1, copy the formula down and still have the text constant "Regular" in B2. The only way that would work (and the reason behind my insistence on where the black text values in Column B came from) is if the text in B2:B15, B17:B30:B32:etc came from a formula itself... then we could produce a combination formula that would still retrieve the text they originally came from for those cells and pickup the values from Column A for the rows you indicated in your first message. Anyway, here is a VB macro solution that will insert the appropriate formulas in B1, B16, B31, etc. while leaving the existing values in the other cells of Column B alone...

Sub InsertColumnAValuesEvery15RowsInColumnB()
  Dim X As Long, LastRowA As Long, LastRowB As Long
  Const StartRow As Long = 1
  LastRowA = Cells(Rows.Count, "A").End(xlUp).Row
  LastRowB = Cells(Rows.Count, "B").End(xlUp).Row
  For X = StartRow To LastRowA
    If X < LastRowB Then
      Cells(15 * X - 14, "B").Formula = "=A" & X
    End If
  Next
End Sub

A couple of notes about this function. Because you did say, I had to guess... one, the formula inserted into B1, B16, B31, etc. overwrites the value currently existing in that cell (that is, the exising cell value is lost) and, two, no formulas are inserted into any cells past the row containing the last piece of data in Column B. If these restictions are not right for you, post back telling us what should happen and I'll adjust the code accordingly.

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (InsertColumnAValuesEvery15RowsInColumnB) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. To make things simpler for you (especially if you will need to call this macro often), when you select the macro from the list, before clicking Run, you can click the Options button and assign a keyboard shortcut to it first and then use that keyboard shortcut the next time you want to run the macro.

 

[Stan_C]

Then there is no formula solution possible besides the one I gave you (there is a VB solution... more about that in a second). Perhaps you are not aware, but a cell can only contain one thing... either a constant (think hand-typed or copy/pasted) or a formula, but not both. This means there is no way to put a formula in a cell and copy it down without destroying the contents of the cell being copied over. So, you cannot have "Regular" in B2, put a formula in B1 that reproduces A1, copy the formula down and still have the text constant "Regular" in B2. The only way that would work (and the reason behind my insistence on where the black text values in Column B came from) is if the text in B2:B15, B17:B30:B32:etc came from a formula itself... then we could produce a combination formula that would still retrieve the text they originally came from for those cells and pickup the values from Column A for the rows you indicated in your first message. Anyway, here is a VB macro solution that will insert the appropriate formulas in B1, B16, B31, etc. while leaving the existing values in the other cells of Column B alone...

Sub InsertColumnAValuesEvery15RowsInColumnB()
  Dim X As Long, LastRowA As Long, LastRowB As Long
  Const StartRow As Long = 1
  LastRowA = Cells(Rows.Count, "A").End(xlUp).Row
  LastRowB = Cells(Rows.Count, "B").End(xlUp).Row
  For X = StartRow To LastRowA
    If X < LastRowB Then
      Cells(15 * X - 14, "B").Formula = "=A" & X
    End If
  Next
End Sub

A couple of notes about this function. Because you did say, I had to guess... one, the formula inserted into B1, B16, B31, etc. overwrites the value currently existing in that cell (that is, the exising cell value is lost) and, two, no formulas are inserted into any cells past the row containing the last piece of data in Column B. If these restictions are not right for you, post back telling us what should happen and I'll adjust the code accordingly.

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (InsertColumnAValuesEvery15RowsInColumnB) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. To make things simpler for you (especially if you will need to call this macro often), when you select the macro from the list, before clicking Run, you can click the Options button and assign a keyboard shortcut to it first and then use that keyboard shortcut the next time you want to run the macro.

I thank you for the work that you have input into this question. I have figured my issue out, stuck with the simple solution of adding another row to make the division even so the formula will not mess up past 500.v Again thank you!