Named Ranges

[Guest]

Hi,

I have a series of named ranges in Excel called Game_1_Paid?, Game_2_Paid?
through to Game_100_Paid?.

Now, I want to include these named ranges in a VLOOKUP or SUMPRODUCT
function, which I can copy and paste down - I need it to start in E3 with:

=IF(SUMPRODUCT((Player_Names=$B$1)*(C3<>"")),"Yes","No")

where the players name is in $B$1 and the search on Game_1_Paid? will
determine whether the player has paid (i.e. not blank), or not paid (i.e.
blank).

C3 holds the formula:

="Game_"&TRIM(MID(B3,FIND("e",B3)+1,LEN(B3)-FIND("e",B3)))&"_Paid?"

and has been filled down through to C102

where B3 holds

Game 1

and is filled down through B102 so that B102 holds Game 100.

Thus effectively C3 holds: Game_1_Paid?
and C4 would hold: Game_2_Paid?


I want to copy the formula in E3 down through E4:E102 - any suggestions
because it's not working? (or have I attempted this from completely the wrong
angle)?

Many Thanks,

Gary T.

 

[Debra Dalgleish]

Use the Indirect function in cell C3, e.g.:


=INDIRECT("Game_"&TRIM(MID(B3,FIND("e",B3)+1,LEN(B3)-FIND("e",B3)))&"_Paid?")

And in E3, look for non-zero values:

=IF(SUMPRODUCT((Player_Names=$B$1)*(C3<>0)),"Yes","No")

 

[Harlan Grove]

I have a series of named ranges in Excel called Game_1_Paid?, Game_2_Paid?
through to Game_100_Paid?.

....

I just tested this, and Excel does allow ? in names, but online help implies
that character shouldn't be allowed in names. This could be something
Microsoft could change in some later version of Excel, so no something you
should rely on.

C3 holds the formula:

="Game_"&TRIM(MID(B3,FIND("e",B3)+1,LEN(B3)-FIND("e",B3)))&"_Paid?"

and has been filled down through to C102

where B3 holds

Game 1

and is filled down through B102 so that B102 holds Game 100.

Thus effectively C3 holds: Game_1_Paid?
and C4 would hold: Game_2_Paid?

You need to use INDIRECT, but if B3:B102 are as you describe, then you could
use the following formula in C3.

=INDIRECT(SUBSTITUTE(B3," ","_")&"_Paid?")