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

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

<colgroup><col><col></colgroup><tbody>
</tbody>

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.