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

 

[Dryver14]

what does the layout look is it 11 X 11? curved?

are the seats numbered with the name to go below?

 

[Gerald Higgins]

And do you somehow record their seat ID number in the financial sheet ?

 

[69liz]

I was going to attach the sheet but cuoldn't see the option.
Anyway,
the layout is a straightforward rectangle: A1:N16 The seat numbers match the cell references.

the name of the person who has bought the seat is typed into the cell when they purchase. This automatically generates a register when a second 'check in' sheet is used on the night.

On the financial sheet, we have a list of seat numbers in column B. Column C holds the name of the person, D the amount paid, E records if it is a child, F if it is an adult, G if cash was paid, H, a cheque etc.

It is much easier on check in to have the screen laid out like the actual seating plan. Any ideas would be gratefully received! Liz

 

[scottylad2]

Do you have any headers on the columns? an index and match springs to mind. If you put a border round your data you can copy and paste an image into here so we can see what you have.

 

[69liz]

By headers, do you mean labels? The seating plan doesn't have any but the financial sheet does have.

Sorry, can't seem to get an image in either.

 

[Gerald Higgins]

OK so it sounds as if, on the diagram of the seating plan, individual cells in the range A1:N16 represent seats, and these seats have numbers which match the cell address - is that right ?
So cell A1 refers to seat A1, and cell D9 refers to seat D9, and so on, is that right ?

If it is, then plug this formula into cell A1, and copy to the rest of A1:N16

=VLOOKUP(ADDRESS(ROW(),COLUMN(),4),Sheet1!$B$1:$C$224,2,FALSE)

This assumes that the "financial" sheet is called Sheet1, and the seat numbers and names are in the range B1:C224.

Note, this formula will return a #N/A error for any seats that have not been sold yet.
If you don't want that, you could use this instead

=if(isna(VLOOKUP(ADDRESS(ROW(),COLUMN(),4),Sheet1!$B$1:$C$224,2,FALSE)),
"unsold",VLOOKUP(ADDRESS(ROW(),COLUMN(),4),Sheet1!$B$1:$C$224,2,FALSE))

 

[69liz]

Thanks for this! It certainly returns 'unsold' in the seats.

You are right about the layout. The layout sheet is called SALES but the financial sheet is called Sheet 1 because I am having to satisfy the finance office and combine the spreadsheet they use with the one I want to use.

How should I layout the ticket numbers on the financial sheet to make this work properly? At the moment (the range is B5: C225) and they are listed as A1 (in B5), A2, in (B6) etc and then changes to B1 at B21).

At the moment, with your formula, the name from the financial sheet in cell C22 (and seat B2) displays in the seating plan in A1. No other names appear before this. After this they fill in A1, B1, C1 etc rather than filling the front row first.

I really want to get it to work as otherwise we will have to go back to the old method which is chaotic and inaccurate - ie. it is paper-based and prone to human error.
Thanks a lot for your help.

 

[Gerald Higgins]

Hi.

One detail.
If your range of seats really does fill the range A1:N16, then I think that should be 224 seats, because A1:N16 is 224 cells.
You said that on the financial page, the seats fill the range B5:C225, which is only 221 seats.
Maybe you do actually have 3 seats missing, in which case that's fine, but I thought I should mention it.

With the range that you have mentioned, I'm assuming that you have people's names in column C. So if you sold seat A1 to John Smith, then cell C5 on the Financial page should contain "John Smith", and it's that that you want to pull into cell A1 on the layout page - am I understanding your requirement correctly ?

If so, then the formula I suggested does that, but just edit it slightly to

=if(isna(VLOOKUP(ADDRESS(ROW(),COLUMN(),4),Sheet1!$B$5:$C$225,2,FALSE)),
"unsold",VLOOKUP(ADDRESS(ROW(),COLUMN(),4),Sheet1!$B$5:$C$225,2,FALSE))

This is the formula that needs to go into cell A1 on the sales sheet, and then you can copy it across to the entire range A1:N16.

 

[Peter_SSs]

1. Why ISNA in the formula? As far as I can tell, all the seat numbers are listed in column B of Sheet1 so unless there is an #N/A in column C of Sheet1, the VLOOKUP will never return #N/A. I think that formula needs to be looking for a blank in column C not an error:

=IF(VLOOKUP(ADDRESS(ROW(),COLUMN(),4),Sheet1!$B$1:$C$224,2,FALSE)="",
"unsold",VLOOKUP(ADDRESS(ROW(),COLUMN(),4),Sheet1!$B$1:$C$224,2,FALSE))

2. The formula would be much simpler if we added a header column and row on tne SALES sheet to indicate the row and seat number.

So Sheet1:

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

Sheet SALES:
Formula in B2 copied across and down.

Excel Workbook
	A	B	C
1	Seat/Row	A	B
2	1	Don	unsold
3	2	Tom	Bob
4	3	Jen	Jan
5	4	Kim	unsold
6	5	Sam	unsold
7	6	unsold	unsold
8	7	unsold	unsold
9	8	unsold	unsold
10	9	Ken	unsold
11	10	unsold	unsold
SALES