+ Reply to Thread
Results 1 to 14 of 14

Need for this formula to act dynamic when the number of columns are increased

  1. 02-26-2022, 04:09 PM #1
    Tirrazo
    Tirrazo is offline
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Red face Need for this formula to act dynamic when the number of columns are increased

    Hi,


    Se attached workbook.

    I got this formula in cell G:305
    =HVISFEIL(SORTER(FILTRER(FILTRER(E18:M269;(H18:H269>=DE10)*(H18:H269<=DE11));(KOLONNE(E:M)=5)+(KOLONNE(E:M)=7)+(KOLONNE(E:M)=8)+(KOLONNE(E:M)=10));4);"Ingen utbytte")

    The problem is that if I increase the number of columns, this stops working.
    It's based on the number of columns from A and continues. I need the numbers that all represents a cell here to be dynamic, and not static.
    (KOLONNE(E:M)=5)+(KOLONNE(E:M)=7)+(KOLONNE(E:M)=8)+(KOLONNE(E:M)=10));4);"Ingen utbytte")
    It reads from colmns 5, 7, 8 and 10. If I increase a columns to add something that has nothing to do with this, the formula stops working.

    I can translate this if needed.
    Attached Files Attached Files
    Last edited by Tirrazo; 03-17-2022 at 06:52 AM.
    Reply With QuoteRegister To Reply
  2. 02-27-2022, 09:42 AM #2
    davesexcel
    davesexcel is offline
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,475

    Re: Need for this formula to act dynamic when the number of columns are increased

    It would help if you had some data in the workbook
    ,
    Xlorate.com, Selection Codes, YouYube Channel
    Reply With QuoteRegister To Reply
  3. 02-27-2022, 11:55 AM #3
    Tirrazo
    Tirrazo is offline
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Need for this formula to act dynamic when the number of columns are increased

    I have added some data now.

    If you just try to add a column right after column A, you will see the formula doesn't show anything.
    Reply With QuoteRegister To Reply
  4. 02-27-2022, 12:13 PM #4
    AliGW
    AliGW is offline
    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
    80,460

    Re: Need for this formula to act dynamic when the number of columns are increased

    The formula isn't there ... Where should it be?
    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.
    Reply With QuoteRegister To Reply
  5. 02-27-2022, 12:18 PM #5
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,078

    Re: Need for this formula to act dynamic when the number of columns are increased

    How about
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 02-27-2022, 02:10 PM #6
    Tirrazo
    Tirrazo is offline
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Need for this formula to act dynamic when the number of columns are increased

    Hi,

    The formula starts in column G305

    The formula you made @Fluff13 is working, but I can see there is another problem.
    I use this formula 12 times with 7 columns of break. It gives me back data for each month.
    The formula for the last month is looking like this:
    =HVISFEIL(SORTER(FILTRER(FILTRER(E18:CL269;(CG18:CG269>=DE10)*(CG18:CG269<=DE11));(KOLONNE(E:CL)=4)+(KOLONNE(E:CL)=83)+(KOLONNE(E:CL)=84)+(KOLONNE(E:CL)=86));4);"Ingen utbytte")

    How can I use the formula you made in every 7th column to the right?

    I have changed the workbook so you can see each month as an example.
    Reply With QuoteRegister To Reply
  7. 02-27-2022, 02:43 PM #7
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,078

    Re: Need for this formula to act dynamic when the number of columns are increased

    Ok how about
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    and in col N
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 02-27-2022, 05:28 PM #8
    Tirrazo
    Tirrazo is offline
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Need for this formula to act dynamic when the number of columns are increased

    This seems to work! Thank you!

    And I can just change the last numbers as before, but the formula still works if the number of columns increase/decrease?

    So this is another function you are using in this formula?
    Reply With QuoteRegister To Reply
  9. 02-27-2022, 05:40 PM #9
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,078

    Re: Need for this formula to act dynamic when the number of columns are increased

    You're welcome & thanks for the feedback.
    Yes the the formula will work if you add new columns.
    Reply With QuoteRegister To Reply
  10. 03-03-2022, 01:02 PM #10
    Tirrazo
    Tirrazo is offline
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Need for this formula to act dynamic when the number of columns are increased

    How can I get DE10 & DE11 to stay constant?
    (H18:H269>=DE10)*(H18:H269<=DE11)
    When I paste the formula in different cells, these moves and becomes DL11 & DL12 and so on.
    Reply With QuoteRegister To Reply
  11. 03-03-2022, 01:05 PM #11
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,152

    Re: Need for this formula to act dynamic when the number of columns are increased

    this .....


    $DE$10 & $DE$11
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  12. 03-03-2022, 01:05 PM #12
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,078

    Re: Need for this formula to act dynamic when the number of columns are increased

    You can lock them like $DE$10
    Reply With QuoteRegister To Reply
  13. 03-03-2022, 01:12 PM #13
    Tirrazo
    Tirrazo is offline
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Need for this formula to act dynamic when the number of columns are increased

    Thank you, that worked
    Reply With QuoteRegister To Reply
  14. 03-06-2022, 02:42 PM #14
    Tirrazo
    Tirrazo is offline
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Need for this formula to act dynamic when the number of columns are increased

    Before I put this post as "solved".

    Does anyone know why some of the sheets in Excel with a lot of data are "lagging"?
    When you you use the arrow keys to navigate between cells, the marker is not keeping up as it does in a new sheet.
    If I press a key to long, the cell marker won't stop before it's far down the spreadsheet :o
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula to copy formula in another cell but with a different reference in the formula
    By sHAMBLES88 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-24-2020, 07:27 AM
  2. IF formula not working because cell has a formula in it (IF and LEFT FORMULA)
    By Beh162 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-10-2019, 09:34 AM
  3. find formula for (formula)+(*1.5 if colum A has certain word) if not use original formula
    By amac4556 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-16-2018, 06:50 AM
  4. [SOLVED] Formula SUM(A+B) - Copy formula to SUM(D+E), SKIP C. Automatic formula
    By hens91 in forum Excel General
    Replies: 8
    Last Post: 09-22-2017, 05:41 AM
  5. Excel formula bar to display the result of the formula , not the formula?
    By max_max in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2016, 07:15 PM
  6. Copy Value String text of Formula become Formula but the formula not automatically active
    By Yovan Wisnu in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-06-2014, 03:34 PM
  7. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1