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_]

I'm getting #VALUE error codes and once I paste the formula the windows file explorer opens ("Update Values: Sheet 1")

 

[Peter_SSs]

You need to check that the sheet name in my formula matches the sheet name you are pointing at. In your first post it was Sheet1 in post 9 it looks more like Variable Name 1
If that is the correct name you would have to do those replacements in the formula. Additionally, because thare are spaces in that sheet name the syntax each time needs single quotes like this

{=TRIM(MID(TEXTJOIN(REPT(" ",1000),TRUE,IF('Variable Name 1'!U$2:AG$4=1 ....

 

[Tim_Excel_]

The formula is referring to the right sheet, the #value error remains
<a href="https://imgbb.com/"><img src="https://image.ibb.co/hZZs68/Untitled.jpg" alt="Untitled" border="0"></a>
<a href="https://imgbb.com/"><img src="https://image.ibb.co/dBaRYo/Untitled.png" alt="Untitled" border="0"></a>

 

[Peter_SSs]

Hmm, has that target sheet name changed again?

Are there any #VALUE errors at all anywhere in the range U12:AG50 on the target sheet?

 

[Tim_Excel_]

I change my worksheets' names at least once an hour to keep it interesting

All jokes aside, I named my worksheets Variable Name 1 and 2 because the worksheet names are named after variables in my Macro.


I realised I was looking at the wrong columns for the "1" values. The columns that contain those values are T, W, Z, AC and AF. Other columns hold formulas, or are empty. Your LEFT() function is still valid, as the headers are still like I showed before

 

[Peter_SSs]

So it is all good now? Resolved the #VALUE issue?

 

[Tim_Excel_]

Sorry for my absence, but summer asks for vacation trips.

As for your question, yes I've solved the #VALUE issue. However, the code does not operate fully as I wish. If the cell in column O in sheet "componenten" is empty, the formula returns a blank value. Instead, I wish to have no blank rows and thus the formula should (in case of an empty cell in column O) look at the next cell. This was my main reason for opening the thread, perhaps I should have been a little more clear.

 

[Peter_SSs]

I don't think that I can contribute anything further without having a representative set of dummy sample data and expected results that I can test with. There are several issues with sample data provided as you did in post 9:
1. The columns don't match what you are now describing
2. The columns between the columns containing the "1" values are shown blank, including the headings. 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.
3. The sample data cannot be copied/pasted from those images to test with.

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.

 

[Tim_Excel_]

I've attached a mockup file, which should give you some insight into the problem. I've left out unnecessary data. Please let me know if you have any questions about the workbook.

https://we.tl/XOHk4qmU7v

 

[Peter_SSs]

I've attached a mockup file,

Where are the expected results?

I've left out unnecessary data.

You may be right, but I can't tell without seeing what could be in those columns. For example, as far as I know, some of those cells might also hold '1" values or maybe that is imppossible, which is why I asked

The columns between the columns containing the "1" values are shown blank, including the headings. 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.

Previously you said some of them at least contained formulas.


I cannot see where you addressed this

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.