update custom formatted spreadsheet based on value in specific column

[linpeng921]

Hi all,

I am building a workflow to pull data from various sources in order to update an existing spreadsheet without messing up the spreadsheet format only if the "Need to Update" column is flagged as "Y". See below for sample table. I want to be able to update value 1, 2 and 3 for object B and C with new pulled info.

As of now I am able to pull the required values in workflow and then manually copy and paste the values to a temp spreadsheet and then use a vlookup in workbook to manually update the values and I would like to be able to automate to directly overwrite the table for the rows that need to be updated (Y).

I would really appreciate some recommendation on which function(s) to use to solve the problem.

[DavidP]

One option is to create a hidden sheet in your excel file to write the data to. Within your excel file you can then set up cell formulas to map the values from the hidden sheet to the values of the cells in the sheet shown in your post. 

This becomes unworkable if you have large data sets, but could be ok if it's a limited number of cells.

This way the formatting of your Sheet in excel is not impacted.

[linpeng921]

Thanks David for your solution, that is what I am doing right now manually.

The issue is that the data get pulled weekly (value 1, 2 and 3) and the table is already getting very large. Also "To be Updated" flag will change from Y to N eventually and at that point, the value 1, 2, 3 for that Object should not be updated anymore with new data pull, linking value 1, 2, 3 to embedded table(new data pull) will not work, unless I convert the formula to value before each weekly data pull for all objects flagged as Do Not Update(To be Updated = N), I am not sure if that is doable in Alteryx.

In addition, I am having another issue, when I load the excel spreadsheet, it is loading the entire sheet including null column/row, which artificially increased the file size when importing file.

Lin

[DavidP · Accepted solution]

I've been playing around with a couple of options.

1. Define a range in the excel file and then write to the range instead of writing to a sheet.  - Couldn't really get this to work. This option only works with Append to existing sheet, but if there is data in the sheet, the new data gets added below it.

2. Writing the data to a specific cell range in the sheet in the excel file.

Option 2 kind of works, but it has problems of its own. Have a look at the example attached. I can specify a specific cell range to write to in the sheet as shown with the Overwrite sheet option. It does only overwrite the cells in the range without affecting the formatting of other cells, but in the process, is alters the Excel file's XML and when you open the file in Excel, you get the following error message.

We found a problem with some content in 'sample file.xlsx'. Do you want us to try to recover as much as we can?...

If you click Yes, it does open the file with the updated cells.

There's a lot written about this issue on the Community.

[linpeng921]

Thank you David, I am able to append the data by using Append to Existing Sheet option. I appreciate all the input

[ShravaniChanda]

Hey, I am facing similar challenge, in my case I want to update certain cells in sheet A which eventually effect other sheets B , C D and soon. My aim is to just update couple of columns in Sheet A, which I was able to do it but the challenge us it is done for one of my company codes, I do have 30 more company codes which required same formatted output in 30 different files. Any suggestion? 

Option that I have isto duplicate 30 excel templates manually and divide the results into company codes and connect the result to 30 different output tools to achieve the result which is not efficient way. And also in future the company codes might increase always creating template manually creating an output tool makes tougher. 

Please suggest.

[DavidP]

Hi @ShravaniChanda 

You can use the same method, but dynamically update the filename in the Output data tool so that your workflow writes each subset of data to the correct file based on the Company Code. For instance, if your data looks like this:

You can create a filename (and path if you wish) with a formula tool and then use that in the Output data tool to dynamically write the correct records to the correct files.

Note the configuration of the Output Data tool

Example attached.

[ShravaniChanda]

i am getting this error

Output tool configurations