How to create new column from one of the cell of excel sheet ?

[shukla_sumit]

@yalmar_m @Kenda

how to create new column from particular row data.

for example in the attached sheet I have row number 5 "Data Collection 2017-18" from which i wanted to create date column and the value will be 20180331 means last day of march for year 2018 ,if the year is 2016-17 then 20170331 and so on for all the row of that file.

Second In row number 6 which is "My Trust Name" wanted to create new column trust name and the value for that column will be the value of that cell for all the rows of that file ,currently it's My Trust Name.

I wanted to add this column into my exisitng workflow result.

[shukla_sumit]

Thanks @jd I have made the changes and its working fine now.

[shukla_sumit]

Hi, @jdunkerley79 @danilang @Kenda

In my attached workflow i am fetching trust name from the file and and creating new column trust name and putting trust name for all the rows for that particular file.

But for one single trust i have multiple files with different file names so i cannot take trust name from file name as i am using wildcard search to fetch all files in one go.

Instead of file name I just wanted to fetch that trust name from inside of that file. in row 6 or sometimes from row 7 and need to put that name into trust name column for all the row belongs to that file.

How i can achieve this, Can you help me ?

[jdunkerley79]

I suggest adding a filter before the Row multi row formula filtering for non empty rows. The row numbers then become 2 and 3 for date and trust name. Workflow for this attached.

If this doesn't work you will need to add a heuristic to find (e.g. search for first Specialty value in F2 and get previous non-empty row).

[shukla_sumit]

Hi James I tried the same but my concern is i am using multiple join tool so in connection #1 and #2 i am fetching filename as a trust name and then i am joining so i am depend on file name . If the file name is incorrect or multiple files are present at that time i will miss that data and join condition will  be failed.

So in all the connection i want that trust name from inside the file not from the file name.

Thanks