Advertising sales record spreadsheet

[Soapy Joe]

I am preparing a new spreadsheet to record advertising sales bookings. There
are different sized advertisements and rates vary between clients. Each
client is listed in one column with the prices for each size advertisement in
the same row. On from this are columns for bookings to be recorded in each
edition for example:

Client Q H F AUG SEPT OCT NOV DEC
a 200 500 800
b 150 450 650

Can I use a formula to create a way of typing e.g. "H" in the cell below the
appropriate month, corresponsding to a particular client, say "b", and pull
through rate quoted?

Any help really would be appreciated.

 

[Duke Carey]

Yes - you can. However, before you get too far down this path, I recommend
that you reconsider your approach.

What will happen to your sheet and the calculations in it when it comes time
to change the rates?
What will happen if one of your clients chooses to take out 2 different
bookings in the same month?

If you don't care about those concerns, then the answer to your question is,
assuming row 1 has column headers, and the data starts in row 2, with the
rates in columns B-D.

=INDEX($B2:$D2,1,MATCH("H",$B$1:$D$1,0))

Replace the "H" with the appropriate code

 

[Soapy Joe]

Thanks Duke, I will go back to the drawing board!