OnePlus 5 review
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?
Thanks for reading
Here is a Excel Formula dictionary if it helps.
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.
You could do it using a formula like the following in (say) Sheet1!B4:
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.
Cheers for all your help - I shall go and give it a try and let you know how I get on!
Can you give some more detail on how the sheet is laid out? What does each row/column contain?
I would have thought that VLOOKUP would be ideal for this click here
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)
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
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
Application.EnableEvents = True
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.
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.