Append rows to the output file

[mihir_mir_jb]

Hello All,

I have an output file that has multiple sheets and in one of the sheets I am appending rows from an input file based on only one column. For eg...I am appending column A - account number and rest of the columns are formula based so once the data is appended in column A,,,other columns like B,C ,D will auto populate based on column A. 

However the data is not getting appended exactly below the previous row. for eg if in the sheet the previou row was in row nuber 10, I am expecting the data to be appended from row 11 however it is not getting appended in row 11 however in some vague row like 15000. 

Coud you please help me with this. 

[Jean-Balteryx]

You can't add Excel formulas using Alteryx. But maybe you can replicate them into Alteryx and append them as well.

[HomesickSurfer]

Hi @mihir_mir_jb 

You can output to a named table range.  I assume that the formulas in the excel table will extend to the additional rows.

Alternatively, you can recreate the formulas within Alteryx.  I/we can help if you provide specific requirements.

[MelCee]

Hi
How do you append to a named table range? I've attached the config sheet in my output tool.

[HomesickSurfer]

Hi @MelCee 

Config your Output Data tool accordingly.  Instead of specifying the sheet name after the path, specify the named range.

[MelCee]

Thanks for the reply @HomesickSurfer. 

I made the suggested change and got the error message "Sheet must exist".

[MelCee]

Hey @HomesickSurfer

I got the append to work by clearing the cells between the old data and the appended data; however, I have a new issue 😪. The full table range is from A:I but cells A and B contain formula. I created a secondary range from C:I and appended the data accordingly, but the formulae in cells A and B did not autofill as it usually does when the data is manually pasted in.

[HomesickSurfer]

Hi @MelCee 

Can I assume your range is formatted as a true table?  Please confirm.

If so, the formulas will not autofill.  You will need to remove the formulas from the file, append to A:I, and create your formulas in A:B with Alteryx.

...

You bring in your data, add a "Record ID" tool to number the rows, and then add a "Formula" tool to add the string version of the formula. The example below would do A2-B2 on row 2, A3-B3 on row 3, etc...

"=A"+tostring([RecordID]+1)+"-B"+tostring([RecordID]+1)

When you export to Excel and open the file, it shows "=A2-B2", etc... in the field when you click on the cell, and it shows the numeric result when you're not clicked in the cell.

[MelCee]

Thanks for all the help!
This worked.

[HomesickSurfer]

Hi @MelCee 

Kindly Accept as Solution.  Glad it works.