View Full Version : excel function

Trev O
08-10-2003, 08:19 PM
I have my accounts set up with excel (XP) and one thing that I'd like to change is typing in the same info more times than I have to...

So I have the GST free amount calculated into a row box and then I have to copy that figure into the correct column (for the end of year returns on a different sheet)

eg. I put say 10.00 ($10) into expences column 'F' and the GST free amount is calculated into box 'H' and then I have to copy that amount into which ever box I want it to go into ( I, J, K, L... etc)
and what I'd like to do is just click on the box i'd like it to go to ( I, J, K, etc) and the figure go straight in... with out me having to either type it in or copy and paste it in.

can it be done and if so how...?

thanks Trev

08-10-2003, 08:38 PM
Why dont you have an IF formula in the column I etc so the amount is blank unless the condition you want is met?

Trev O
08-10-2003, 08:45 PM
I'm not sure how to do that...
the expence type could end up in any column ( rates, petrol, vehicle etc)

08-10-2003, 08:49 PM
The problem with an if formula is the criteria to be used. You could have a column I where you use a number to represent each expense column. The if statement for J would be =if(I8=*account code*,h8,""), where row 8 is the row you are on.

The account codes could be set up as a drop down menu using the tools toolbar

Trev O
08-10-2003, 08:57 PM
I'm lost...!

although the drop down menu sounds good for the expence types on each sheet in column A...(something else wrong with my accounts set up!)

08-10-2003, 09:04 PM
When you put an amount in column F, how do you decide which expenses col to put it against. Is there another column with the expense type? Drop down boxes are quite easy and so are conditional formulas but there needs to be a way to determine the type of expense so it goes in the right column.

Trev O
08-10-2003, 09:18 PM
Ok in column A I have the date Incolumn B I have the expence type (say BP petrol) then over in column I the amount paid for the fuel, and in c J the GST amount is calculated via formula and c K the amount without GST (calculated also)... this amount I type into the correct column (say vehicle column L)
so i'd just like to click on that column (L) and the figure be copied into that box instead of me having to type it in
If it were a different expence type I'd like to be able to click on a different box and the amount be copied over to that box...

08-10-2003, 10:41 PM
Have you got a list of all the expense types and what column they are to go in? Once you have this there can be several approaches depending upon the number of different options. Having a conditional formula in I-L wont be much good if you have a lot of options so have to be handled another way.

08-10-2003, 10:47 PM
Heres a very simple example using he OR and IF functions in columns D-F.


08-10-2003, 11:02 PM
If I understand it correctly, the expense type is allocated to columns depending on the type. Columns C to H possibly.

Why not reverse the experience, and have I with the formula =SUM(C2:H2). Then filling out the cost once, under the type (e.g. Vehicle) will populate I, and all that flows from that.

The advantage of summing across the columns is that you can allocate the pie you bought at the same time to Meals, and the balance to Vehicle.

This is too simple, I must have missed something ...

09-10-2003, 07:07 AM
The best solution is to get some proper software from someone like www.acclaim.co.nz. It has appeared on the PCW cd. It's free for personal use and not very expensive for business. It will do a better job than excel.

Trev O
09-10-2003, 07:55 AM
Morning all, thanks Wots for the drop down menu idea for column B, which was going to be my second question... I did that last night via Insert--Name and Data--Validation. I don't want to go to other software as they're so complicated for what I want (or at least I find them complicated!)

I thought about the If function some more Parry and that could work, apart from, sometimes I split the petrol money to 2 columns and also when I buy stuff from (say) Mitre10 it could go into about 5 boxes, eg house R&M, Garden R&M, Car R&M etc. sorry I couldn't see your example page (not available)

Godfather in columns c-g I have my income, also GST divided and and in c H I have the whole amount from both sides added up to give a monthly result at the bottom (all the columns have a monthly result at the bottom). reversing the whole spreadsheet info sound good, but how would I end up with each items GST free amount in the box I type into?

09-10-2003, 08:32 AM
AAh, well my suggestion wouldn't work.

I use an even simpler method however. MYOB.
Took 5 minutes to do my 6 monthly GST return this month.
At FYI I simply burn the file to a CD and give it to my Accountant.

Trev O
09-10-2003, 01:52 PM
Aah, but i'm saving my hard earned dolleros for me, so I don't have that expensive accountant anymore

and it only takes me five minuits to do my 6 monthly GST returns and five mins for the IR3's too and I also burn it all to CD...

so is there no way I can type a figure into a box and after pressing enter it turn into the 8, nineths?

09-10-2003, 05:25 PM
Hi again, yes typing into a box then clicking a button could put the amount into a cell but it has its own problems with where to put the data as it changes for you. There could be a simple box to enter amount, list boxes to choose the column & row to input it to etc but if your objective is to save time then I dont think this will speed things up much as its a lot quicker just to have extra columns for the expenses and in these have your formula to calc the ex-gst amount eg =(b1/8)*9 or b1/1.125 where b1 is the cell where you entered the incl gst amount.

You could do what you want with code as well which may be quicker if you dont want to use new columns. But again there are issues depending upon how you approach it as you need to distinguish between which cells are OK (have the exgst amt) and those that dont.

Trev O
09-10-2003, 06:43 PM
so it's a no goer then?
I'll do the IF function as you suggested for just the sure bets, eg phone, power, rates etc. and type the other variable box amounts manually like I'm doing now..
I'll be a bit better off so It'll be worth it, thanks Parry

Thanks everyone for the help also