+ Reply to Thread
Results 1 to 7 of 7

Refining a formula

  1. 02-17-2017, 05:41 PM #1
    TiedBassDrummer
    TiedBassDrummer is offline
    Registered User
    Join Date
    02-17-2017
    Location
    Sparks, NV
    MS-Off Ver
    2010
    Posts
    4

    Refining a formula

    Hello,

    I am trying to make up a template to make keeping a summary of the waves we pull at work easier. I have gotten close to the result that I want, but have had a problem with my idea working intermittently.

    My plan was to have the cover sheet protected so that my co-workers and I don't have to worry about accidentally breaking the formulas. The formulas on the cover sheet are meant to look at their respective sheets and mirror the cells, in real-time. We export our data from another program into Excel and then trim it so that we have the first four columns (WAVE, Description, CNTORDER, QTYORDER) and then type in the last two columns (Printer and Printed).

    On the Morning Waves sheet, everything copies over to the Cover Sheet without a problem. When it comes to the Extra Waves and Express Waves sheets, things start to break down. When I do the same process to the Extra Waves sheet, I'll get my expected result about 70% of the time. The other 30% being only a few lines showed up, or nothing showed up. When I do the same thing to the Express Waves sheet, it almost never works. The best I've gotten was a few lines to show.

    Here is the kicker though, if I take everything from the Morning Waves sheet and just paste it to the other sheets, the formulas on the Cover Sheet will work as intended.

    Assuming all that made sense, is there a way to refine the formula I have now, or is there a better one that I can use?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-17-2017, 06:17 PM #2
    daffodil11
    daffodil11 is offline
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Refining a formula

    Your spreadsheet works a little bit better with INDEX(..SMALL(IF where A5:

    =IFERROR(INDEX('Morning Waves'!A$1:A$100,SMALL(IF('Morning Waves'!$A$1:$A$100<>0,ROW('Morning Waves'!$A$1:$A$100)),ROW(A1))),"")

    Copy over and down, and repeat same formula from A5 in H5 and O5 with different sheet references.

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!
    Reply With QuoteRegister To Reply
  3. 02-17-2017, 07:20 PM #3
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,624

    Re: Refining a formula

    In template file, cover sheet, under morning waves in column A you have:
    A6 
    Please Login or Register  to view this content.
    A7 
    Please Login or Register  to view this content.
    , etc down column A
    Notice that Column(A5) & Column(A6) both return 1 since column A is the first column and it does not matter which row in the column you point to. So that ALL cells in column A are looking at cell A1 on the Morning waves sheet to see if it is blank. The formulas should be looking at the rows of column A as it is copied down:

    A6 
    Please Login or Register  to view this content.
    As another example, take the Extra Waves segment of cells. In H5 you have:
    Please Login or Register  to view this content.
    .

    Since column H is column #8, your first test for blank is looking at A8. If there are less than eight records you will never copy any over. If ther are more, then the first seven will be skipped.

    All of your formulas using column(x) for the blank test are making the same mistake and, since you are incrementing the column to the right as you copy it, that means that you are testing for the first blank farther and farther down column A.
    Last edited by protonLeah; 02-17-2017 at 07:24 PM.
    Ben Van Johnson
    Reply With QuoteRegister To Reply
  4. 02-20-2017, 02:17 PM #4
    TiedBassDrummer
    TiedBassDrummer is offline
    Registered User
    Join Date
    02-17-2017
    Location
    Sparks, NV
    MS-Off Ver
    2010
    Posts
    4

    Re: Refining a formula

    It works until I start trimming everything down to what I want to keep. Once I delete a column from one of the other work sheets $A$1:$A$100<>0 turns into #REF!.
    Reply With QuoteRegister To Reply
  5. 02-20-2017, 02:48 PM #5
    TiedBassDrummer
    TiedBassDrummer is offline
    Registered User
    Join Date
    02-17-2017
    Location
    Sparks, NV
    MS-Off Ver
    2010
    Posts
    4

    Re: Refining a formula

    So should my formula look like this across all the columns under Morning Waves on my Cover Sheet?

    A6
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    B6
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    C6
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    D6
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    E6
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    F6
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 02-20-2017, 11:16 PM #6
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,624

    Re: Refining a formula

    I think that will fix it.
    Reply With QuoteRegister To Reply
  7. 02-21-2017, 10:57 AM #7
    TiedBassDrummer
    TiedBassDrummer is offline
    Registered User
    Join Date
    02-17-2017
    Location
    Sparks, NV
    MS-Off Ver
    2010
    Posts
    4

    Re: Refining a formula

    Thank you!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Refining deposited data
    By jbrooks1988 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-13-2017, 12:54 PM
  2. [SOLVED] Help refining a formula - not sure if it needs to VBA or not.
    By Jveto in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-17-2016, 01:43 PM
  3. [SOLVED] Modifying n Refining VBA
    By dineshsachidananda in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-07-2015, 02:56 PM
  4. Refining Price Lists
    By VFSFINANCE in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-16-2014, 11:06 AM
  5. Refining VBA Code
    By johannes2008 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-21-2011, 08:37 PM
  6. Formula refining
    By jackandjill in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-23-2010, 08:32 AM
  7. Refining Countif
    By asg2307 in forum Excel General
    Replies: 5
    Last Post: 02-14-2006, 03:20 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1