Populating cells based on growing list of names.

Originally Posted by rxwillow

I have attached a workbook with 2 sheets. One sheet is a list of magazines that shows what magazines we have. Each magazine gets distributed around my office. When a person is done with it, they give it back to me and I choose someone else to give it to. The sheet has a grid of initials for each person and when I give them the magazine, I "x" out the box for that magazine under their initials.

Some people don't want to receive certain magazines so the 2nd sheet is a grid where they choose what they want to receive. Is there a way that I could link the blank boxes from the sheet where they choose what they want to the magazine list and automatcially "x" out that magazine?

To use a vlookup function would require me to type an equation in a box that I might have to populate later so i'm not sure if something in a module for that worksheet would be better.

I hope I just made sense.

Hi,

If the list of magazines wasn't changing, other than perhaps being extended rather than overwritten, then presumably you could use a simple IF() formula, to identify magazines that weren't wanted, since you could simply overtype the formula when the mag was issued.

One solution would be to have a bit of VBA code which remembered where the magazines had been issued by temporarily copying the grid elsewhere, then populating the sheet with a new list of mags and filling in the grid with formulas, finally copying back the Xs, for any mags that had already been issued.

That seems to me a bit OTT. You may find the Conditional Formatting approach is something you can work with. Add the following conditional format to E2 on the Magazine List sheet, and then copy E2 across and down. You should put this in the Formula Is box, and set the format pattern to say Red.

What you'll see are red coloured cells where a mag is not required. The contents of the cell are irrelevant.

=VLOOKUP($A2,Selections,COLUMN()-3,FALSE)<>"x"

HTH