Spreadsheet Autosort

  • Thread starter Grayfox
  • 5 comments
  • 1,983 views
11,669
Australia
Australia
I_Grayson_Fox_I
I am making a fuel tracking spreadsheet.
Is their a way to have the spreadsheet automatically sort the data(by descending date) when entered and shift all data one row down?

I essentially want to enter data in row 9(odometer, fuel type, litres and fuel price(rest is done automatically)), then have that new data moved one row down along with all per-existing data moved with it.
I do not know anything about spreadsheet scripting.
1672990137303.png

Currently have test data in cells for testing.

Thanks
 
Last edited:
I think you should be able to record a macro for that. I haven’t used macros in a long while though, so I’ll have to test that.

Edit: Just to check, is that Excel of the Google abomination?

Edit 2: Yep, a macro will do the trick. Go to the developer tab (ribbon?) and record a new macro. When recording, do the task you want to have automated and then click stop recording when you've finished. On the same tab (ribbon?) you can then choose to insert a button, which you can link to the macro you just recorded.

I'd recommend storing the data in a table, that way you can easily sort the data by a specific column, no matter how many rows of data you've got.

1673018097418.png


Here's what the developer tab (ribbon?) looks like in Swedish. I assume the icons are the same in the English version :D

1673018450881.png
 
Last edited:
Edit: Just to check, is that Excel of the Google abomination?

Edit 2: Yep, a macro will do the trick. Go to the developer tab (ribbon?) and record a new macro. When recording, do the task you want to have automated and then click stop recording when you've finished. On the same tab (ribbon?) you can then choose to insert a button, which you can link to the macro you just recorded.

I'd recommend storing the data in a table, that way you can easily sort the data by a specific column, no matter how many rows of data you've got.

View attachment 1221066

Here's what the developer tab (ribbon?) looks like in Swedish. I assume the icons are the same in the English version :D

View attachment 1221081

I use Libre Calc since I use linux mint as my OS.
 
My knowledge of libre office is extremely limited, however you need to create a macro which records when a specific cell changes. Thus:

Sub cell_onChange (oEvent As Variant) 'Macro responds to changes in B1 only
If oEvent.AbsoluteName <> "$SheetName.$I$9"
Then
Sheet.Rows.insertByIndex(8,1)
Exit Sub
End Sub

What this will do is wait until you have put the price per litre into the sheet as the last value, then insert a line above it at row 9. If that doesn't quite work change 8 to 7. Please let me know if it works.
 
My knowledge of libre office is extremely limited, however you need to create a macro which records when a specific cell changes. Thus:

Sub cell_onChange (oEvent As Variant) 'Macro responds to changes in B1 only
If oEvent.AbsoluteName <> "$SheetName.$I$9"
Then
Sheet.Rows.insertByIndex(8,1)
Exit Sub
End Sub

What this will do is wait until you have put the price per litre into the sheet as the last value, then insert a line above it at row 9. If that doesn't quite work change 8 to 7. Please let me know if it works.
I get this

1673064047703.png
 
Hmmmm, you could try putting the whole instruction in one line so that it looks like this:

If oEvent.AbsoluteName <> "$SheetName.$I$9" Then Sheet.Rows.insertByIndex(8,1)
Exit sub
End sub
 
Back