Format this cell like that cell, automatically?

[Stan Brown]

I keep my grades in an Excel workbook of four sheets, of which the
two relevant ones are Attendance and Grades. During the semester, if
a student drops the course I mark all cells for that student in both
worksheets as Locked and I apply a gray pattern to them.

Having to do this in two worksheets is kind of a nuisance. Is there
any way to set, say cells Grades!A11:AP11 to pick up automatically
any formatting and Lock/Unlock status as it changes in Attendance!A1?
I know I could do this in a macro, but I'm wondering if there's any
purely worksheet way to do it.

 

[Dave Peterson]

You'd need a macro to pick up locked/unlocked formatting setting.

But you may be able to shade the colors by using an indicator column and then
inspecting that in the other worksheet by using format|Conditional formatting.

I created a worksheet named Att (for attendance) and I had the student's name
(ID?) in column A. I inserted a new column B that held my indicator (D
indicates Dropped).

Then I used Insert|Name|Define to create a range name for columns A:B on that
Att sheet. I named it myTable.

Then back to the Grades sheet.

With names in A2:Axx and columns to shade A:M, I did this:

I selected A2:Mxx
With A2 the activecell
Format|Conditional formatting
Formula is: =(VLOOKUP($A2,myTable,2,FALSE)="D")
And gave it a gray pattern format

On the Att sheet, I could use the same kind of thing.

I selected A2:Xxx
With A2 my activecell (and column B still the indicator cell):
Format|Conditional formatting
Formula is: =($B2="D")

Maybe you could add strike-through to the font formatting to make it stand out
more (and drop the locked/unlocked stuff???)

 

[Ron Rosenfeld]

I keep my grades in an Excel workbook of four sheets, of which the
two relevant ones are Attendance and Grades. During the semester, if
a student drops the course I mark all cells for that student in both
worksheets as Locked and I apply a gray pattern to them.

Having to do this in two worksheets is kind of a nuisance. Is there
any way to set, say cells Grades!A11:AP11 to pick up automatically
any formatting and Lock/Unlock status as it changes in Attendance!A1?
I know I could do this in a macro,

That's what you will need, most likely.

but I'm wondering if there's any purely worksheet way to do it.

No. But you could possibly use the XLM.GET.CELL function from Longre's free
morefunc.xll add-in (downloadable from http://xcell05.free.fr/ ).

You could use this to assess the Fill pattern, and then use conditional
formatting to match it.

Although you could also ascertain whether a cell is Locked, using that
function, I know of no Non-VBA method to actually lock the cell.

In addition, since you cannot have a conditional formatting formula that refers
to a different worksheet or workbook, you would need to enter the formula
elsewhere on your Grades worksheet, and then refer to that cell.
--ron

 

[Dave Peterson]

Just a general comment about the last paragraph.

But you can give the range a workbook level name and use that in the conditional
formatting formula--or even use =indirect() to point at that other sheet.


Ron Rosenfeld wrote:

 

[Ron Rosenfeld]

Just a general comment about the last paragraph.

But you can give the range a workbook level name and use that in the conditional
formatting formula--or even use =indirect() to point at that other sheet.

Good point.
--ron

 

[Stan Brown]

Sat, 28 Apr 2007 08:57:49 -0500 from Dave Peterson

You'd need a macro to pick up locked/unlocked formatting setting.

But you may be able to shade the colors by using an indicator column and then
inspecting that in the other worksheet by using format|Conditional formatting.

[details, snipped]

Thanks for that detailed answer, Dave. There are some good ideas
there, particularly the strikethrough. But I see it as an alternative
to the background pattern, not to the locking. The reason I want to
lock those cells is so that Excel will skip over them as I enter quiz
grades using the down arrow. It sounds like you've confirmed my
suspicion that I'll have to do it with a macro.

 

[Stan Brown]

Sat, 28 Apr 2007 10:05:40 -0400 from Ron Rosenfeld

That's what you will need, most likely.


No.

[more information, snipped]

Thanks for the response, Ron. As long as I have to write a macro
anyway, I may as well make it do the whole job.

 

[Ron Rosenfeld]

Sat, 28 Apr 2007 10:05:40 -0400 from Ron Rosenfeld

That's what you will need, most likely.


No.

[more information, snipped]

Thanks for the response, Ron. As long as I have to write a macro
anyway, I may as well make it do the whole job.

Fortunately, Excel provides the macro tools to do this.
--ron

 

[ilia]

Sat, 28 Apr 2007 10:05:40 -0400 from Ron Rosenfeld

Is there ny way to set, say cells Grades!A11:AP11 to pick up
automatically ny formatting and Lock/Unlock status as it changes
in Attendance!A1? know I could do this in a macro,
That's what you will need, most likely.
but I'm wondering if there's any purely worksheet way to do it.
No.

[more information, snipped]

Thanks for the response, Ron. As long as I have to write a macro
anyway, I may as well make it do the whole job.

Fortunately, Excel provides the macro tools to do this.
--ron- Hide quoted text -

- Show quoted text -

You ought to create some styles. I see it probably needs two: one for
current students, one for dropped students. Only make part of the
style what applies, e.g. strikethrough is part of font and grey is
part of shading, but not borders etc. Then, all you have to do is
apply the style to dropped students, and the active student style to
any new students.

 

[Stan Brown]

You ought to create some styles. I see it probably needs two: one for
current students, one for dropped students. Only make part of the
style what applies, e.g. strikethrough is part of font and grey is
part of shading, but not borders etc. Then, all you have to do is
apply the style to dropped students, and the active student style to
any new students.

Styles -- what a great idea! I use them heavily in Word, but never
thought of them for Excel. Thanks!