+ Reply to Thread
Results 1 to 14 of 14

Auto fill up cells if the above cell is blank

  1. #1
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Auto fill up cells if the above cell is blank

    hi friends,

    i am attaching a sample book for your ref.
    sheet 1 is the present set up. this is a letter that we generate to various depts.
    sheet 2 is what i want. this saves spaces in order to get the letter on one page.

    what i want is to fill up the empty spaces in column left by moving part of data from columns right.

    the FRUITS (CONTD....) part in column left should come leaving one blank row up. here the blank row in the column left is row 8.
    so from row 11 onward the splitting of data will start.

    however this has to be automatically done. for instance if the column VEGETABLES got date till the 9th row then the header (FRUITS CONTD...) should appear in the 11th row keeping a space in between. this should happen automatically when ever data is entered.

    many thanks and look forward to hear from you.

    regards
    rizvi
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Auto fill up cells if the above cell is blank

    This proposal employs a helper column which may be moved and/or hidden for aesthetic purposes.
    The helper column, H, is populated with various summing and counting formulas.
    As for the output the first column, J, is populated using:=IF(ROW()=H$5,"Fruit Cont…",IF(ROW()=H$6,"Product",IF(ROW()>H$6,INDIRECT("D"&SUM(H$11,MAX(ROW()-H$6,0))),B3)))
    Column K is populated using:=IF(ROW()=H$6,"Quantity",IF(ROW()>H$6,INDIRECT("E"&SUM(H$11,MAX(ROW()-H$6,0))),C3))
    Columns L:M are populated using: =IF(ROW()<=$H$11,D3,"")
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Re: Auto fill up cells if the above cell is blank

    Hi JeteMc,

    This is exactly what I want, BEAUTIFUL!!! Thank you very much... However, I have just realised that there will be 3 columns instead of 2 under VEGETABLES and only 2 columns under FRUITS. So when part of the FRUITS come into the left side, the first 2 columns have to merge. An exact set up is shown in SHEET 2. Please observe the border lines and the headers are in thicker border lines. Therefore, when part of the FRUITS data is moved to the left the header with thick borders too should appear.
    I hope you could assist me plse...
    but thanks again for the help so far.
    attached sample work book

    best wishes
    Rizvi
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Auto fill up cells if the above cell is blank

    Attached is as close as I can get to the table shown on Sheet 2. Perhaps at this point someone with VBA knowledge can accomplish the rest of the request.
    Let us know if you have any questions.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Auto fill up cells if the above cell is blank

    Quote Originally Posted by RIZVI View Post
    So when part of the FRUITS come into the left side, the first 2 columns have to merge. An exact set up is shown in SHEET 2.
    I would advise you to rethink the above and consider using the first 2 columns for the data, leaving the third column empty.
    Given that you will need to merge and unmerge the cells every time the data changes, you are creating a recipe for armageddon! I doubt that anyone will be keen to help you with such a task.

  6. #6
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Re: Auto fill up cells if the above cell is blank

    Hi JeteMc,

    Many thanks mate, beautifully done and very much appreciated here. Thank you!

    Hello Jason.b75
    Thanks for the comment. actually what we do is enter the data, save, then make the formatting such as border lines, deleting empty rows (we got a macro running for this) etc and then print (hard copies - not PDF). now if we find a mistake done after formatting then we simply exit without saving, open up the worksheet again, make corrections to data, then save, re-do the formatting and print and exit without saving. using JeteMc's worksheet I re-organized the working pattern (sheet is attached).

    1. the conditional formatting was removed and the thick border lines were created just the way i want
    2. created a format button, recorded a macro, assigned it to this button.
    3. now, when we finish entering data, the format button will be pressed and the document will be formatted as per our requirement.
    4. the area that we work in the sheet is restricted to range K3:O15 (based on JetMc's sheet - this work area will not change until the delete empty rows macro is run)

    this is all what is required by me. this macro was recorded assuming that the data entered is final and ready for formatting.

    using the format button, what we require is a VBA code to find the "Food Cont..." (in our case it's K10) and run the formatting formatting thereafter.

    any help is appreciated.

    regards
    Rizvi
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Auto fill up cells if the above cell is blank

    Take a look at this, variation of JeteMc's lsuggestion from post #4. No macro involved, just formulas and conditional formatting!

    It needs a bit of fine tuning, the conditional formatting goes wrong if there are not more fruits than vegetables, but I'm not going to spend more time on it until you've had a look at it.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Re: Auto fill up cells if the above cell is blank

    Hi Jason.b75

    Thanks. Tested, but it doesn't do what I require. I think a VBA is required here as JeteMc suggested.
    Regards
    Rizvi

  9. #9
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Auto fill up cells if the above cell is blank

    Quote Originally Posted by RIZVI View Post
    Tested, but it doesn't do what I require.
    If you don't say what it is not doing correctly then I can't fix it.

  10. #10
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Re: Auto fill up cells if the above cell is blank

    Hi Jason.jb75,

    JeteMc's format is perfect. actually it's a part of which i want. there are only 11 rows data entry for both products (rows 5 to 15) nothing more. but less, yes. concentration should be first given to the VEGETABLES side. depending on the data in put, the FRUITS side will then divide the entries and stow part of it in the VEGETABLES side as well. just like what JeteMc did. the issue is with the formatting of border lines and merging cells and this could be done, i believe, only by using VBA. the VBA has to first identify the "Fruits Contd..." cell in the VEGETABLES side and then wok on the formatting part.

    Thanks in advance for your assistance Jason.jb.75...

    Best Wishes
    Rizvi

  11. #11
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Auto fill up cells if the above cell is blank

    The border lines can be done easily enough, as you will see from the modified file that I provided for you to try.

    The merged cells are a recipe for disaster, they are more than likely going to be a problem with future changes.

    I don't see any practical reason for having them, which is why I suggested an alternative way of doing it without. If you're not happy with the way that I formatted it, I will try to do it in a way that matches a sample provided which does not invlove merged cells.

    If you are insistant on having the merged cells, I suggest that you wait to see if anyone else takes up the challenge here, or failing that try the commercial services sub-forum.

  12. #12
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Re: Auto fill up cells if the above cell is blank

    Appreciated Mate, Thank you.

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Auto fill up cells if the above cell is blank

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

  14. #14
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Re: Auto fill up cells if the above cell is blank

    Will do JeteMC, hope that someone could assist to get format going with a VBA.

+ 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. Auto fill blank cells given Date Range
    By rkclutch81 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-16-2015, 11:29 PM
  2. Macro to auto-fill all the blank cells ???
    By canefan17 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-27-2015, 05:34 PM
  3. [SOLVED] Auto Fill from other columns and using IF with related to blank or Zero cells
    By Psionicrnd in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-05-2014, 05:45 AM
  4. Auto fill & auto blank cell
    By sushant.gore in forum Excel General
    Replies: 1
    Last Post: 10-07-2010, 01:06 PM
  5. Auto fill blank cells with data in a cell above
    By StudentMod in forum Excel General
    Replies: 4
    Last Post: 05-09-2008, 04:37 PM
  6. [SOLVED] blank cells with auto fill in excel
    By derecl in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-02-2005, 06:20 PM
  7. Auto Fill blank cells
    By maddz in forum Excel General
    Replies: 0
    Last Post: 02-01-2005, 05:49 AM

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