• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Use H Lookup Feature to Add Months [SOLVED]

N

Nikki91775

Member
I have Sales Bowlers that I do at the end of every month. I have it set up with months across the top - Jan, Feb, March. Then on the sides I have Stretch, Plan and Actual. Every month I change the formula in Stretch, Plan and Actual to include the next month. For example for Jan - April - it is Column J - M. Now for May coming up, I will need to change it to N.


I am trying to figure out how to use a H lookup to include the month so it always knows to update that sheet to add the next month without me having to change it.
 
are you able to build the sheet/formula 'for the future'? meaning, you know which months are in a year and the order... so could you list the months and write the formula to look at the specific range to calculate the total? any upcoming months wont affect the total as they will be empty.


??
 
Hi, Nikki91775!


Tried using dynamic named ranges? So you'd use formulas like:

=SUM(rngDynamic)


Example of dynamic range definition for row 2, from column 2 to last column in row 1:

=OFFSET($B$2,,,1,COUNTIF($1:$1)-1)


Just advise if any issue, and better indeed if you upload a sample file.


Regards!
 
The months are already populated at the top and the data pulls from a pivot table to populate the fields for that month.


For example this is what it looks like

Jan Feb March April May

8 Plan 1 2 2 3 5

15 Stretch 2 3 4 6 6

17 Actual 5 7 4 1


So the plan and stretch numbers are populated for the entire year and put in the sheet at the beginning so I could not do a =SUM of the entire area, I could on actuals because those are what pulls from the Pivot and add as they go. However, for Plan and Stretch I only want to look as what the current YTD is to see if we are on target or not. I have about 12 of these sheets and they each have about 300 lines, takes me almost a day to just update to include the new range.
 
Sorry, that posted weird and did not state what I wanted.


For example.


Plan total is 8 and the numbers correspond from January through April, but May's numbers are in there too, my range where the "8" is does not look for that until next month.
 
I created a sample of what I am using. This is just one line of many that I have on 12 different sheets.


https://hotfile.com/dl/225092179/afd093f/Bowler_Example.xlsx.html
 
Hi, Nikki91775!


Try this:


a) C4

Value: date of any day of current month (xx/mm/yyyy or in mdy format mm/xx/yyyy), set to last, it's always useful

Format: yyyy


b) column B, B5 and down:

=SUMA(DESREF(D5;;;;MES(C$4))) -----> in english: =SUM(OFFSET(D5,,,,MONTH(C$4)))


Regards!
 
Hi Nikki ,


If your actual figures will always be greater than 0 , then you can define a named range called Number_of_Months , and in the Refers To box , put in the following formula :


=SUM(IF(Sheet1!$D$7:$O$7>0,1,0))


For the YTD figures , you can use the following formula , starting from B5 :


=SUM(OFFSET($D5,,,,Number_of_Months))


and copy it down.


Narayan
 
Ok.. sorry to be a pain, but I am trying to put it in my big worksheet and it is not working. This is how my workesheet works - I have Jan - December at the top in J6 to U6. Then I have about 30 boxes like in my example all the way down until line U 414. I am not sure what is going on, but when I put in the formula under the name manager, it keeps opening up my windows screen like it is looking for a file.
 
Nevermind - I have it solved. I realized I cannot do the range of the whole sheet when using the name manager to make it work, I just have to do the range at the top and then copy the cells down within the form.


Thanks for everyone's help.
 
Hi, Nikki91775!

Have you tried my earlier suggestion?

http://chandoo.org/forums/topic/use-h-lookup-feature-to-add-months#post-105873

Did it work with your little worksheet? If yes, which's the issue with the big one?

Regards!
 
Yes, I got it to work and when I went to the big worksheet, when I named the months to Number of Months, I selected everything the sheet when you just select the top portion. Then I put the correct fomula in all the cells and it worked. I have updated 3 of my spreadsheets so far with the new formula.


Thanks for all of your help.
 
Hi, Nikki91775!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top