Drop down list (list cell range) which include data from the row of the drop down list

Question

Hello.

I have an excel file with 2 sheets. the first include the data columns and a comment column from row 2 to 50. The second include the text of comments (20-30) which I like to use for drop down list but i like to include data from the first sheet in the text, I used the indirect to include the data Indirect("Sheet1!A"&ROW()). I am out of luck. I can't generate the drop down list in the first sheet . I try to make the dropdown list which include the text and when select the element of the drop down list in the sheet1 comment column in row x, it will include the specified data from sheet1 for row x. The data is in different part of the text in every comment in sheet2. Somebody can give me an advice how I can do it?

Thanks a lot for any help

Regards

Answer 1

Hello.

I could solve the problem in LibreOffice but in Excel it doesn't work. Can you check both files and help to figure out what can be the problem with it in Excel?

I tried various things and the final result of the dropdown list is at Munka1!U23.

LibreOffice https://drive.google.com/file/d/12HIsidnQ_RUEzrmPHRFdig1b8cCtWOIV/view?usp=sharing

Excel https://docs.google.com/spreadsheets/d/1CfirWR_cCyzFd5-iG7uvDYTrE-7q8I7O/edit?usp=sharing&ouid=107387275611375268121&rtpof=true&sd=true

It seems Excel doesn't allow CONCATENATE or CONCAT with array. Is there any other solution to merge the texts (which is work with arrays and drop down list?

Answer 2

Sorry for replying late.

I suggest you break up the text of comment cells to separate cells.

Answer 3

Hello.

Is it even possible this way or have to break up the text of comment cells to separate cells(text and reference)?

Regards

          Csaba

Answer 4

Hello.

There is the link to the file. https://1drv.ms/x/s!Aka1HRzQ-ky6c-qVppxfQRFLf7g?e=QUGOgf

Because of confidential data I had to delete the most in Munka1 but I left Dátum. So in munka3 there are the texts of the comments and where it show "Dátum" it means i like to put the Dátum row data (Munka1) into the dropdown list at the relevant row of Megjegyzés - Ügyfélszolgálat

Regards

         Csaba

Answer 5

Hello , Necromancer007

Welcome to Microsoft Community and appreciate your time taken for sharing detailed information.

I know you are looking for very detailed steps. To achieve this, you files need to be researched first, because detailed steps need to combine the file content. So, can you share your file in OneDrive and copy the link to Microsoft Community, so that everyone in Microsoft Community can view the file to figure out the best workaround together? Detailed steps are listed here: Share OneDrive files and folders (microsoft.com)

Don't contain confidential or personal information in your files.

If your problem can't be solved, please let me know in time, and I will continue to think of a solution.

Best Regard,

Yuhao Li| Microsoft Community Support Specialist