hmm..

[Don Juan]

I am trying to :


display a name into a cell from another page... =Roster!a1

This formula is fine for only pulling from one source.

But what if I wanted the option of changing the name on the cell with the formula =roster!a1.... i cant without messing up the formula. what is the most effecient way of doing this?

Perhaps refrence the cell w/ the formula to another empty cell so that if a name is typed into the second cell it automatically overrides =roster!a1 ??

However its done I want the option of changing the name without messing up the formula.

[Bryan Hessey]

Knowing your situation I would suggest a new sheet = Override, and then
use =If(Override!A1<>"",Override!A1,Roster!A1)

You can then manually amend any cell by fixing the Override sheet and/or clearing that sheet.

hth
---

I am trying to :


display a name into a cell from another page... =Roster!a1

This formula is fine for only pulling from one source.

But what if I wanted the option of changing the name on the cell with the formula =roster!a1.... i cant without messing up the formula. what is the most effecient way of doing this?

Perhaps refrence the cell w/ the formula to another empty cell so that if a name is typed into the second cell it automatically overrides =roster!a1 ??

However its done I want the option of changing the name without messing up the formula.

[Don Juan]

why does it keep asking me to update the cell value and pop up a open file box like it wants me to select a new file from my comp?

=If(Override!A1<>"",Override!A1,Roster!A1)

also does this get posted in the watch bill where the name should be displayed?

[Bryan Hessey]

The formula
=If(Override!A1<>"",Override!A1,Roster!A1)
gets amended and posted to the places where previously the formula
=Roster!A1
existed. (with columns and rows adjusted accordingly, cell A1 is the point used in a Forum)

I have no idea why your Excel would try to open a file, do you have a sheet called Override and a sheet Roster ?
If so check the spelling, and test it by reducing the formula to
=Roster!A1
then
=Override!A1
to see which has a fault.

---

why does it keep asking me to update the cell value and pop up a open file box like it wants me to select a new file from my comp?

=If(Override!A1<>"",Override!A1,Roster!A1)

also does this get posted in the watch bill where the name should be displayed?

[Don Juan]

I posted the formula as you said into the watchbill worksheet where the name and formula were (c15). I change the a1 in the formula to c3, which is the cell on the override sheet that corresponds to the cell recieving =IF(Override!C3<>"",Override!C3,Roster!A12). Perhaps I did it wrong. It will display the name of the override command in the override sheet to the watchbill but it will not randomly generate, even if there isn't a name posted in the override cell (override!c3). It just displays the same name in cell roster!a12 without changing. I uploaded the workbook so you can have a better idea of what i'm refering if im not making any sense.

[Bryan Hessey]

The question asked was "But what if I wanted the option of changing the name on the cell with the formula =roster!a1" for which I assumed that you wanted to manually override the normal random Roster selected name.

Firstly, it might be better to make the Override sheet a direct copy of WatchBill, then you will be using the same cells in each, it's much less confusing for you.

If you want the Override entry to be a different random generation then you will need to enter that formula in the relavant cell of the Override sheet. Again, you need to work out what constitutes the making of an entry in the Override.


Also, on the Shuffle sheet, if you enter the number of available people in G1, (7 people, put a 6 there) and change the formula in A1 from
=OFFSET(Roster!F$12,$E1,0)
to
=OFFSET(Roster!F$12,MOD($E1,G$1-1)+1,0)
and drag this down the A column you will avoid the zero entries in column A.

Alternately you can have the system count the people with (in G1)
=50-11-COUNTBLANK(Roster!F12:F50)-1

where 50 is your last row, 12 is your first (inclusive) row, and -6 is required for the Mod = 0

---

I posted the formula as you said into the watchbill worksheet where the name and formula were (c15). I change the a1 in the formula to c3, which is the cell on the override sheet that corresponds to the cell recieving =IF(Override!C3<>"",Override!C3,Roster!A12). Perhaps I did it wrong. It will display the name of the override command in the override sheet to the watchbill but it will not randomly generate, even if there isn't a name posted in the override cell (override!c3). It just displays the same name in cell roster!a12 without changing. I uploaded the workbook so you can have a better idea of what i'm refering if im not making any sense.

[Don Juan]

lol,

wow, i'm a goofball.

I just realized your formula works like I wanted it too, I just put a12, instead of a70 for the generation. Yes, I wanted to be able to generate randomly first, but then customize later. I think you understood me, I just got confused. I trying to figure this out I came up with another way to do it. I think yours is more effecient though.


assign a value to the name in override!c15 and display something like, =IF(override!c15>0, override!c15, roster!a12)

or combine a word search routine in the watchbill c15 cell where the name is displayed to look for other names to its corresponding cell in override c15.

would that work?

[Bryan Hessey]

No, the
=IF(override!c15>0, override!c15, roster!a12)

would need to be
=IF(override!c15>0, offset(roster!a12,override!c15-1,0), roster!a12)
or
=IF(override!c15>0, offset(roster!a70,override!c15-1,0), roster!a12)
otherwise you will have a number entered in your Watchbill.

Personally I would use the straight
=IF(override!c15<>"", override!c15, roster!a12)
in the Watchbill and the Override you can put either the direct name
Mandrake
or
=Roster!a70
or the random (set the number viz 4 as required)
=Offset(Roster!a70,4,0)

as this will give you direct control over the optional second random override (ie, as a last resort you can assign Mandrake to a specific additional post).

----

lol,

wow, i'm a goofball.

I just realized your formula works like I wanted it too, I just put a12, instead of a70 for the generation. Yes, I wanted to be able to generate randomly first, but then customize later. I think you understood me, I just got confused. I trying to figure this out I came up with another way to do it. I think yours is more effecient though.


assign a value to the name in override!c15 and display something like, =IF(override!c15>0, override!c15, roster!a12)

or combine a word search routine in the watchbill c15 cell where the name is displayed to look for other names to its corresponding cell in override c15.

would that work?

[Don Juan]

bryan man,

you guys blow me away with your knowledge of excel. The more I dig into this stuff the more I realize how much time and energy you guys have invested into this program. Thanks for the support.