woowoowoo 12:11 06 Jan 09
Locked

Hi I am making up an excel spreadsheet to work out my finances for the tax year. Certain activities have the same mileage and cost every time I do them eg going to work which involves mileage, car park fees and printing out my work for the day.
So what I would like to do is just tick a box for 'going to work' and all the other info gets filled in automatically. I am sure there must be a way to do this and I have tried to google the answer but dont know what this function is called.
Could anybody help with this please?

Noldi 12:20 06 Jan 09

I would have thought the IF function.

If you entered W in a cell for "Work" then the IF fuction would enter the data.

Noldi

Noldi 12:25 06 Jan 09

Here is a Excel Formula dictionary if it helps.

Noldi

Picklefactory 13:00 06 Jan 09

I don't think simple IF statement will do this, as the formula would need to reside in the cell requiring value which would be manually overwritten if 'w' was not required. I think this wiil be a bit of code.

Jim_F 14:04 06 Jan 09

You could do it using a formula like the following in (say) Sheet1!B4:

=IF((Sheet1!A4="w"),Sheet2!B1,"")

It uses Sheet2 as a lookup for static data but this can be tedious in terms of having to cut and past the formulae to all cells you want populated and train excel that you want to increment Rows by sheet1 but not in sheet2.

Code is better depending on how complex your data is - usings a loop to scan down for the first empty cell in Column A and then another loop to fill in data across the sheet starting the the date. This could be tied to a button but I'd still use the second sheet as a lookup to save hardcoding the data.

woowoowoo 14:50 06 Jan 09

Cheers for all your help - I shall go and give it a try and let you know how I get on!

Picklefactory 15:32 06 Jan 09

Can you give some more detail on how the sheet is laid out? What does each row/column contain?

VoG II 15:38 06 Jan 09

I would have thought that VLOOKUP would be ideal for this click here

Picklefactory 15:52 06 Jan 09

This would input a set value in one cell based on anything being entered into a cell in another column. In the example below entering any value into a cell in Col E would place "10" into the cell immediately to it's right. This could be adapted to suit your needs.
Post back if you're unfamiliar with using VBA and someone will help.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldValue
Dim NewValue
If Target.Count > 5 Then Exit Sub 'The number here corresponds to the column ie Col A would equal 1, 5 = Col E
If Target.Column <> 5 Then Exit Sub ' As above, these two values should be the same
Application.EnableEvents = False
NewValue = Target.Value
Application.Undo
OldValue = Target.Value
Target.Value = NewValue
If OldValue = "" Then
With Target.Offset(0, 1) 'Offset 0 rows by 1 column to the right 0,1
.Value = 10 'Insert fixed value to offset cell, in this case I've used 10
End With
End If
Application.EnableEvents = True
End Sub

Picklefactory 15:55 06 Jan 09

Hope that's not too complicated, if I'm understanding correctly you want to use a particular column for eg miles and either enter data to that column manually or, put a tick or somesuch into another column to auto enter a fixed value to the miles column.

woowoowoo 16:36 06 Jan 09

Hi all thanks for your help - just to explain - I am self employed selling books online and supplement this with casual work. So in excel I have a page for each month which I fill in the details of earnings and expenses. The fixed expenses are mainly mileage to PO or to work and paper used. I would like to just tick a box, say for trip to PO or wherever I have to travel to work say work 1, work 2 or work 3 and the mileage and paper use would be filled in automatically.
At the moment I just type it in day by day and at the end of the month and year it all gets added up.
Thanks for your input - I am getting some ideas now.

This thread is now locked and can not be replied to.

Intel Coffee Lake 8th-gen Core processors release date rumours

1995-2015: How technology has changed the world in 20 years

Framestore’s haunting post-WWII title sequence for new BBC series SS-GB

Best iPhone games 2017 | Best iPad games 2017: 162 fantastic iOS games that you need to play right…