| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 38475 | 2003-10-08 07:19:00 | excel function | Trev O (452) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 181356 | 2003-10-08 07:19:00 | 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 |
Trev O (452) | ||
| 181357 | 2003-10-08 07:38:00 | Why dont you have an IF formula in the column I etc so the amount is blank unless the condition you want is met? | parry (27) | ||
| 181358 | 2003-10-08 07:45:00 | I'm not sure how to do that... the expence type could end up in any column ( rates, petrol, vehicle etc) |
Trev O (452) | ||
| 181359 | 2003-10-08 07:49:00 | 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 |
wotz (335) | ||
| 181360 | 2003-10-08 07:57:00 | 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!) |
Trev O (452) | ||
| 181361 | 2003-10-08 08:04:00 | 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. | parry (27) | ||
| 181362 | 2003-10-08 08:18:00 | 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... |
Trev O (452) | ||
| 181363 | 2003-10-08 09:41:00 | 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. | parry (27) | ||
| 181364 | 2003-10-08 09:47:00 | Heres a very simple example using he OR and IF functions in columns D-F. www.geocities.com |
parry (27) | ||
| 181365 | 2003-10-08 10:02:00 | 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 ... |
godfather (25) | ||
| 1 2 | |||||