Formula for a range of cells with a certain value to return a specific cell

[kajjampur]

Hi I am quite new to excel formulas. I am trying to make a Roster for my work and have basically made a 14 week roster with Numbers representing persons.

I want a formula to search the range of cells over 14 weeks and return the value according to the Name assigned to the value of number in a different cell.

So If the range is A1:I20 and if any cell in that range has a value =1 then I want that cell then to show the text which I have entered in L2 which could be "Jane"

So if I change the name "Jane" to "John" the values should change accordingly.

At the moment I have a list of 12 names which I would like to use to fill the roster. And I have filled in numbers 1-12 for each name

Any help is greatly appreciated

[L-Drr]

Instead of using a formula you could also just replace (you know in the options search/replace) all the 1's for "Jane". Then later you can still replace "Jane" for "John". Would that do it for you?

Or instead of 1 you could say =L2

[kajjampur]

If I do that then it will also replace the "1" in "10, or 11 or 12" with the name Jane or John.

The reason I think a formula is useful is because then I can change the entire list of the 12 names and still fill the roster without much difficulty

[L-Drr]

Ok so in that case you'll have to create duplicate tables (I think), one for the numbers and one for the names. Is that ok?

[kajjampur]

I have just uploaded a sample excel file showing 12 names in Column L and 2 week roster for 12 people.

I want to be able to change the names in the column L which will reflect in the Roster .

[L-Drr]

Ok so in the attachment I have created a duplicate table with the numbers replaced by names. It's quite a long formula and I'm sure it can be done shorter, but hey it works
Now you only have to copy the other (constant) data from the old table to the new.

Does that work for you?

[popipipo]

Please Login or Register  to view this content.

Try this formula

[kajjampur]

yes it does work, but can you please tell me how you managed to that as I have to try to do that for 14 more weeks, and I just want to know how to do it, and I shall try a smaller formula, but this works great

[L-Drr]

Are you asking about my formula or popipipo's? Anyway, mine works as follows:

I check if the value is a 1, if yes then it displays the first name in your names table, if not then it checks of the value is 2 et cetera.
Basically the same as what popipipo's formula does but his is much easier

[kajjampur]

Are you asking about my formula or popipipo's? Anyway, mine works as follows:

I check if the value is a 1, if yes then it displays the first name in your names table, if not then it checks of the value is 2 et cetera.
Basically the same as what popipipo's formula does but his is much easier

Thanks to both of you..I just cant figure out how exactly did you make the second set of data and put in the formula in each cell.

[L-Drr]

You only have to write the formula once, e.g. in M3. Then if you select M3, you can drag it (click-and-hold the bottom right corner of the selected cell) sideways and downwards. Cell references in the formula that look like "B3" are changed automatically while dragging. Cell references like "$B$3" will stay the same when dragged.