Hello everyone!
on the screenshot I posted you can see two different rows of digits. The second row is connected to the 1 row but not 1:1. The first to cells of the second row get the digit from the first cell of the first row. Cell 3 and 4 of the second row get the second cell of the first row and so on...
My question is now, how can I modify the formula to drag the cells for Auto fill.
Thank you and have a nice day
Screenshot 2023-01-12 084445.png
Try this,
K7
copied right
Please Login or Register to view this content.
Hey,
Thank you very much. Its working so far!
But I have an additional question:
How would the formula look like if the upper data row would start at column "M" for example?
Thank you very much, you already helped a lot!
Best wishes
Use M instead of K
M7
copied rightPlease Login or Register to view this content.
In K6 then copied across. Data can start from any cell, formula remains same. It gives correct result.
=IFERROR(INDEX($K$5:$Y$5,AGGREGATE(15,6,COLUMN($K$5:$Y$5)/($K$5:$Y$5<>""),1+INT((COLUMNS($K$6:K$6)-1)/2))-COLUMN($J$5)),"")
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Hi,
as an alternative solution,
with formula Excel 365 (with the use of EXPAND and TOROW functions)
=TOROW(IFNA(EXPAND(K5:Z5,2),K5:Z5),,1)
where, 2 is Nb cells
Hey again,
thank you very much for your solutions, it already helped alot!
But I have third and last question: What do I have to do, if i want to pull 2 Cells which each go +1 after every second cell?
Have a nice day!
Greetings Marco
Please show an example!
Imagine K an L from the second row highlighted have two different formulas in it, referring to the same cell. And I want to drag both drag and fill both cells at the same time, while counting each cells row only 1 up.
Do you get the Point?
Thanks!
The problem with words that are not even my native language is that you can quickly interpret them in different ways.
That's why I'd like to see an representative example, as you did in Post#1 with your input and expected results of the formula.
Attachment 813729
Here We go, I want to drag and fill in this scheme
Greetings
One guess
Input 5 - 12
Output 5 - 12 - 6 - 13 - 7 -14 - 8 -15 - 9 -16 ....
Did you mean this?
Our posts were crossing.
I can't douwnload your attachment (invalid attachment)
Please add your attachment via Go advanced ... manage attachments (see yellow banner)
Does it work?
One way:
Please try in D6 and copy to right:Formula:
Please Login or Register to view this content.
Last edited by HansDouwe; 01-17-2023 at 09:56 AM.
Works for me thanks!!!
Going try to implement it to my excat table.
Thank you
marco
Glad to hear that I was able to help you.
If you have any further questions, don't hesitate to ask them here.
Would you please consider adding reputation to the responses of all helpers that you feel have earned due to the help provided.
If you want to, click on * Add reputation at the bottom left of the answer.
So I am a little bit confused when it comes to bringing this to my formula. Imagine having this formula in one cell instead of SUM(...):
=SUMMEWENN(PZx_VSI!AU$6:AU$20000;"PZM"&$C9;PZx_VSI!$AJ$6:$AJ$20000)-SUMMEWENNS(PZx_VSI!$AJ$6:$AJ$20000;PZx_VSI!AU$6:AU$20000;"PZM"&$C9;PZx_VSI!$G$6:$G$20000;">"&HEUTE())
And this in the second instead of Average(...)
=SUMMEWENN(PZx_VSI!AU$6:AU$20000;"PZM"&$C9;PZx_VSI!$AJ$6:$AJ$20000)
The Green highlighted columns shoulb be counted +1 as described before.
Can you help me out here as well?
Please add a small sample workbook with the same structure. See yellow banner at the top of the page.
Did adding the attachment work?
Hope you will get the idea
thanks
The attachment is there... but it has links to values in another file which we do not have access to. Please edit it and REPLACE it. Make it CLEAR what your expected answers are.
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
Here we go
B5 copied down:
=SUMIF(INDEX($E$12:$H$20,,MATCH(B$3,$E$11:$H$11,0)),"PZM"&$A5,$D$12:$D$20)-SUMIFS($D$12:$D$20,INDEX($E$12:$H$20,,MATCH(B$3,$E$11:$H$11,0)),"PZM"&$A5,$C$12:$C$20,">"&TODAY())
C5 copied down:
=SUMIF(INDEX($E$12:$H$20,,MATCH(B$3,$E$11:$H$11,0)),"PZM"&$A5,$D$12:$D$20)
Select B5:C7. Drag across.
Thank you very much, I think I finally got it now!
Greetings to Ireland!
You're welcome (currently in Thailand!!).
It would 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.
Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
In your case I would refer to the headers and not column numbers.
I also find that easier to understand and easier to adapt when the rules change
Please try in B5 and copy across and down:In German:Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
As I already thought, it opened up another question for me because I dont really understand the logic of the functions you brought up to solve my problems.
I attached a file and a screenshot, in this case I want the empty grey cells to be drag and filled arcodingly like the scheme shown before It should be like:
Feb 23, Feb 23 (empty) Mar 23, Mar 23 (empty) Apr 23 Apr 23 (empty) May 23 May23 (empty) etc....
Thank you very much!
Screenshot 2023-01-19 115406.png
Who are you talking to?
If me... then as per attached file (the original... as an empty sheet doesn't help too much...)
Select the block of green cells and drag across.
Hello again,
the File attached shouldt be empty I checked it. I want to fill the missing dates. I think we misunderstood eachother.
Ahhh. See new formulae in F4 & G4. Select F4 to H4 and drag across.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks