Automatically add lines and keep formulas

[Matt]

Hi folks!!!

I'm kinda stuck and need a little guidance. I have created a spreadsheet
for tracking hotel bookings, Revenue from the bookings and commission paid.

What I would like to is to be able to have the spreadsheet automatically add
new rows as I run out space. Currently I have around 100 rows dedicated to
this, but it will only take a couple of days to fill those row before I need
more. It looks something like this.

The headers begin in A4 and go as follows:- (Columns I-N all have a width
of zero so they appear hidden)

A - Reservation Number
B - Source of booking (dropdown box - Choices = Web, Worldspan, Pegasus,
Galileo, Amadeus, Sabre)
C - Arrival date
D - Number of nights
E - Number of people
F - Total booking value
G - Average room rate (contains formula - Total booking value divided by
number of nights - F5 divided by D5)
H - Meals included (dropdown box)
I - Room revenue (contains formula - =IF($H8="Breakfast",$E8*6," ")
J - Breakfast Revenue (contains formula - =IF($H5="Breakfast",$E5*6," ")
K - Dinner Revenue (contains formula - =IF($H5="Dinner",$E5*17.95," ")
L - Dinner, bed and breakfast revenue (contains formula -
=IF($H5="DBB",$E5*23.95," ")
M - Room only (contains formula - =IF($H5="Room only",$E5*0," ")
N - Total food revenue (contains formula - =SUM(J5:M5)*D5
O - Net room revenue (Contains formula - =IF(F5=0,"",I5/1.175)
P - Net food Revenue (contains formula - =SUM(N5/1.175)
Q - Enhance revenue
R - Total net revenue (Contains formula - =SUM(O5:Q5)
S - Comission paid (contains formula -
=IF(B5="Web",G5/100*6*D5,IF(B5="Amadeus",3.25+D5*G5/100*5,IF(B5="Sabre",3.25+D5*G5/100*5,IF(B5="Worldspan",3.25+D5*G5/100*5,IF(B5="Galileo",3.25+D5*G5/100*5,IF(B5="Pegasus",3.25+D5*G5/100*5))))))

All cells with formulas are locked and the sheet protected.

I appreciate that there are probably easier way to achieve the results that
I have, but I just want to point out that I am self taught with Excel which
is why I have probably gone about it all the long way.

I have done a search and found a couple of topics where people have a
similar situation to me, but they all seem to require the use of VB. The
problem is that I have never even looked at VB before and am easily confused
by it. Is there a simpler way to achieve what I want? If not, does anyone
know where I can find a complete idiots guide to using VB?

Many thanks in advance
Matt

 

[Roger Govier]

Hi Matt

The easiest way for you if you don't want to get into using VBA at the
moment, would be to create formulae down the page for a greater area than
you require.
You can easily copy the formulae down, by selecting the whole row of cells,
then hovering over the bottom right of the rightmost cell until you see a
small solid black cross (known as the fill handle). Click on the fill handle
and drag down the page as far as you wish
Before doing so, however, you should include an extra IF clause for some of
your formulae, so that it does not carry out the calculations on rows where
you have not yet entered data, e.g. in G5
=IF(D5>0,G5/D5,"")
This will stop a series of DIV/0 errors from showing up down the sheet.

As far as you last formula is concerned, it is only Web that has a different
rate to the others, so you could simplify the formula to
=IF(B5="Web",G5/100*6*D5,3.25+D5*G5/100*5)

 

[Matt]

Hey Roger,

Thanks for the help and advice. This spreadsheet will be a constantly
growing list but unfortunately I am not going to be the person managing it
which is why i need it to be able to automatically insert new rows when the
existing ones get full. I'm getting the impression that VB is the only way
forward for what I need, so any pointers would be helpful (regardless of how
small they may seem)

Kindest regards
Matt