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

1. ## 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.  Register To Reply

2. ## 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  Register To Reply

3. ## 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.  Register To Reply

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

The formula isn't there ... Where should it be?  Register To Reply

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

Formula:  `Please Login or Register  to view this content.`  Register To Reply

6. ## 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.  Register To Reply

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

Formula:  `Please Login or Register  to view this content.`
and in col N
Formula:  `Please Login or Register  to view this content.`  Register To Reply

8. ## 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?  Register To Reply

9. ## 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.  Register To Reply

10. ## 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.  Register To Reply

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

this .....

\$DE\$10 & \$DE\$11  Register To Reply

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

You can lock them like \$DE\$10  Register To Reply

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

Thank you, that worked   Register To Reply

14. ## 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  Register To Reply

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