0

Budget to Actual Table

Budget to Actual Table

2019 Actuals

2019 Actuals

In the Budget to Actual table there is an amount in cell L9. I want to populate the data from a separate table (2019 Actuals) using the column headers 1,2,3,4 etc representing each month of the year simply by entering a number representing the month in cell N5.

In other words, I want excel to pull the data out of the 2019 Actual table based on a given month using the column header and the amount must be specific to the account number in each row.

For example, it is February and I enter 2 into cell N5 and the number 50 (from the 2019 actuals table) will populate in cell L9.

I tried using the Hlookup formula, but you cannot copy the formula down.

Also, how could I sum cumulative data in the same way. For example, I want to sum the data in the same way year-to-date. If it is March, then I need the sum of 3 months worth of data in cell S9 after inputting the number 3 into cell N5.

What can I do without having multiple formulas in each cell. Ideally, I would like one formula in each cell of the table.

  • (1) Thank you for posting what appears to be a reasonable sample data set.  I guess you’re saying that you want a formula in cell L9 in the “Budget to Actual Table” sheet that displays a value from the “2019 Actuals” sheet, as indexed by cell N5 (in the “Budget to Actual Table” sheet).  Can you please make this a little bit clearer?  (2) You say you tried using HLOOKUP.  Please show us and describe what you tried.  (3) You say “you cannot copy the formula down.”  As far as I know, you want a formula in one cell (L9).  What and where do you want to copy down?  … (Cont’d) – Scott Feb 21 '19 at 2:08
  • (Cont’d) …  (4) You say “I want to sum the data in the same way year-to-date.”  Please don’t expect us to reverse-engineer a page full of data to figure out what you want; describe what you want.  (5) You say “If it is March, then I need the sum of three months’ worth of data in cell S9 after inputting the number 3 into cell N5.”  So, do you always want the sum of three months’ worth of data, or (if it is March) do you want the sum of January, February and March (i.e., the year-to-date sum)?  … (Cont’d) – Scott Feb 21 '19 at 2:09
  • (Cont’d) …  (6) Why do you show “34,199” in cell S9?  Is that based on the rest of your data, or is it a random, made-up number? … … … … … … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete. – Scott Feb 21 '19 at 2:09
0

HLookup can do this.

=Hlookup($N$5,Sheet2!$AB$1:$AM$20,Row(A6),false)

With the $ signs you anchor the cell references so they don't change when the formula is copied down. the Row(A6) will return the number 6, because you want the 6th row of the matching column returned. Copy the formula down and the reference will change to A7, so you can get the data from the next row without having to change the forumula.

For the YTD you can use an Index in a Sum function. You know the starting cell in the Actuals table, so you can hard code that and combine it with Index using the number in N5 as the column index.

=Sum(Sheet2!$AB6:index(Sheet2!$AB6:$AM6,$N$5))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.