+ Reply to Thread
Results 1 to 6 of 6

Use data in one array in formulas for another

  1. #1
    Registered User
    Join Date
    08-02-2020
    Location
    Houston, Texas
    MS-Off Ver
    Office 365
    Posts
    3

    Use data in one array in formulas for another

    Hi -

    I'm trying to use data in one sheet (i.e. assumptions - data is horizontal by year), and use it in another sheet (i.e. cash flow - data is vertical by year), without manually changing the row index. Can anyone help me with this?
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,129

    Re: Use data in one array in formulas for another

    Welcome to the forum.

    Where are you tying to use it? INDEX MATCH should be a option.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    08-02-2020
    Location
    Houston, Texas
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Use data in one array in formulas for another

    Thanks! I've highlighted and commented on the sample file. I'm having issues describing it succinctly

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,129

    Re: Use data in one array in formulas for another

    Where???

    Sorry - I cannot work out from the attachment what is supposed to be referenced or where, nor do I see any annotations. Give me some idea of the cells in each sheet that you are trying to cross-reference.

  5. #5
    Registered User
    Join Date
    08-02-2020
    Location
    Houston, Texas
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Use data in one array in formulas for another

    Assumptions O16 to Project Cash Flow E15
    Assumptions O17 to Project Cash Flow F15
    Assumptions Q16 to Project Cash Flow E16

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,129

    Re: Use data in one array in formulas for another

    You would need to make the headings on the Assumptions sheet match the row labels on the Cash Flow sheet, and then, as I said, you can use some sort of INDEX MATCH.

    I'm not really clear how the dates pair up, though - what's across the top of the cash flow are rangers, on assumptions it's individual years.

+ 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. [SOLVED] Copy Formulas from One Worksheet to Another for an Array of Data
    By Science-Guy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-21-2013, 07:17 PM
  2. Using Array formulas to pull data from one tab to another
    By mobie86 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-25-2013, 11:19 AM
  3. Linked Table Data and Array Formulas
    By Ludeth in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-27-2013, 09:11 PM
  4. [SOLVED] Adding Record to data table + preserve array formulas in specific columns
    By olivierpbeland in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-21-2013, 02:33 PM
  5. Ctrl+Shift+Enter (CSE) array formulas in VBA using differing formulas
    By officeguy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-11-2012, 04:31 PM
  6. Reorganise data into tables with array formulas
    By ChrisNor in forum Excel General
    Replies: 3
    Last Post: 12-06-2010, 07:09 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