+ Reply to Thread
Results 1 to 2 of 2

Help with multi level referencing

  1. #1
    Registered User
    Join Date
    05-27-2021
    Location
    MN
    MS-Off Ver
    2016
    Posts
    1

    Help with multi level referencing

    Hello everyone, this is my first post so hopefully I do a good enough job describing my issue. See my attached workbook to better understand my question. In this workbook I have the template and two examples of the results I would be looking to be able to have automatically calculated.

    I am wanting to have the "Final" column blue cells set to reference "Investment A" cells for each "Month" up until the month of change (D2) which I want to be able to adjust at any time to get different results. On the month that equals (D2) I want the "Final" column blue cells to start referencing "Investment B".

    So far this is simple, I run into problems with the fact that I always want the point where the reference changes to begin the "Investment B" reference from the first month. For example if I select the change to happen at month 5 I want month 5 to reference "Investment B" "Month" 1 and then the remaining final cells to reference the following Investment B rows.

    To take it one step further I would like the "Investment B" reference to actually be affected by another conditional reference shown in column "Investment B+A at time of switch". I want this to add "Investment B"s starting value with whatever the value of "Investment A" is one month before the switch. This will then impact the values for the cells I would like to reference after the switch.

    So in summary I want to be able to select any month for a switch to occur in referencing two different columns. I want the post switch reference to always begin at the first cell of the new reference rather than the corresponding cell in the column. I would also like the reference, post switch, to include the value of the reference one row before the switch into its calculation. I want to be able to change only the yellow cells to get different results and I need help with formulas/functions for all of the blue cells.

    I have really been struggling to describe what I am going for so hopefully there is some excel wizard on here that can translate all this for me. If not please ask questions.

    Thanks All!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,843

    Re: Help with multi level referencing

    For part I:

    Investment_A =Sheet1!$G$6:$G$15
    Investment_B =Sheet1!$H$6:$H$15
    SumAB =Sheet1!$I$5:$I$15
    SwitchMonth =Sheet1!$D$2


    C8:C15
    Please Login or Register  to view this content.
    (I don't quite get part II yet)
    Attached Files Attached Files
    Ben Van Johnson

+ 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. Macro needed to convert multi-level BOM to single-level BOM as per attached file
    By AhmedSaad in forum Excel Programming / VBA / Macros
    Replies: 35
    Last Post: 05-12-2021, 08:31 AM
  2. Format the second level of multi-level category axis - clustered chart
    By mushkitoes in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-04-2018, 09:30 AM
  3. [SOLVED] creating Multi-level list styles using existing multi-level list?
    By JimmyWilliams in forum Word Formatting & General
    Replies: 2
    Last Post: 08-28-2017, 12:21 AM
  4. [SOLVED] Multi level sort
    By Mr Ferret in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 06-15-2016, 11:43 AM
  5. Bill of Materials conversion from multi level to single level
    By susmitpatel in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-11-2013, 12:53 AM
  6. Multi level BOM
    By neorez in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-23-2010, 05:22 PM
  7. Using macro to convert single level BOM to Multi Level BOM
    By andrew_chong in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-07-2006, 04:57 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1