Hi All,
It's been a while since I've posted on here (usually in the formula and function section). I hope everybody is well.
Attached is a spreadsheet I want to use to conduct a monthly Accounts Review process. To give some context, I am a finance manager of an overseas office and report to Group Finance/Head Office. I submit monthly management accounts which are then reviewed by Head Office and Head Office typically query any anomalies etc. With reference to the spreadsheet:
- Row 20 will be used to insert the first query that Group Finance has about my monthly management accounts. Group Finance will then send the sheet across to me by email.
- Upon receiving the email, I will populate row 21 with my response to their question. I will then send the sheet across to Group Finance by email
- Upon receiving the email Group Finance will then select from the drop down menu in cell AD21, whether my response satisfies their query
I would like the following to happen but I have no clue about VBA/Macros and I'm pretty sure a VBA/Macro will be needed here:
- If Group Finance selects 'No' from cell AD21, I would like two more rows to automatically insert in row 22 and 23 (essentially pushing the current row 22 down to row 24) and these two new rows will be carbon copies of rows 20 and 21. Essentially, the addition of these two new rows provides space for Group Finance to clarify their question and for me to provide another response
- Secondly, you will also see that I have split the document into three sections; Income Statement (reserved for questions Group Finance has on the P&L), the Statement of Financial Position (reserved for questions Group Finance has on the Balance Sheet) and Other Queries (reserved for any non-financial queries that Group Finance may have e.g. did the local office recruit any new employees during the month etc.). Within each section you will notice that I currently have 3 'question and response sections' for Group Finance queries, indicating that I expect Group Finance to have 3 queries for each section. This may not be the case however - Group Finance may have one question in relation to the P&L, 5 questions in relation to the Balance Sheet and two questions under 'Other Queries'.
I don't want to trouble Group Finance with the task of deleting empty 'question and response sections' if they have less than 3 queries, or trouble them by making them add extra ones if they have more than 3 questions. I would therefore like another macro/VBA enabled function whereby (on the basis that I first reduce the spreadsheet so that each section has only one 'question and response section') if Group Finance have additional queries, they click on a small '+' button (which will have to be created) and this will automatically insert a new 'question and response section' beneath the first 'question and response section'.
Bookmarks