This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have a workflow that outputs to several .xlsm files, and some of the files have just over 1 million rows, exceeding the Excel limit. When it reaches a file that exceeds the limit, it doesn't output anything else after it. How can I make it so when the rows exceed the max it outputs to a different file or tab?
I got a sum tool that counts how many rows there are, and can edit the filename, but if I just add a filter to change the filename variable to +1, it'll just cap out at the same spot under a different filename. How do I get it to put the extra rows onto another sheet or workbook?
I appreciate the input, but we've yet to come to a solution.
If I just set the sheet name to change every 1 mil rows, it'll output with odd sheet names. D1 will have Sheet1, D2 will have Sheet2, ......... D21 will have sheet21, etc. It looks strange to open a document and see only one sheet for that department called Sheet56. There are only 2 or so departments that have over 1 million rows that I need output.
Here's what I've got now at the end of the day. It still doesn't work, but I took inspiration from both of you and feel like I'm closer.
How do I get the RecordID to set back to 0 when the Department# changes? If I could do that, it would work with what I've got below. Right now all it does is set recordID to 0 the row the department changes, then goes back to counting what it was originally at, which messes up the sheet naming.
Multi Row function (updates recordID): IF [Dept] = [Row-1:Dept] THEN [RecordID] + 1 ELSE [RecordID] = 0 ENDIF
Formula Tool 1 (creates Sheet# field): IF [RecordID] > 1000000 THEN 'Sheet2' ELSE 'Sheet1' ENDIF