update custom formatted spreadsheet based on value in specific column

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.

Output

Accepted answers

DavidP

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.

write to excel cell range.yxzp

All comments

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

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

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

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.

write to excel cell range.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703