text in a cell based off of numbers

[teomi]

This is for a quite complex scheduling spreadsheet I have made.
So, without going into complicated detail:

If I have a cell containing a number (no decimal), and another cell containing simple text, how can I tell excel to the write the text to individual horizontal cells based on the number written in that first cell.

Thanks,
Eitan

[Palmetto]

We probably need more detail to fully understand the requirement, but based on your description . . .

In the target cell to receive the text, perhaps an IF formula will do

=IF(A1=x,B1,"")

Where "x" = is the number value you want to test against, and
B1 is the cell containing the text you want to display if A1 contains the correct number

Is this what ou intended? If not, be more specific and perhaps upload a sample workbook.

[NBVC]

I think it might be more like:

=IF(COLUMNS($A$1:A$1)>A1,B1,"") copied across the full intended horizontal range to use.

where A1 contains the whole number and B1 contains the text string.

so that copied across, when the number of columns exceeds the value of A1, then it will begin to return blanks.

[teomi]

Hi there, the formulas that you suggested don't seem to work. The latter just gives me strings or zeros when stretched across columns.

Here is a better explanation of what I am trying to do:

I am creating a schedule of classes. I want to create a simple weekly timetable where the beginning column represents the Monday of every week, and the columns represent the days of the week.

The number I specified above corresponds to the amount of classes needed to complete a specific course, and the text represents the name of the course in question.

The idea is for me not to need to manually enter the course's name in each cell based on the amount of classes needed but rather Excel to do it automatically based on the given number above.

I hope this clears things up.

Thanks,
Eitan

[NBVC]

I did not make A1 and B1 absolute...

=IF(COLUMNS($A$1:A$1)>$A1,$B1,"")

[teomi]

hmm..still doesn't work.
When I stretch horizontally I only get empty cells, and when I stretch vertically I get zeros...

[NBVC]

Ok... I am not with it today...

Reverse the conditional parts

=IF(COLUMNS($A$1:A$1)>$A1,"",$B1)

[teomi]

Actually if I stretch the formula across a lot of cells, what it does it leaves empty cells till they add up to the number in A1 and then thereafter, if I stretch more, it starts inputting the text in B1 on each cell after that.

So in a way, it does exactly the opposite

Any ideas?

Thanks,
Eitan

[NBVC]

This part:

COLUMNS($A$1:A$1) gives you an incremental count starting at 1...

so as you copy across it changes to 2, 3, 4, etc...

If you have 5 in A1 and "XXX" in B1, then wherever you copy my formula to, should give you an "XXX", then copy it across should give "XXX" in all subsequent cells upto the point where the COLUMNS() function gives result equal to value in A1... then it gives blanks.

Do you have that exact formula or something else?

[teomi]

OK now it works perfectly!!
Now, can I enhance this formula so when stretching it, it will write the text on alternate columns until it reacher the number in A1.

What I mean is when stretching the formula horizontally, it will fill the first column, leave the one next to it blank, and fill the third one, etc., until it reaches the specified number count.

Thanks,
Eitan

[NBVC]

Try perhaps...

=IF(AND(MOD(COLUMNS($A$1:A$1),2)=1,COLUMNS($A$1:A$1)+1<=2*$A1),$B1,"")

[teomi]

Wow...you are definitely da Man
thank you so much!!

[teomi]

one more enhancement...how can I add a number next to the text that is being stretched accross that can serve as a counter: XXX #1, XXX #2 etc.

Thanks again,
Eitan

[NBVC]

Try:

=IF(AND(MOD(COLUMNS($A$1:A$1),2)=1,COLUMNS($A$1:A$1)+1<=2*$A1),$B1 &" #"&(COLUMNS($A$1:A$1)+1)/2,"")

[teomi]

Works like a charm! Thanks a million!!

Eitan

[teomi]

Now here is a real challenge
When I stretch the formula, can it skip to the next row after passing 8 cells in a row?
In other words, fill a table that is constrained to 8 cells horizontally.

Thanks,
Eitan

[NBVC]

You'd be better off getting a vba code to do that... formulas would be too complex...

Repost that specific question in the programming forum.

[teomi]

Just did. Thanks a lot for all your help!!