Help with Accounts Spreadsheet formulas please

Hi Everybody,

I have gained a lot of info from reading these forums over the last year and put my spreadsheet together as best as i can but i'm a bit out of my depth now so its time to ask for help

i've attached an example of my yearly accounts for my self employed work but i would like to make it more fluid. Any help on all or one part of the formulas for these things would be great or just point me to the correct formula name so i can read up about them more would be very much appreciated .. Thankyou :


Column A - I would like to click in a box and have a pop up calender open, starting at the date in the box above so i can choose the next date... currently i have a pop up third party calender but it always starts at todays date.


Column C - I would like to be able to click in a box and as soon as i type R or E it automatically fills with the next reference number in sequence for that particular month .. ie if it is april and i type R, it will fill the box with Rec1718-4/000 .. next box i type R would have Rec1718-4/001.


How to turn column C,D & E to a grey colour as shown once the C column has been entered


Column D,E,G & I - i would like these to remember the data that is always the same

And last but not least, After i enter a Date in Column A, i would like it to automatically sort it into date order within the month section rather than me having to highlight everything and selecting the sort icon up top

Originally Posted by NongYo

Column A - I would like to click in a box and have a pop up calender open, starting at the date in the box above so i can choose the next date... currently i have a pop up third party calender but it always starts at todays date.

There is no third-party calendar control in your file. I would rather see if it's possible to fix what you have first rather than installing a new calendar control.

Column C - I would like to be able to click in a box and as soon as i type R or E it automatically fills with the next reference number in sequence for that particular month .. ie if it is april and i type R, it will fill the box with Rec1718-4/000 .. next box i type R would have Rec1718-4/001.

What is your numbering scheme? Does the 4 in your example mean April? What do the 17 and 18 mean? The format for E is different than R--do you just leave out the month number?

How to turn column C,D & E to a grey colour as shown once the C column has been entered

That is easy with conditional formatting. But I'm not sure how to interpret your example, because there are many rows that have data in the C column but are not gray.

Column D,E,G & I - i would like these to remember the data that is always the same

What do you mean by "remember"? What data is always the same? Can you describe the steps that the user would take and the results you want to see in the worksheet?

And last but not least, After i enter a Date in Column A, i would like it to automatically sort it into date order within the month section rather than me having to highlight everything and selecting the sort icon up top

I don't see anything that looks like a "month section". In your first section your totals are labelled "balance for February" but your data has dates from April, June, and September.

Are you looking for someone who can explain to you how to do these things, or are you looking for someone to do it for you? Most of this is going to involve writing VBA code.