Formula to paste data in subsequent cells within the same column

[Tim_Excel_]

Hi forum!

I have been wrecking my brain over this, I hope you can help me out.

I have two sheets. In sheet 1, the user can mark at which interval a repair needs to be for certain machine codes. In sheet 2, 1 column shows the machine codes together with the interval codes (see example pictures). This column in the second sheet needs to be automised, in that a formula should automatically paste the codes+intervals when a user marks the intervals in sheet 1. However, were I to write something along the lines of

(IF(sheet1!$A2="1",CONCATENATE(sheet1!$D2,"_","03M",IF(sheet1!$B2="1",CONCATENATE(sheet1!$D2,"_","06M",""))

then only one of the two monthly repairs would appear in sheet 2, even if two are marked.
I'd like both to appear in the same column, maybe you know how to? I am stuck.

Tim

 

[Tim_Excel_]

Where are the expected results?

The expected results are in the "Code" ("B") column in sheet AssetTypeTask.

Knowing the headings as well as what is, or may be, in those columns could well be relevant to finding if a solution is possible.

I've left out data as I cannot share the full file (company rules). Blank columns do indeed contain data, but this data is not of use for this problem. All the data and columns necessary for the problem can be found in the mockup workbook. Cells in column "O" are either filled or not, but this is a dynamic process by the user, hence why I need formulas and not a VBA solution.

Any sample data and/or the explanation that comes with it would also need to clarify what you mean by "look at the next cell", as that can be interpreted several ways.

The next cell would be the cell directly underneath. In short, I want the "Code" column to be filled without blanks. So, if in sheet "Componenten" in column "O" the cell is blank, the cell in Column "Code" won't be blank as well but will be filled with the correct string for the next filled cell in column "O".

 

[Peter_SSs]

The expected results are in the "Code" ("B") column in sheet AssetTypeTask.

In the file I am looking at that column is blank except for the heading in row 1.

 

[Tim_Excel_]

In the file I am looking at that column is blank except for the heading in row 1.

That's correct, I have not entered the formula you provided me.

In the following link you'll find the complete workbook I'm working in.
https://we.tl/rWIygXSgIc

 

[Peter_SSs]

That's correct, I have not entered the formula you provided me.

I did not want the formula I provided as I already have that.
What I asked for was the expected results.

In the following link you'll find the complete workbook I'm working in.

I'm afraid I will not be able to help further, other than one suggestion below, as there continues to be changes at every post.
- Now there seems to be only one column with "1"s
- The results in column B of AssetTypeTask seem to have no direct relationship to column B in Componenten where the "1" values occur as there was in the original question and all through the thread.

If column B of AssetTypeTask contains the results you want other than that there are blank rows in the list, then you could try this in another column. I inserted a new column C, entered this formula in cell C2 and copied down.

=IFERROR(INDEX(B$2:B$1539,AGGREGATE(15,6,(ROW(B$2:B$1539)-ROW(B$2)+1)/(B$2:B$1539<>""),ROWS(C$2:C2))),"")

 

[Tim_Excel_]

Peter,

the reason there are "changes at every post" is that I tried to keep this problem the least cluttered with useless information (e.g. the large number of columns in my workbook) as to make the problem easier to oversee and solve. Since you've requested to see my file, I've provided you said file which naturally looks different compared to my simplistic example in my first post.

I'll from now on refer to my mockup file, if anyone else wants to give it a shot. The expected results in Column B from sheet AssetTypeTask:

[TABLE="width: 500"]
<tbody>[TR]
[TD]0400.1_
[/TD]
[/TR]
[TR]
[TD]0.5500.01_01.02
[/TD]
[/TR]
[TR]
[TD]0700.00.F42<strike></strike>_01.01
[/TD]
[/TR]
</tbody>[/TABLE]