Hi,

I'm trying to create a spreadsheet to track merchandise stock, sales, etc on a tour of a lot of venues.

My aim is to have columns in which you enter the start and end stock for each item and it will calculate the sales figures, etc. The rows will be each item that is sold and the price (my idea was to have a list on a seperate sheet where I can input all the items and their prices).

These columns would be repeated lots as you would need this info for every venue. So I would input the venue city and name at the top of each group of columns.

I wondered if there is any way to:-

1. Have a sheet which has a drop down box of the cities, when you pick one it would give you figures and sales for that venue (which it would lookup and grab from my other sheet)?

2. Is there a way that if you then have a sheet of data you can have a little button that will automatically create (and save?) a pdf file of the sheet and make sure it fits to one page?

3. Can you somehow have a sheet in which you enter all the cities and venues, and it will automatically put these as column headings on my sheet in which I input my figures and then go on to reference these as I mentioned in Q1?

4. Can you make a cell change it's format type based on a drop down list selection. For example, when you pay commission at venues you sometimes pay a percentage and sometimes pay a flat fee. I would like a drop down list to choose whether it's a percentage or fee, and then a box below to enter the percentage or the fee value (I would therefore need this cell to switch between percentage and currency formats).

5. Can you either add/delete or hide/unhide rows based on whether they are empty?

Finally,

6. If I have a list of items and prices and everything references this... if we then change the price we are selling an item at it would change every calculation. is there any logical way of avoiding this so you can have a load of data that remembers the old price and isn't affected if you change it to a different price.

I'm ok with excel but when it comes to macros, and more advanced formulas I'm lost so any basic answers or examples would be truly appreciated. If the above doesn't make sense I can try and post a basic idea of the spreadsheet.