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]

Where are the black text values coming from in Column B... another column other than Column A?

 

[Stan_C]

They are just copied, starting off b1=a1 b2-b15 will always be the same b16 repeats the process just changing b16=a2.

 

[Stan_C]

I found a work around for me anyways, simple. Just added an extra row onto each table and this works:

=HLOOKUP($A$1,$A$1:$A$301,ROW(B16)/16+1,FALSE)

Would like to know if there is a better way or well a logical explanation of what I need to think of??

 

[Rick Rothstein]

I found a work around for me anyways, simple. Just added an extra row onto each table and this works:

=HLOOKUP($A$1,$A$1:$A$301,ROW(B16)/16+1,FALSE)

Would like to know if there is a better way or well a logical explanation of what I need to think of??

I asked you about where the black text values came from because I thought you wanted to put a formula in Column B and copy it down, getting the red text from Column A and the black text from some place else. But the formula you posted makes it sound like you are putting it in cells B1, B16, B31 etc. manually... is that correct? I'll ask again, in case not... where did the black text values come from... another column via a formula (if so, what formula) or are they just typed in values?

 

[Robert Mika]

Another way would be
=INDEX($A$1:$A$301,ROWS($B$1:B15)/15) without adding extra row.
The second part of INDEX (which is the row return)as you go down is diveded by 15 so the results are
15/15=1
16/15= 1.06666
....
returning first postion in column A.
in row 16 we have
30/15=2

returning second position in column B.
and so on.

 

[Stan_C]

I asked you about where the black text values came from because I thought you wanted to put a formula in Column B and copy it down, getting the red text from Column A and the black text from some place else. But the formula you posted makes it sound like you are putting it in cells B1, B16, B31 etc. manually... is that correct? I'll ask again, in case not... where did the black text values come from... another column via a formula (if so, what formula) or are they just typed in values?

I am sorry, I am trying to get the formula correct and the "yes" I am manually putting the formula by copy and paste.

 

[Rick Rothstein]

I am sorry, I am trying to get the formula correct and the "yes" I am manually putting the formula by copy and paste.

Since it will be a static list in Column B, why not just put the direct cell reference in the cells directly?

B1: =A1
B16: =A2
B31: =A3
etc.

 

[Stan_C]

Another way would be
=INDEX($A$1:$A$301,ROWS($B$1:B15)/15) without adding extra row.
The second part of INDEX (which is the row return)as you go down is diveded by 15 so the results are
15/15=1
16/15= 1.06666
....
returning first postion in column A.
in row 16 we have
30/15=2

returning second position in column B.
and so on.

You talk about the 2nd part.... have to dived by 16. You are saying to manually input this where the formula will not work? Because that formula will produce a fraction that will not compute correctly going down. That was my original formula, well close to.

 

[Stan_C]

Since it will be a static list in Column B, why not just put the direct cell reference in the cells directly?

B1: =A1
B16: =A2
B31: =A3
etc.

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.