+ Reply to Thread
Results 1 to 4 of 4

Formulas not working on replicated sheet

  1. #1
    Forum Contributor
    Join Date
    11-24-2013
    Location
    Huddersfield, England
    MS-Off Ver
    2013
    Posts
    104

    Formulas not working on replicated sheet

    Hi Guys

    On the attached sheet can anyone tell me why the profits by month table formulas starting in AS78 on "BTL Place" sheet aren't working like they do on the "BTL Win" sheet?. The info on "BTL Place" is copied from "BTL Win" using formulas so wondered if this is why?

    Many Thanks

    Jonathan
    Attached Files Attached Files

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Formulas not working on replicated sheet

    That is because in the BTL PLace sheet starting returns a "" if the corresponding cell in the Win sheet is empty. This returns a VALUE error
    Eventually replace your formula with ='BLT Win'!A3 and drag down, hide the 00/01/1900 in the empty cells one way or another and change your formula to

    =SUMPRODUCT(($A$2:$A$23999<>0)*(MONTH($A$2:$A$23999)=MONTH($AR77))*(YEAR($A$2:$A$23999)=YEAR(AR77)))

    ( the diff in range comes from the fact I unmerged the headers)

    Better still, replace the hard coded ranges with dynamic ranges or Named dynamic ranges

    EDIT if you select cells A88 to A24000 and press " delete", AS78 and others return a value
    Last edited by Pepe Le Mokko; 02-17-2016 at 04:19 AM.

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Formulas not working on replicated sheet

    You have "null" ("") in the date column A which causes value errors. Need to be numeric 0 ?

  4. #4
    Forum Contributor
    Join Date
    11-24-2013
    Location
    Huddersfield, England
    MS-Off Ver
    2013
    Posts
    104

    Re: Formulas not working on replicated sheet

    Ah right ok, thanks. Works now

    Thanks very much

    Jonathan

+ 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. Mirror Cells for edits to be replicated both ways
    By hemal89 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-30-2020, 08:44 AM
  2. Need Columns to Feed to Replicated Questionnares
    By ablits1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-07-2014, 11:46 AM
  3. formulas in sheet 2 not working
    By Alina Loredana in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-15-2014, 03:57 AM
  4. Replies: 1
    Last Post: 05-21-2012, 03:00 AM
  5. formulas not working or is the sheet not functioning?
    By grimlock in forum Excel General
    Replies: 8
    Last Post: 05-18-2011, 06:51 PM
  6. Vlookup to find missing or replicated data`
    By excelhlp in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-13-2008, 05:24 PM
  7. Unwanted replicated split screen
    By BoyLeroy in forum Excel General
    Replies: 1
    Last Post: 09-28-2005, 03:05 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