How to skip columns when writing to a range in Excel?

Maybe you need named multiple range like C3:K12 & N3:V12 & Y3:AG12 & etc... for all isolated range , then use option of output to Named-Range to get your want.

cjaneczko

You could also use a couple of select tools and split the workflow, then create an output for each range. You'd need to use a Block until Done or Control Container though so each field has a chance to write without throwing an error that the file is already in use.

I'm unable to use named ranges to output to an Alteryx excel file. How would I do that? I tried updating the "Write to File" option and added the named range after the sheet name and that doesn't work:

"D:\...\Alteryx Output to Excel Range.xlsx|||Sheet1$Test_Row_1"

This just outputs to a sheet called "Sheet1$Test_Row_1". I'm unable to find the correct syntax for overwriting to a named range excel template

cjaneczko

Are the columns the same every time that you are writing to or does the range change? if they are the same you can forgo the named ranges and hard code the range in the output.

"D:\...\Alteryx Output to Excel Range.xlsx|||Sheet1$A1:Z1000" for example.

The Named Range output would look more like this. It doesnt include the sheet name as your named range in Excel would already have that information.

"D:\...\Alteryx Output to Excel Range.xlsx|||Test_Row_1"

I see. The columns stay the same BUT I need to skip some columns so for example I need it to write to C3:BC12, but if I want to skip the following columns: AS, AT, AH, AI, W, X, L, M. I won't be able to do that without a named range.

Output to Range of Sheet1:

D:\...\Alteryx Output to Excel Range.xlsx|||Sheet1$Test_Row_1

Output to Named Range of Book:

D:\...\Alteryx Output to Excel Range.xlsx|||`Test_Row_1`

jstammers

Hi @avanibhatnagar

Do the work in Excel?

just output your "alteryx" data to an excel tabpage. (which you can hide later in excel if you like)

then on a different tabpage in the same excel doc, you map the data over from that alteryx data tabpage to the columns you want and have your excel formula's in the columns you want... backwards way to do it. but once set up it should just work ?

@flying008 When I try doing this: \...\Alteryx Output to Excel Range.xlsx|||`Test_Row_1` for named range defined for the entire workbook I get this error:

In your case, change the \...\Alteryx Output to Excel Range.xlsx|||`Test_Row_1` to \...\Alteryx Output to Excel Range.xlsx|||Test_Row_1

[remove the up dot.]

Still no luck. Error says this:

Output Data (2) Invalid range: "Test_Row_1". Check sheet to make sure cells in range are contiguous, or specify a new range.

It does say "contiguous" so i wonder if named ranges won't work??

Here is the alteryx workflow and excel. Not sure what i'm doing wrong.

Alteryx Output to Excel Range.xlsx

Output To Range.yxmd

A single named range in an Excel file must consist of contiguous cells without any gaps. Your [Test_Row_1] is actually two separate and disconnected ranges, so naming it in this way is meaningless.

Therefore, your [Test_Row_1] should be defined as "=Sheet1!$B$1:$E$2", either defined [Test_Row_1]=Sheet1!$B$1:$C$2 and [Test_Row_2]=Sheet1!$E$1:$E$2 , Then output according to your expectations into the corresponding named ranges.

@FLY So then I would have to output using different output tools. I was hoping to output using just 1 tool where it would skip the columns I want it to skip looks like that is not an option then

No, you still just use 1 output tool to get your want, only need assign the multiple-ranges by your formula.

@flying008 Sorry - I feel like I'm still stuck (Thanks for being here still!). I'm not sure what you mean by assigning multiple ranges by the formula?