Text to next Cell...

Stan_C

Active Member
Joined
Feb 10, 2012
Messages
353
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Where are the black text values coming from in Column B... another column other than Column A?
 
Upvote 0
They are just copied, starting off b1=a1 b2-b15 will always be the same b16 repeats the process just changing b16=a2.
 
Upvote 0
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??
 
Upvote 0
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?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top