+ Reply to Thread
Results 1 to 27 of 27

Automatically Selection of Range to all cells with formula

  1. #1
    Registered User
    Join Date
    12-28-2020
    Location
    India
    MS-Off Ver
    2019
    Posts
    19

    Automatically Selection of Range to all cells with formula

    I have an excel that need to Provide the range of data everyday. And I have 20 cells with different averaging column but the row is same. So, I need to manually change the range to every cells for 20 cells everyday.
    Example: average(D5:D10), Average(I5:I10), Average(H5:H10).... and so on
    and on the next day I need..
    Average(D6:D11), Average(I6:I11), Average(H6:H11).... and so on.

    So For this I need to Manually Type the Numbers everyday to all 20 cells.
    Please Provide me a Formula that I will Type the Numbers like 5:10 or 6:11 to a cell and all the 20 cell copy the Numbers from the Given Cells as the Row remain same.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,412

    Re: Automatically Selection of Range to all cells with formula

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    12-28-2020
    Location
    India
    MS-Off Ver
    2019
    Posts
    19

    Re: Automatically Selection of Range to all cells with formula

    Please Some one Help Me

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,412

    Re: Automatically Selection of Range to all cells with formula

    Please provide a sample workbook, as requested.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Automatically Selection of Range to all cells with formula

    Please do not be so rude by bumping your post after a few minutes. Please read the yellow banner (top of screen) and attach a sample sheet, as requested, instead of being so demanding.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  6. #6
    Registered User
    Join Date
    12-28-2020
    Location
    India
    MS-Off Ver
    2019
    Posts
    19

    Re: Automatically Selection of Range to all cells with formula

    Ok I m So sorry, I m very new So I dont Know those things

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,412

    Re: Automatically Selection of Range to all cells with formula

    Nor will you if you just ignore requests - read and learn.

  8. #8
    Registered User
    Join Date
    12-28-2020
    Location
    India
    MS-Off Ver
    2019
    Posts
    19

    Re: Automatically Selection of Range to all cells with formula

    I have the Excel Attached Hope You got it
    Attached Files Attached Files

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,412

    Re: Automatically Selection of Range to all cells with formula

    OK - where do you want the results? Which cells contain your formulae?

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Automatically Selection of Range to all cells with formula

    You didn't tell us where to look. I chose AC15 and AD15 (shaded ornage)

    =AVERAGE(INDEX(BH:BH,$AC$11):INDEX(BH:BH,$AD$11))
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    12-28-2020
    Location
    India
    MS-Off Ver
    2019
    Posts
    19

    Re: Automatically Selection of Range to all cells with formula

    On AC15, AD15, AF15, AG15, AH15, AI15

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Automatically Selection of Range to all cells with formula

    OK. So I have done it on the first two. Is it working as intended? If so, you can do the other four.

  13. #13
    Registered User
    Join Date
    12-28-2020
    Location
    India
    MS-Off Ver
    2019
    Posts
    19

    Re: Automatically Selection of Range to all cells with formula

    Thank You for your Support Glenn Kennedy, But What I want is like I type the Number 18:16 or 10;18 to a cell and all the cells AC15, AD15, AF15, AG15, AH15, AI15 copy the numbers from that cells and do the average function. is it Possible? I dont Know it may sound Stupid.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,412

    Re: Automatically Selection of Range to all cells with formula

    That's EXACTLY what Glenn's formula does. AC11 and AD11 contain the numbers.

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Automatically Selection of Range to all cells with formula

    Look at the formula in those two cells. They refer to AC11 and AD11. Change those two cells to the whatever your desired row range is. As I said, I have done it for you.

  16. #16
    Registered User
    Join Date
    12-28-2020
    Location
    India
    MS-Off Ver
    2019
    Posts
    19

    Re: Automatically Selection of Range to all cells with formula

    Ok Please Give me Some time Let me Check

  17. #17
    Registered User
    Join Date
    12-28-2020
    Location
    India
    MS-Off Ver
    2019
    Posts
    19

    Re: Automatically Selection of Range to all cells with formula

    Hi Glenn, I am sorry may be my Mistake I did the Same thing You told me but the results are different, I Type 18.26 in AC11 and I put Your Formula in AC9. it give me 507.77 but it should be 700.72. any Suggestion

  18. #18
    Registered User
    Join Date
    12-28-2020
    Location
    India
    MS-Off Ver
    2019
    Posts
    19

    Re: Automatically Selection of Range to all cells with formula

    this is the screen shot of what I did

  19. #19
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Automatically Selection of Range to all cells with formula

    No!! Putting the start and end numbers in one cell makes it more complicated. Ac11= START. AD11 = END.

    READ the formulae!!

  20. #20
    Registered User
    Join Date
    12-28-2020
    Location
    India
    MS-Off Ver
    2019
    Posts
    19

    Re: Automatically Selection of Range to all cells with formula

    sa ve.PNG

    this one First one not working

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,412

    Re: Automatically Selection of Range to all cells with formula

    Your formula is averaging one cell only.

    You need this:

    =AVERAGE(INDEX(BH:BH,$AC$11):INDEX(BH:BH,$AD$11))

  22. #22
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Automatically Selection of Range to all cells with formula

    Here is the sheet again.
    Attached Files Attached Files

  23. #23
    Registered User
    Join Date
    12-28-2020
    Location
    India
    MS-Off Ver
    2019
    Posts
    19

    Re: Automatically Selection of Range to all cells with formula

    Oh No. Finally I got it Glenn Thank You so Much, at First I thought =AVERAGE(INDEX(BH:BH,$AC$11) one formula and INDEX(BH:BH,$AD$11) other Formula. But when I Combine it Works Thank You man You make my Day Happy... May God Bless You to Fulfill Your Dreams and make Prosperous in 2021. Thank You So much

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,412

    Re: Automatically Selection of Range to all cells with formula

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  25. #25
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Automatically Selection of Range to all cells with formula

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It would also be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

  26. #26
    Registered User
    Join Date
    12-28-2020
    Location
    India
    MS-Off Ver
    2019
    Posts
    19

    Re: Automatically Selection of Range to all cells with formula

    I add Reputation and Selected Solved too. is it ok now?

  27. #27
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,412

    Re: Automatically Selection of Range to all cells with formula

    Thank you.

+ 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] Listbox selection bug (selection is automatically made when it shouldn't be)
    By seleseped in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-10-2017, 01:06 PM
  2. [SOLVED] Automatically selection x+4th cell down from current row as next value in formula
    By jonbasha1 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 08-05-2016, 11:55 AM
  3. [SOLVED] I need help range selection and cells selection
    By venkatpvc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-23-2013, 07:36 PM
  4. Replies: 1
    Last Post: 04-12-2013, 06:27 PM
  5. [SOLVED] copy range of cells automatically to different range evry hr
    By Sarangsood in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-24-2011, 03:46 AM
  6. Using a cells value as letter in range selection
    By elduderino65 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-16-2010, 06:54 PM
  7. How to automatically clear cells upon changing/making selection?
    By Ingeniero1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-17-2006, 04:50 PM

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