I cannot have any equations in the B column. I need to be able to
enter what level/letter the character is in that column. I also need
to be able to enter a P in the D column if dues had been paid. C is
the only column that can have any equations in it.
Steven
Mark cells I3:I14
Cut
Move cursor to cell F3, right click>Insert Cut Cells
In B2 enter
=VLOOKUP(B2,$F$3:$I$14,4,0)
Mark cells C2:C14
Format>Conditional Formatting>use drop down to select Formula Is>
=$D2="" >Format>Select Red font>OK
--
Regards
Roger Govier
As you all can tell, I stink at explaining what I want. So I have
attached an example with a bit more explanation. There are no
macros or anthing else and I am virus free (that is one thing I am
good at, security and safety) so it is safe to view it. I hope
that one of you can give me a formula for the dues column that
will accomplish this. Let me say that I really appreciate the help
that has been given so far. You guys are great.
TIA
Steven
Hi Steven
Slight problem with terminology
Single digit would imply 0 to 9, not 0 to 60 as once we get past
9 we
would have 2 digits in the cell.
Single digit A to L should be single character, A to L as these
are
alpha characters not digits.
If you decide to go with the digit entry, then the first of my 2
solutions will work fine whether you type 6 or 23 or any other
number
into the cell.
If you are going to go the text route, then either single
characters A
through L or "1 -5", "6 - 10" will work using the second method.
just
make the entries in the first column of the table Dues, as A, B,
C etc.
--
Regards
Roger Govier
the entry will be was going to be a single digit, 1 - 60 but I
think
in order to simplify it, it can be a single digit, A - L
representing
the 12 level groups. Thanks for asking that question, it
clarified
that for me. I don't think logically enough, that's for sure.
Hi Steven
It is still unclear (to me at least), whether the entry in
column B
will be either 1 or 2 or 3 etc. up to 60 or whether it will be
"1-5"
or "6-10" etc. up to "56-60".
If the former, then on another part of the sheet (or even a
different
sheet) set up a table as follows
0 0.20
6 0.40
11 0.60
.
.
60 2.40
Mark the range of this 2 column table, and in the Name box (the
small
square to the left of column A and above row 1) type Dues and
press
Enter
Then using Erik's idea enter in C1
=IF(B1="","",IF(D1="P",VLOOKUP(B1,Dues,2),-VLOOKUP(B1,Dues,2)))
If, on the other hand, you are using "1-5" etc. in B1, then
create a
table as follows
(Note: all the entries of 1-5 etc will either have had to have
the
cells pre-formatted as Text, or you will need to precede the
entries
with a single quote '1-5 to force them to be text vales)
1-5 0.20
6-10 0.40
11-15 0.60
etc.
Again name this table as Dues as outlined before.
Now, use the formula in C1
=IF(B1="","",IF(D1="P",VLOOKUP(B1,Dues,2,0),-VLOOKUP(B1,Dues,2,0)))
Your entries in column B will also have to be Text entries.
Format>Cells>Text
--
Regards
Roger Govier
I really have not explained what I want clearly. Please forgive
me. I
will try to be more exact. I have three columns. The first (B)
is the
level a person is at. This can be 1-60, in sets of 5, so 1-5,
6-10
and so on. The second column (C) is the guild dues they owe,
which is
based on their character level range. Everyone who is level 1-5
owes
the same guild dues, while everyone who is 6-10 owes a higher
amount
and so on. So what I need is to be able to put their level in
(B) and
have it bring up one of 12 different dues amounts, based on the
level
entered in (B). So if I enter a number between 1 and 5, column
(C)
would automatically show the one value out of the 12 that
corresponds
to that level. Column (D) needs to be either blank or have a P
in it.
If blank, then the figure in (C) would be red or negative and
if
there is a P, then it would be black/positive. This is for a
game,
where we all pay game money to the guild, to help it grow. I am
in
charge of keeping track of who pays and who does not, so I need
something to make it easier and quicker.
So something like [if b = 1-5 then 20c if 6-10 then 40c, if
11-15
then 80c on to 56-60 and if (D) is blank then (C) is negative
and if
(D) is P then (C) is positive/paid.
Steven,
If you fill H1 to H13 with 1,6,11,16 and so on to 61, and
if you fill I1 to I13 with 20,40,60,80 and so on to 260, and
enter in C1 formula: =LOOKUP(B1,$H$1:$I$13) and copy this
formula
down,
then the result is 20 in C1 for values 1,2,3,4,5 in B1
=IF(D1="P";LOOKUP(B1,$H$1:$I$13);-LOOKUP(B1,$H$1:$I$13))
will make this value positive if D1 is P, or negative if it
is not.
HTH
Erik
"Steven" <
[email protected]> schreef in bericht
Sorry, but that makes no sense to me, I don't know enough.
Expand on this idea
=LOOKUP(E1,{0,1,6,11,16;0,1,2,3,4})
--
Don Guillett
SalesAid Software
(e-mail address removed)
Thank you so far. I still have questions though. Will that
work
for several sets of ranges? I need it to be 1-5, 6-10,
11-15, on
up to 60. Also, I think D would be simplest if it was
either P
or blank. I also need to set C to be whatever value
corresponds
to the specific range, like 1-5 makes C 20, while 6-10
makes C
40 and so on. I am sorry I did not think this through
properly
before making my original post.
message
Hi Steven
Maybe
=IF(AND(B1>0,B1<=5),999*IF(D1="",-1,1),"")
Change the 999 to any number you want to use.
--
Regards
Roger Govier
I have three columns, B, C and D. In B I would have
ranges such
as 1-5 6-10 and so on. In D I would have yes or now, paid
or
not paid. I would kind of like it to be checked or not
checked
if possible. C must be something like "if B is 1,2,3,4 or
5,
then xxx and if D is yes or checked it is a positive and
if it
is empty then it is a negative." I kind of have an idea
how to
do this, but nothing I try works. How do I accomplish
this?
TIA
Steven
"A democracy is nothing more than mob rule,
where
fifty-one percent of the people may take away the rights
of
the other forty-nine." - Thomas Jefferson
