Changing output location base on size of other outputs

[jkell]

I have a workflow that summarizes data and outputs it to a specific location in a tab with in an excel file (filelocation|||SheetName$A2:f50). 

Using the same data set, I also output un-summarized data to the same tab but under the summarized data. Generally I put the un-summarized data 10 rows below. (filelocation|||SheetName$A60:Q500).

Is there a why to make the cell locations more dynamic for both of these? For example if the summarized data only results in 10 lines, I dont want there to be a 50 row gap between the summarized and un-summarized data. Alternatively, if the summary results in 60 lines, I dont want to encounter an error, determine how many rows are needed and then adjust both outputs.

[PhilipMannering · Accepted solution]

Your cell reference could be derived from a calculation. Perhaps the location of the raw data is something like,

'filelocation|||SheetName$A'+ ToString(2 + [SummaryCount] + 10) + ':Q'

Where,

• 2 is the start of the Summary
• [Summary Count] is a count of records of the Summary Data (appended to Raw Data)
• 10 is the gap between Summary Data and Raw Data

(I have deliberately omitted the end cell range row as I believe this is not needed)

[Prometheus · Accepted solution]

I used a dataset that has countries and capitals with their population as a sample. I used a Block Until Done tool to send detailed data through the Count Records tool. I then took this number (TopCount) and appended it to every record of the detailed data. I used it to dynamically update a formula expression that creates the full path. Coming out of the Block Until Done tool's #2 output, I sent the data to be summarized and counted every record using the Count Records tool again and named the field (BottomCount) at the Append Fields tool. I brought in TopCount and used both counts in a Formula tool to determine the range for the summarized data by adding 11 to TopCount and adding this number to BottomCount to create the summarized range. After this, I used Select tools to deselect unnecessary fields for the output (TopCount and BottomCount) and then configured the Output Data tools to Change Entire File Path using the field I created called "FilePath" and unchecked "Keep Field in Output." In the output, you get your detailed data on top and then 10 rows down, you get your summarized data.

 

 

 

 

 

[jkell]

Thank you both - these work great!