referencing a range of cells within a formula of a macro relative to a different cell

Welcome to the forum,

Originally Posted by Jgray805

...
1. So I'd like to find a way where i can create just a few macros instead of 365
2. what id rather do is some how call those ranges based on some offset of the cell that the macro button is at (the box of the day that i want to calculate the count per hour of the day).
3. i could use the same macro for the entire month rather than having to have a separate macro for each day
4. id need a total of 12 macros this way since each month i want the results to display in the same area as the month
5. id want the macro to show the results on lines AE112:AF112 etc
6. so i don't have to copy paste and edit 365 macros, and create all those range names 730 :O
...

hello Shakespeare ...

So ... what exactly do you want to achieve ?

If you have many buttons to which you want to link a macro/macros, for example you can do it this way (for Microsoft Excel controls):

Please Login or Register  to view this content.

or alternatively ... if you want to operate multiple buttons with one macro, for example you can do it this way:

Please Login or Register  to view this content.

See example in the attachment:

1. Initial state - buttons without macro/macros assigned
2. You run the macro "A1_assign_macro_ABC_to_buttons"
3. Then you press the buttons

It seems to me that, you make your life difficult, with "these named ranges" (defined names). "This" can be done without these - below is an example.
Press "Make buttons", then press any "D day" button - selection is only for range visualization.
Using this scheme, you can extract specific ranges of cells to/for formulas.

OH WOW about how to create all those buttons at one time.. That will definitely save me hours!! THANKS! I am going to go try to code this to my specific sheet using your example, thanks!

I need those buttons to do something different though. I need them to auto populate the # of employees working per every half hour of the day. (not just add up the hours worked per shift per employee) I want this count per half to populate on the column just to the right of the calendar.
(see my attachment for what i am talking about visually)

I think I'm having trouble figuring it out because the buttons will be in different locations each time, and where i want the information to be displayed will be in an absolute location. I am going to reread and reread ur code to see if maybe the answer is in there and i am just not getting it yet.

NOTE: the way January functions at the surface level in my spreadsheet is perfect!! It's exactly the way i want it, but i just know recreating all the other months the way i have it set up will take hours and hours...
(not as many now that i know the creating button trick.. but still as of now because i feel like i need a different macro per button? I will keep trying to figure it out, but if you have any ideas or extra help i would appreciate more insight. Thank you!

The number of macros can be kept to a minimum, they don't have to be dozens.
Below is an example of how you can handle most of your needs with one macro, just an example, because it has not been tested for all cases and it certainly won't work properly from 'A' to 'Z'. However, it shows the direction in which you can go.

holy cow!! that's it!! I've been tweaking it just slightly all night and really analyzing all the code you wrote out and it all actually now starting to make sense.

Thank you so much for all your help!! It's amazing how you can actually refer to all those cells without actually having to name them. :D

After i got everything the way i wanted just a little bit ago, i was able to duplicate February's page in just about 10 minutes. and it worked perfect!!

I'm going to change the rest of the months tomorrow, and i will let you know how it turns out. i'm sure those will go even faster now that i know what all the syntax means.

Thank you again so much!! I'm definitely going to sleep well tonight!

I just wanted to follow up and let you know that i have finished the whole year now. You saved me so much time. Thank you again for helping me figure this out!

I learned a lot too from you. Knowing what you taught me will be so useful for future things I create too! I added some conditional formatting to the cells that shower the hour count which is going to save me a lot of $$ in payroll by not over scheduling and stressing when i accidentally under schedule in the future.

Now i am going to move to my next step of the document and start adding employee availability so I don't accidentally schedule people on their requested time off.

Take Care,

Jason

Ok, I'm glad