+ Reply to Thread
Results 1 to 16 of 16

Copy formulas to adjacent columns - multiple cells and multiple sheets

  1. #1
    Registered User
    Join Date
    10-27-2010
    Location
    Swansea, Wales
    MS-Off Ver
    Excel 2007
    Posts
    47

    Copy formulas to adjacent columns - multiple cells and multiple sheets

    Afternoon,

    I have a spreadsheet, basically a more complex version of the attached.

    Where in the attached file there are "Total" formulas, in my file there are sumproduct formulas with multiple conditions. In total there are 4 tabs, each having 30 rows, each row containing 31 sumproduct formulas. So in total there are 3,720 sumproduct formulas calculating each time I change something in the spreadsheet which is slowing me down considerably.

    One solution I can think of for this problem is to write a macro that if I keep the sumproduct formulas in the first column of each sheet (that's only 120 sumproduct formulas!), the macro will copy the formulas to a particular column and paste values to the previous days column.

    For example, today is the 6th so I am calculating results for previous days performance, in the attached spreadsheet that would be column F where the results for the 5th are recorded.

    What I am hoping to achieve is to copy formulas from cells B6, 12, 18, 25 and 30 (day one formulas will always be included) into F6, 12, 18, 25 and 30 and paste values in E6, 12, 18, 25 and 30.

    The same process will be repeated every day whereby one day has to have formulas copied into it and another will have values pasted. This does not happen every day, as there will be no results on weekends.

    Any ideas?
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    10-27-2010
    Location
    Swansea, Wales
    MS-Off Ver
    Excel 2007
    Posts
    47

    Re: Copy formulas to adjacent columns - multiple cells and multiple sheets

    Morning all,

    Can anyone offer advice with this?

    Thanks.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Copy formulas to adjacent columns - multiple cells and multiple sheets

    To me the above all sounds a little like rearranging the deckchairs on the Titanic

    Though your plans are undoubtedly feasible/logical I'd suggest first getting to the root of the problem - your seemingly Volatile SUMPRODUCTs - can you post an example ?

    In short you should not be using SUMPRODUCT in a matrix - there are invariably more efficient alternatives.

  4. #4
    Registered User
    Join Date
    10-27-2010
    Location
    Swansea, Wales
    MS-Off Ver
    Excel 2007
    Posts
    47

    Re: Copy formulas to adjacent columns - multiple cells and multiple sheets

    Ha ha,

    Fair comment DonkeyOte!

    Here's the sumproduct formula from my spreadsheet:

    SUMPRODUCT(--('WI Quantity Data 2'!$B$2:$B$12500=E43),--('WI Quantity Data 2'!$G$2:$G$12500=E$3),--('WI Quantity Data 2'!$F$2:$F$12500=E$23),--('WI Quantity Data 2'!$C$2:$C$12500=E44),('WI Quantity Data 2'!$I$2:$I$12500))

    The reason I am using this formula is to extract the production output where a number of conditions are met (order number, operator login, date, process ID). In my spreadsheet there are 4 worksheets - one for each production lines, 31 days per sheet, 3 shifts per line, and 10 operations per shift (so as mentioned previously, 3,720 sumproduct formulas).

    The multitude of sumproduct formulas means everytime something is calculated in the spreadsheet, the recalculation takes 45 second (I've timed it!).

    If there is an alternative method to extracting this data that would speed up performance then I'm all for it!

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Copy formulas to adjacent columns - multiple cells and multiple sheets

    First and foremost, if you're using this exclusively in XL2007+ environments you can revert to SUMIFS which will be significantly more efficient.

    In terms of why an individual SUMPRODUCT calculation is slow:

    Each precedent range within your SUMPRODUCT contains roughly 12.5k cells and you have n ranges - ie 60-70k cells per calculation.
    SUMPRODUCT unlike SUMIFS and "traditional" formulae is "iterative" by nature (it also does not use simply the UsedRange intersect per "normal" formulae)

    My next questions relates to Volatility.

    You mention "any change" invokes a calculation yet your sample should only recalc. if the precedent ranges are themselves modified / recalculated.
    The above implies that one or more of the precedent ranges are Volatile either directly/indirectly, so...:

    a) the criteria cells... what do they contain exactly ?

    b) the 'WI Quantity Data 2' ranges ... what do they contain exactly ?
    For ex. one reference to TODAY() etc could make everything Volatile.

    It might be simpler to post a further sample that accurately reflects the real file (smaller data sets obviously but same formulae)

  6. #6
    Registered User
    Join Date
    10-27-2010
    Location
    Swansea, Wales
    MS-Off Ver
    Excel 2007
    Posts
    47

    Re: Copy formulas to adjacent columns - multiple cells and multiple sheets

    I've deleted the unnecessary data from the sheet.

    Basically, the user fills in data for works instructions every day in the "Line" tab. The sum product formulas at the end of each works instruction (e.g. cells E51, 61, 71, etc). Interrogate data from the system (WI Quantity data 2 tab) to return production quantities for given conditions.

    Does this make sense?
    Attached Files Attached Files

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Copy formulas to adjacent columns - multiple cells and multiple sheets

    Based on the sample alone there would not appear to be any Volatility in the model.

    Assuming you need to persist with SUMPRODUCT for sake of backwards compatibility (assuming saved as .xls):

    1. add a pre-emptive IF to avoid calculating the SUMPRODUCT unnecessarily, eg:

    Please Login or Register  to view this content.
    2. consider using Dynamic Named Ranges re: WI sheet so as to avoid the need for excess capacity - though use an INDEX based construct rather than OFFSET (previously mentioned Volatility issues)
    effect of this will however be marginal depdending on volume of spare capactity and offset by overhead in calculating the DNR

    3. consider creating a key on WI sheet so you can avoid SUMPRODUCT and revert to SUMIF, eg:

    Please Login or Register  to view this content.
    Your summary calc then becomes:

    Please Login or Register  to view this content.
    though less elegant this will be significantly more efficient than the approach you are presently using
    (to the extent that you could also use entire column references in the SUMIF if preferred)

    I can not stress enough just how significant the impact of 3. would be on the performance of your model !



    However, if backwards compatibility is not a concern then use SUMIFS (like SUMIF this will be significantly more efficient)

    Please Login or Register  to view this content.
    Last edited by DonkeyOte; 01-11-2011 at 05:27 AM. Reason: typo

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Copy formulas to adjacent columns - multiple cells and multiple sheets

    edit: I figured I should at least provide some basic analysis re: performance impact of suggestion 3.

    Calculating the Line sheet using the existing SUMPRODUCT setup took approx 7 secs on my machine (4GB RAM)

    The same sheet but with SUMIF in place (and requisite "Key" having been inserted on WI Quantity Data 2 sheet) took approx. 1.5 seconds to calculate

    On % and time basis - quite significant I'd say.

    If you then add in the pre-emptive IF as outlined that would drop to "instantaneous"
    Last edited by DonkeyOte; 01-10-2011 at 12:32 PM. Reason: repetition in narrative

  9. #9
    Registered User
    Join Date
    10-27-2010
    Location
    Swansea, Wales
    MS-Off Ver
    Excel 2007
    Posts
    47

    Re: Copy formulas to adjacent columns - multiple cells and multiple sheets

    Absolutely perfect!

    I didn't know there was a VLOOKUPS function! (is it new in excel 2007/10?) That's exactly what I was looking for but somebody gave me a solution using SUMPRODUCT and I thought I was stuck with it.

    No concerns about backwards compatibility, so it works fine.

    One last question, our night shift runs from 10PM to 6AM the following day so where the VLOOKUPS function picks up the data as today's date, this will not pick up the last 6 hours of the shift. I overcame this in the SUMPRODUCT formula by adding the formula together but changing the date to tomorrows date and including, time field (another column) = <65000 (where 65000 = 6:30 AM).

    Any ideas how I can overcome this problem using VLOOKUPS? Basically I need the exact same fields, but also anything where the day is the following day and time is before 6:30.

    Thanks

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Copy formulas to adjacent columns - multiple cells and multiple sheets

    Quote Originally Posted by swanseaexcel
    I didn't know there was a VLOOKUPS function! (is it new in excel 2007/10?)
    Presumably by VLOOKUPS you mean SUMIFS ?

    Quote Originally Posted by swanseaexcel
    One last question, our night shift runs from 10PM to 6AM the following day so where the VLOOKUPS function picks up the data as today's date, this will not pick up the last 6 hours of the shift.
    I'm afraid I don't follow the above in the context of the formulae we've covered in this thread.
    Perhaps you could elaborate by means of reference to a cell in your most recent sample file ?

  11. #11
    Registered User
    Join Date
    10-27-2010
    Location
    Swansea, Wales
    MS-Off Ver
    Excel 2007
    Posts
    47

    Re: Copy formulas to adjacent columns - multiple cells and multiple sheets

    Yes, sorry DonkeyOte by VLOOKUPS I mean SUMIFs.

    The following code you provided works perfectly for the morning and afternoon sections of the report:

    Please Login or Register  to view this content.
    But for night shift I need something that not only retrieves data for the current day, but also the following day for results before 6:30AM. Something like the following:

    =IF(OR(B43="",B$3)="",0,SUMIFS('WI Quantity Data 2'!$I$2:$I$9523,'WI Quantity Data 2'!$B$2:$B$9523,E43,'WI Quantity Data 2'!$G$2:$G$9523,E$3,'WI Quantity Data 2'!$F$2:$F$9523,E$23,'WI Quantity Data 2'!$C$2:$C$9523,E44))+IF(OR(B43="",B$3)="",0,SUMIFS('WI Quantity Data 2'!$I$2:$I$9523,'WI Quantity Data 2'!$B$2:$B$9523,E43,'WI Quantity Data 2'!$G$2:$G$9523,F$3,'WI Quantity Data 2'!$F$2:$F$9523,E$23,'WI Quantity Data 2'!$C$2:$C$9523,E44,'WI Quantity Data 2'!$O$2:$O$9523,A1))

    Where the additional code adds data for the following day (e.g. Cell E3 would be the 4th and F3 would be the 5th) and cell A1 would contain <65000. When I try this it doesn't work though, presumably because the SUMIFS can't process the less than symbol?

    Does this make sense?

    Thanks.

  12. #12
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Copy formulas to adjacent columns - multiple cells and multiple sheets

    Quote Originally Posted by swanseaexcel
    Does this make sense?
    To be honest it's quite difficult to follow given the sample does not contain Time entries (presumably column O in your file) and there is no value in A1 (which you appear to be using as a Time criteria of some sort)

    I'd suggest posting an updated sample that better reflects these latest requirements.

  13. #13
    Registered User
    Join Date
    10-27-2010
    Location
    Swansea, Wales
    MS-Off Ver
    Excel 2007
    Posts
    47

    Re: Copy formulas to adjacent columns - multiple cells and multiple sheets

    Apologies again for being a bit vague DonkeyOte,

    The attached should illustrate it, column O now contains times (in our systems odd time format, 65000 = 06:30) and A1 has <65000.

    I want the original SUMIFS formula to remain, but also add in data from the following day where the time field has an entry of less than 65000.

    Thanks!
    Attached Files Attached Files

  14. #14
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Copy formulas to adjacent columns - multiple cells and multiple sheets

    Perhaps I'm missing something fairly fundamental but does this requirement not make your prior calculations invalid ?

    By the above I mean: you are seemingly not excluding these same overnight entries from the following days morning shift calculations (as far as I can tell)

  15. #15
    Registered User
    Join Date
    10-27-2010
    Location
    Swansea, Wales
    MS-Off Ver
    Excel 2007
    Posts
    47

    Re: Copy formulas to adjacent columns - multiple cells and multiple sheets

    That is safeguarded against, as one of the conditions of the SUMIFS formula is the user inputting the data on the shift has to match (Column F in "WI Quantity data 2" tab and row 23 in "Line" tab).

    The same user will not be on night shift AND the following morning shift so this should be prevented.
    Last edited by swanseaexcel; 01-12-2011 at 05:48 AM.

  16. #16
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Copy formulas to adjacent columns - multiple cells and multiple sheets

    I know I sound like a broken record and perhaps I'm not making myself clear... or perhaps you are and reality is far simpler than I envisage ... let's assume:

    Login: MAD

    Works Evening Shift Day 1 which crosses into Day 2 morning (pre 65000)

    Works Afternoon Shift Day 2

    When you calculate the Afternoon shift for Day 2 for Login:MAD how exactly are you discounting Day 2 morning entry in your Afternoon Calculation ?
    (there is no Time criteria in that formula as far as I can tell)

    Same holds true I guess if MAD works evening shift Day 2 and there is a record for that Date - it would include the Day 2 morning entry also surely ?

    Does a Login working the Evening shift generate two entries (one Day 1 and one Day 2) or is Day 2 an exception (with no Day 1 entry) ?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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