+ Reply to Thread
Results 1 to 8 of 8

How to get excel to autopopulate formula horizontally.

  1. #1
    Registered User
    Join Date
    02-29-2016
    Location
    Upstate, New York
    MS-Off Ver
    office 2016
    Posts
    8

    How to get excel to autopopulate formula horizontally.

    Hello all,

    Here is my problem:

    I would like excel to pull data from one spreadsheet to another spreadsheet in the same work book. I am using the VLOOKUP formula to do this. I would like to pull the formula across the cells horizontally and automatically pull this data for each new year in the new excel sheet. However, I cannot currently do this and it is taking me an exorbitant amount of time to complete this work.

    As can be seen in the picture below, I want to take data from DA DATA and bring it into my new spreadsheet "Development Assistance" by pulling vlookup accross my spreadsheet. Please help.
    Attached Images Attached Images

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: How to get excel to autopopulate formula horizontally.

    I can't answer this question without seeing "DA Data" and more of your VLOOKUP formulas. But don't attach any more screenshots. An image attachment has very little value. Just attach the Excel file. It's easier than taking a screenshot first and then attaching that.

    To attach a file:
    1. Under the text box where you type your reply click the Go Advanced button.
    2. In the next screen look above the reply box and click the "paper clip" icon. You will get a pop-up screen.
    3. In the pop-up, click Add then Select to select a file from your local drive
    4. Click the Upload button to upload the file
    5. Click Done to attach it.

    It will be displayed as an attachment underneath your post text. Alternatively as the last step you can click the Insert Inline button and a hyperlink to the file will be inserted directly into your post text in the spot where your cursor is set at the moment you click the button.

    Here are step-by-step instructions with screen shots
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    02-29-2016
    Location
    Upstate, New York
    MS-Off Ver
    office 2016
    Posts
    8

    Re: How to get excel to autopopulate formula horizontally.

    Okay. I attached the excel file. Thank you for your help.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,426

    Re: How to get excel to autopopulate formula horizontally.

    Try this formula in I3:
    Please Login or Register  to view this content.
    Copy down and across.
    Let me know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    02-29-2016
    Location
    Upstate, New York
    MS-Off Ver
    office 2016
    Posts
    8

    Re: How to get excel to autopopulate formula horizontally.

    That definitely works!!! I do have a question on the formula however because I will be reusing it in other spreadsheets.

    What does the $H3 represent? I understand the rest of the values but I don't see how that is "look_up value" ??

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,426

    Re: How to get excel to autopopulate formula horizontally.

    $H3 is making sure that all of the estimates are for the country, Argentina in this case. The $ in front the letter keeps the focus on the same column, and the $ in front of the number keeps the focus on the same row. If there is a $ in front of both then the focus will be locked on that one cell. If there is no $ then the focus of the cell changes as the formula is copied across (columns change) or down (rows change). In this case we want to keep the focus on the country names in column H but we want to direct the focus of the formula to different countries as we copy down the rows.
    Let me know if you have any questions.

  7. #7
    Registered User
    Join Date
    02-29-2016
    Location
    Upstate, New York
    MS-Off Ver
    office 2016
    Posts
    8

    Re: How to get excel to autopopulate formula horizontally.

    I do have one more question for you: What does the "COLUMN(J1)" represent? For example, that portion of the formula is supposed to be the column index which I would have indexed by counting the number of columns in the previous excel sheet; but COLUMN(J1) simply refers to column J1 of the current excel sheet. Can you please explain what this means and how it acts to pull the data from the other sheet?

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,426

    Re: How to get excel to autopopulate formula horizontally.

    COLUMN(J1) is being used to give a value to the argument Column_Index_Number of the VLOOKUP function. If you run the Evaluate Formula, from the formula tab, while cell Sheet2!I3 is selected, you'll see that it evaluates to 10. As the formula is dragged across to column W the argument in the COLUMN function changes so that it becomes COLUMN(X1) meaning that it will feed the VLOOKUP function a value of 24. So that in cell I3 VLOOKUP displays the value from the 10th column (corresponding to the year 1999) and in cell W3 it displays the value from the 24th column (corresponding to the year 2013). I hope that makes sense.
    Let us know if you have any questions.

+ 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] Autopopulate macro, needs to searrch for appropriate section to autopopulate
    By Butcher1 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-09-2015, 09:11 AM
  2. autopopulate part of VLOOKUP formula with data in another cell
    By kudell in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-22-2013, 11:40 AM
  3. autopopulate formula from two columns of data
    By thedunna in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-11-2013, 09:57 AM
  4. formula to autopopulate a cell with specifc data from another
    By boltoncalling in forum Excel General
    Replies: 5
    Last Post: 01-11-2012, 12:55 PM
  5. Excel Autopopulate Help needed
    By kymmie in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-30-2011, 03:32 AM
  6. Autopopulate date and add color formula
    By jsumislaski in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-03-2010, 12:54 PM
  7. Formula with autopopulate.
    By sput_sput in forum Excel General
    Replies: 5
    Last Post: 07-24-2009, 02:28 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