+ Reply to Thread
Results 1 to 6 of 6

Synchronising data being referenced dependent on a non zero value

  1. #1
    Registered User
    Join Date
    08-25-2012
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    Excel 2008
    Posts
    5

    Synchronising data being referenced dependent on a non zero value

    I'm sure there is a simple elegant solution, but I cannot find it.

    On a variables sheet the date of new staff joining is set. Each new member of staff gets their own sheet and own start date. Thus on a many other sheets those data are fed through.

    In the example below the third row is on a completely different sheet. For each sheet we need to multiply the value in the second row, with the %age.

    Currently each sheet is adjusted manually by inspection. However the percentages are always the same it is just the start date that changes and then the %ages are reference in order.

    I can solve the problem inelegantly by searching for non 0 value and then getting the cell location and feeding it back in to the formula. However this takes two rows on the spread sheet to achieve the task. Ideally I want to find an elegant solution as there are several instances with similar issues and having a lot of cell referencing on the sheets rather highlights my incompetence.

    Jan Feb Mar Apr May Jun Jul Aug
    0 0 0 1 1 2 3 1
    2% 3% 4% 5% 6% 7% 8% 9%
    0.02 0.03 0.08 0.15 0.06

    The last row of figures in this case is the desired result, starting in April (i.e. the staff's start date)

    Thank you for you time in reading this regardless of if you are able to help.

    Cheers

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Synchronising data being referenced dependent on a non zero value

    I almost follow that. Any chance you can post a workbook demonstrating this layout, the reference data, a user sheet or two and the cells on those sheets you're trying to automate? Fill in the expected answered manually and point them out.

    Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    08-25-2012
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    Excel 2008
    Posts
    5

    Re: Synchronising data being referenced dependent on a non zero value

    Firstly, thank you very much for looking at my problem Jerry. Sorry for my lack of clarity, I am sure it is frustrating, sorry also as I did not think to load a sheet straight off.

    Cheers

    Rupert
    Attached Files Attached Files

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Synchronising data being referenced dependent on a non zero value

    1) Highlight B9:S9 on the Variables sheet, the type Percentages into the Name Box (just left of the formula bar where the active cell address usually appears. This creates a named range that is easier to read on other sheets.

    2) On the named sheets in cell B4, format the cell as percentage then enter this formula:

    =IF(B3="", "", INDEX(Percentages, COUNTA($B$3:B$3)))

    Copy that cell across, then copy to other sheets.

  5. #5
    Registered User
    Join Date
    08-25-2012
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    Excel 2008
    Posts
    5

    Re: Synchronising data being referenced dependent on a non zero value

    Thank you so much Jerry. I did not expect to get an answer today. I don't know why your solution did not occur to me as I have used all the components at different times, but not in this combination. It looks so easy when you know how. Thanks

    By the way your own website looks like it will be very useful.

    Cheers

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Synchronising data being referenced dependent on a non zero value

    If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1