+ Reply to Thread
Results 1 to 14 of 14

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

  1. #1
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    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.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,165

    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

  3. #3
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    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.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,540

    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" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,053

    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

  6. #6
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    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.

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,053

    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

  8. #8
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    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?

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,053

    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.

  10. #10
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    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.

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,832

    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.

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,053

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

    You can lock them like $DE$10

  13. #13
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

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

    Thank you, that worked

  14. #14
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    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 to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 03-24-2020, 07:27 AM
  2. Replies: 3
    Last Post: 01-10-2019, 09:34 AM
  3. Replies: 5
    Last Post: 02-16-2018, 06:50 AM
  4. 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. 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1