Simple look up but odd layout

[69liz]

Hi,

We have a spreadsheet booking system for ticketed events. The booking side of it has the spreadsheet laid out as the seating plan in the hall. The financial section needs to be laid out in vertical columns.

When someone buys a ticket, say A1, their name is entered in the financial sheet. We want the name to automatically appear in the seating plan. Easy enough with a simple =Sheet!C5 BUT the problem is we need to do this for 220 cells and don't want to have to manually put in the =Sheet!C6 etc.

We can't drag because the layout is different.

Can someone suggest a solution?

Thanks

Liz

 

[Peter_SSs]

An alternative is to use vba. Test this in a copy of your workbook.

1. Go back to the SALES sheet not having a header row/column so the seats are represented by cells A1:N16

2. Delete everything on the SALES sheet.

3. In Sheet1, the seat numbers A1, ..., N16 are listed in B5:B228 (all 224 of them as indicated by Gerald)

4. On Sheet1, right click the sheet name tab and choose 'View Code'

5. Copy the code below and Paste into the main right hand pane that opened at step 3.

6. Close the VB window.

7. On Sheet1, if you already have some names in column C, select all those names and copy and paste them back again. (That should trigger the macro to enter those names on the SALES sheet.)

8. Now as you enter, delete or change names on Sheet1, those changes should be automatically reflected on the SALES sheet. Note that this does not work in reverse. That is, if you change something on the SALES sheet that change will not automatically be reflected on Sheet1 (though further code should be able to achieve that also if you wanted)


<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> rNamesChanged <SPAN style="color:#00007F">As</SPAN> Range, rName <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rNamesChanged = Intersect(Target, Range("C5:C228"))<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> rNamesChanged <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> Sheets("SALES")<br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rName <SPAN style="color:#00007F">In</SPAN> rNamesChanged<br>                .Range(rName.Offset(, -1).Value).Value _<br>                    = rName.Value<br>            <SPAN style="color:#00007F">Next</SPAN> rName<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>


By the way, you cannot attach your file, but you can post small screen shots (not all 228 rows please) directly in your post. My signature block below contains 3 methods for posting small screen shots. Test them in the Test Here forum. That way, if something goes wrong, you won’t be messing up a main forum.

 

[69liz]

Gerald's solution has got closest but I can't get it to work correctly. I am trying the HTML add in so I hope that works.
Or I could share the sheet via GoogleDocs?
This is a small section of the sales spreadsheet.

Excel Workbook
	A	B	C	D	E	F
1	Seat/row	1	2	3	4	5
2	A
3	B
4	C
5	D
6	E
7	F
SALES

Excel 2010
This is a small section of the financial sheet (sheet 1):

Excel Workbook
	A	B	C	E	F
3	Date	Ticket No	Name	Child	Adult
4
5	24.01.11	A1	john smith	P
6	24.01.11	A2			P
7	24.01.11	A3			P
8	24.01.11	A4			P
9	24.01.11	A5			P
Sheet1

Excel 2010

The ideal is that the name from the financial sheet populate the correct seat on the sales sheet.
Thanks. All this is good learning!

 

[69liz]

By the way, I tried the VBA option and it worked initially. Then there was a run-time error and it stopped working. I deleted the code and tried it again and it wouldn't work at all.

 

[Peter_SSs]

For the formula method, I had the letters/numbers the opposite way round to you in my SALES sheet. Try this.

Excel Workbook
	B	C
3	Ticket	Name
4
5	A1	Bob
6	A2	Dan
7	A3
8	A4	Jen
9	A5
10	A6
11	A7
12	A8
13	A9
14	A10
15	A11
16	A12
17	A13
18	A14
19	A15
20	A16
21	B1
22	B2
23	B3	Ken
24	B4	Jan
25	B5
Sheet1

Formula in B2 copied across and down.

Excel Workbook
	A	B	C	D	E	F
1	Seat/Row	1	2	3	4	5
2	A	Bob	Dan	unsold	Jen	unsold
3	B	unsold	unsold	Ken	Jan	unsold
4	C	unsold	unsold	unsold	unsold	unsold
SALES

In relation to the vba approach, what I am pretty sure has happened is that your 'Events' have been disabled because of the error. Hopefully you were/are trying that in a separate copy of the workbook.

In the VB window, ensure the Immediate Window is visible (View|Immediate Window) and then on a new line in the Immediate Window, type
Application.EnableEvents=True and press Enter

Now try again. If you can re-produce the error it would be good if you could report the whole error message as well as clicking Debug and indicate what line of code is highlighted yellow. Also try to describe what you did just before the error.

 

[69liz]

Yay! Your formula approach worked!

Thank you so much. The formula method is preferable as this sheet will not be used by me and the VBA can cause problems because of security setting in school.

Thank you so much.