Mapping auditorium seating in Excel

[i dude eclair]

Scenario:

Sheet1 contains a vertical list of sold seats in the format of

EE1
EE2
GG23
GG24
GG26

etc ..


Sheet2 is a visual 'map' of the auditorium with cells of the values of
the seat names.


I would like to implement a macro (or some automation) to scan Sheet1 and
- if the seat value is present - to format the corresponding cell (seat)
on Sheet2.


With my limited knowledge - the only code would be really long and
cumbersome.

Can anyone suggest a simple mechanism?

thanks

(if this is the wrong forum, I'll post where you recommend)


cheers

 

[Pete_UK]

You can use Conditional Formatting to colour the seats on Sheet2 which
are sold (no need for any code). In sheet1 highlight the column
containing the list of sold seats and click the Formulas tab (XL2007)
then in the Defined Names section click on Define Name and give that
range a name like Sold.

Then in Sheet2 you should highlight all the cells that contain your
seat references - suppose this starts in A2 and covers to Z100. Click
on Conditional Formatting on the Home tab, then choose New Rule and
then "Use a formula to determine which cells to format". Enter this
formula in the box:

=MATCH(A2,sold,0)

Click the format button, then the Fill tab, and choose the colour you
would like. Click OK twice to exit the dialogue box.

Hope this helps.

Pete

 

[i dude eclair]

very close

but.

the results are unpredictable, however (there seems to be some type of
offset occurring)



I was sort of hoping to add it to existing code, as this is 1 component of
a larger piece of automation .. extracting, sorting, removing blank row,
etc.

 

[WallyWallWhackr]

I use conditional formatting in the target cell (the map elements) in
mine to be borderless white on white IF a given condition on my first
sheet exists.

I use it to blank out bands. You would have to recreate the entire map
using a cell or other manipulatable element for each seat position.

Check my template on the Microsoft Office Community forum site:


http://office.microsoft.com/en-us/t...resistor-value-decoding-tool-TC101945032.aspx

You can use Conditional Formatting to colour the seats on Sheet2 which
are sold (no need for any code). In sheet1 highlight the column
containing the list of sold seats and click the Formulas tab (XL2007)
then in the Defined Names section click on Define Name and give that
range a name like Sold.

Then in Sheet2 you should highlight all the cells that contain your
seat references - suppose this starts in A2 and covers to Z100. Click
on Conditional Formatting on the Home tab, then choose New Rule and
then "Use a formula to determine which cells to format". Enter this
formula in the box:

=MATCH(A2,sold,0)

Click the format button, then the Fill tab, and choose the colour you
would like. Click OK twice to exit the dialogue box.

Hope this helps.

Pete

snippage

 

[WallyWallWhackr]

The "offset" has to do with how big I made it.

If the whole thing is in view, the whole thing properly refreshes.

 

[i dude eclair]

The "offset" has to do with how big I made it.

If the whole thing is in view, the whole thing properly refreshes.

the offset was directed at the CF =MATCH(A2,sold,0)

I'm tweaking it now, but can't determine the cause.

 

[WallyWallWhackr]

the offset was directed at the CF =MATCH(A2,sold,0)

I'm tweaking it now, but can't determine the cause.

I use the conditional formatting interface and select "by value" and
then define the value as "=(Value)" or "="Value""

I also use <> as well so the cell blanks if a certain other cell's
value is blank or is NOT whatever the comparison criteria is.

Post a nice (as good as you have) jpeg of the seating map overview on
flickr or mediafire
(both free and mediafire is anon if desired).

Or is there a link to a site that has a nice seating map?

Then post the link back here.

 

[i dude eclair]

image:


http://www.mediafire.com/imageview.php?quickkey=3hgipp2bc82ug8x&thumb=4


please note that I am trying to keep this within VBA



I can post seating table if required


thanks for all the attention!

 

[WallyWallWhackr]

image:


http://www.mediafire.com/imageview.php?quickkey=3hgipp2bc82ug8x&thumb=4


please note that I am trying to keep this within VBA



I can post seating table if required


thanks for all the attention!

Wow. That was a lot of work.

So, since they are all individual cells, you CAN do it purely with
conditional formatting, but it requires at least one entry to be made per
cell, perhaps 2 or more per if you need to have multiple logic conditions
be managed.

It is possible without vba, but vba might speed up the method of
entering the conditional formatting rules. It should only need to be
done once though. A run-once-and-delete script.

 

[i dude eclair]

Wow. That was a lot of work.

So, since they are all individual cells, you CAN do it purely with
conditional formatting, but it requires at least one entry to be made per
cell, perhaps 2 or more per if you need to have multiple logic conditions
be managed.

It is possible without vba, but vba might speed up the method of
entering the conditional formatting rules. It should only need to be
done once though. A run-once-and-delete script.

all done

sort of a hybrid VBA/CF thing

tweak here, tweak there

bottle of wine

and voila


thanks for all the suggestions

onward and upward!