Create New Column and fill data from specific cell address

Question

I am bringing in multiple Excel sheets in which the header row is different for several of them and the rows above contain uneeded data except for 1 specifc cell range D4:E4

I've successfully formatted the needed header row but now need to create a new column "COMP#", go to the range D4:E4 (or just D4 as the two are merged), get that number from that cell and place it in the first row of "COMP#" and then fill down to the last row.

Trying to use the multi-row formula tool but can't seem to get that to work for me.

Thanks!

Toni

createnewcolumn.xlsx

RodL · Accepted Answer

Typically I would create a parallel data stream in the workflow to identify that specific cell (if it is consistent where it is in the spreadsheet, I'd use a Select Records and a Select to pin down the cell).

Then I would just use Append Fields to bring it back into your main workflow.

The Multi-Row Formula tool is more about using values above/below each record as it goes through the data.

Philip · Accepted Answer

Rod's solution is the way I do this. I also do this with the Summarize tool to get averages over all values and append as a new column, too.