Working on a big spreadsheet

[mdj0615]

I have been very proud of myself on this spreadsheet, but I'm stuck.
One one sheet, I have an attendance sheet, and on another sheet I have the
identical page, used for a point amount.

What I'm looking for, is if I put an A in a box, the corresponding box on
the other sheet will be a 5, if a T, 2, if an L, 2, and if a TL, a 5. What
formula would that be?

Thank you in advance.

 

[Max]

One way is via a VLOOKUP
Assume your attendance sheet is named: Sheet1
In your "point amount" sheet,
you could have something like this in A2:
=IF(Sheet1!A2="","",VLOOKUP(Sheet1!A2,{"A",5;"T",2;"L",2;"TL",5},2,0))
Then you could just copy A2 across/fill down as desired to return the
necessary.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[mdj0615]

Thanks. That worked well, but now I have another question. All of the cells
that don't have an A, L, etc have a P in the box. When I put a P in the
cells, I get an ##. How do I change the formula to when I put in a P, there
is nothing put in the cell?

 

[Max]

Just change the front part of it:
to this:
=IF(OR(Sheet1!A2={"","P"}),"", ...
which will trap the "P"
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[mdj0615]

Okay, one more question, and I will stop. That worked just great for the
spreadsheet, the Ps on sheet one no longer affect sheet two. I did not put
into account that I also need to put other numbers onto sheet two directly,
and I'm afraid to tinker with the formula now. How can I change the formula
to where it will still be there even if I need to write directly into the
cells. For example, there are times where I need to put a T on sheet 1,
which becomes a 2 on sheet two, but I need to change that value to 4, or
there's a P on sheet one, which puts nothing on sheet two, but I need to
write in a 3.

 

[Max]

Not possible. A cell can contain either a value or a formula at any one
time. You can overwrite a formula cell by manually entering a value into it
but the formula's gone.

Instead of amending the formulated Sheet2, it may be viable for you to work
further on a static capture of the entire Sheet2 via quick copy n paste
special as values of Sheet2 into a new sheet, making your manual downstream
amendments/adjustments over there. In this way you can retain Sheet2's
formulated functionality as it stands.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik