+ Reply to Thread
Results 1 to 6 of 6

(1) Insert 2 rows if cell contains a value and (2) insert section by clicking a '+' icon

  1. #1
    Banned User!
    Join Date
    09-26-2015
    Location
    Manchester
    MS-Off Ver
    2013
    Posts
    218

    (1) Insert 2 rows if cell contains a value and (2) insert section by clicking a '+' icon

    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'.
    Attached Files Attached Files

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,821

    Re: (1) Insert 2 rows if cell contains a value and (2) insert section by clicking a '+' ic

    Give the attached file a try.
    Attached Files Attached Files
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Banned User!
    Join Date
    09-26-2015
    Location
    Manchester
    MS-Off Ver
    2013
    Posts
    218

    Re: (1) Insert 2 rows if cell contains a value and (2) insert section by clicking a '+' ic

    Sorry for the delayed reply, it's only morning time where I am.

    Thanks Mumps for taking a look. I don't know why, but the spreadsheet you attached is non-responsive when I make a change:

    - If I make a change to cell AD21 or AD29 or AD37 to select 'No', no extra rows are added. In stead the spreadsheet just freezes, like it's crashed
    - If I select (from the VBA drop down menu) a section that I would like to add a 'Question and Response' section to and then click on the blue VBA box, the same thing happens i.e. no new section is added, instead the spreadsheet just freezes.

    I don't know if this is an issue of processing power or something, but it isn't working as intended on my laptop. Does it work on yours?

    EDIT:
    OK so I re-opened a new sheet several times and tried the exact same process again the same task expecting a different result and a couple of times I got the desired result (what was it Einstein said again??). For some reason, sometimes extra rows are being inserted when I select option 'No' from cell AD21 and other times the spreadsheet just freezes. The times when I can insert two new rows, I'm limited to doing this just once i.e. if I select 'No' in cell AD21, two new rows appear in rows 22 & 23. If I select 'No' again from cell AD23, the spreadsheet theoretically should insert another two new rows and so on and so forth. Instead, it freezes.

    I'm not having any luck whatsoever with inserting new 'Question and Response' sections and often times, I can't even leave your drop down list to click on the macro - the cell that contains your drop down list remains selected and I can't do anything about it.
    Last edited by STUARTXL; 08-30-2017 at 01:36 AM.

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,821

    Re: (1) Insert 2 rows if cell contains a value and (2) insert section by clicking a '+' ic

    I can't seem to reproduce the problems you are experiencing. I have tried many different combinations and it has worked properly. Can you attach an exact copy of the file that is giving you problems so I can test it on my machine?

  5. #5
    Banned User!
    Join Date
    09-26-2015
    Location
    Manchester
    MS-Off Ver
    2013
    Posts
    218

    Re: (1) Insert 2 rows if cell contains a value and (2) insert section by clicking a '+' ic

    Attached the sheet. This is simply the version you attached, downloaded to my machine, which makes me wonder, is it possible that there is a reference to a document on your machine within the macro, that may be preventing the macro from working on my machine?
    I don't know if this is a potential problem, but I know when I have written formulas on Excel in the past, that reference an external document on my laptop, the formula breaks down when downloaded on another individuals computer (as they won't have the external document on their machine).
    Attached Files Attached Files

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,821

    Re: (1) Insert 2 rows if cell contains a value and (2) insert section by clicking a '+' ic

    There are no links in the macro or file anywhere and the macro is working properly on my machine. I even tried it with both Excel 2010 and Excel 2013 and there were no problems in either version. I'm not sure what could be causing the problem on your machine. Keep in mind that anyone using this file must have macros enabled in Excel or the macros won't run.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 10-14-2016, 05:33 PM
  2. Replies: 2
    Last Post: 09-11-2016, 12:40 PM
  3. Replies: 3
    Last Post: 09-24-2015, 06:46 AM
  4. [SOLVED] Enable the Insert icon in Developer tab > Controls > Insert in Excel 2010
    By pedapudipradeep in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-03-2014, 12:41 PM
  5. Excel 2007 : insert icon set in a cell
    By jonicute in forum Excel General
    Replies: 13
    Last Post: 01-16-2009, 12:04 PM
  6. 'Insert Rows Icon'
    By ginger in forum Excel General
    Replies: 1
    Last Post: 06-07-2006, 09:00 PM
  7. Insert Bitmap or Icon depending on a cell value
    By hhalle in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-08-2006, 06:43 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1