Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically add lines and keep formulas
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically add lines and keep formulas
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(D50,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) -- Regards Roger Govier "Matt" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically add lines and keep formulas
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 "Roger Govier" wrote: 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(D50,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) -- Regards Roger Govier "Matt" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically insert lines? | Excel Discussion (Misc queries) | |||
How am I able to del duplicate lines automatically | Excel Worksheet Functions | |||
Inserting Lines or Copying lines with formulas but without data | Excel Discussion (Misc queries) | |||
Automatically number lines | Excel Worksheet Functions | |||
Can I automatically extend formulas to added lines in Excel 2002? | Excel Discussion (Misc queries) |