Excel Formula For Vat

[Alayna Rother]

Not quite sure if it is the same with the normal Excel Writer,
But if you start a string column with = you can also write formulas directly with the template writer.
=1+1 will put that formula into Excel (formula has to be in english format)

But then, at the end of every week, we receive a separate .csv of data that contains updates to the master data. Some rows are added, some rows are updated, and some rows are simply duplicates of rows already in the Master workbook.

I've found some references in other posts that writing the formula as a string and then saving the file as .csv solves the problem, but a) that's not really an option in our environment, and b) I tested it and it didn't work anyway.

Whenever I'm faced with a situation in which I need to retain parts of an Excel workbook, I just create a data tab in the workbook and output my data there. Then use formulas to reference this. Works quite well. You can also go a step further and use commands to save the workbook with a new name (i.e. changing the date) before you write your data.

I like the idea of writing to a data worksheet... but wouldn't this still suffer from the same issue? Alteryx is still opening the file and resaving it. Meaning it's still in charge of writing back out the original formula. Why is it that Alteryx can handle that, but not if it's on the same worksheet as I'm writing to?

If all you're done is writing to a workbook, it doesn't have to first read it in. Therefore you don't have the issue. The other thing is that Alteryx works tab by tab. So if you have one data tab that's in 'Alteryx' format you can read and write this with Alteryx without messing up the rest of your workbook.

Hello @carl_steinhilber , @Emmanuel_G , @r4upadhye , @kat
Hope y'all are doing good.

Y'all can use the workflow available in the below link which I had uploaded on the Alteryx Knowledge Base few months ago. This solution workflow will let you Retain / Preserve the Formulas which existed in the original Excel File (Master Workbook) after passing it through the Alteryx Workflow. Kindly make the necessary modifications as per your requirements. Please like and accept it as the solution if you found it helpful.

Retain Excel Formula After Running The Excel File Through Alteryx Workflow

Regards,
Allwyn Thomas

I am new in alteryx community (my first subject) and I have an issue with a data input. Database table have formulas in a rows(excel file). After uploading it to the alteryx i get incorrect result of the formulas. In excel file the result of the formula '=G45' gives me string 'Record' in alteryx it shows '00:00:00'. I tried to look for the answer but I haven't found anything. Thank you in advance for help or any suggestions...

Actually, I ran into a situation like this recently with an odd field showing up like a date, pretty sure I know what's causing it - the field that is in your Excel file is probably formatted as a Time format... so even though it looks right in Excel because Excel sees that it's a word not a time, when Alteryx reads the file, it tries to make the data match the formats that are designated in the Excel file, so it's converting it to Time (and a word converted to time will show up as 00:00:00...)

Found a way!! Couldn't possibly tell you why this works, but if you change the input file type from Microsoft Excel to Microsoft Excel Legacy, it will work. Also, I noticed that the field format in Excel doesn't matter if you have the word typed into the cell but formatted as Time... it only inputs it as Time format in Alteryx if it gets the word in the Time cell via a formula. So since your cell actually contained "=G45" not the word "Record", it was showing up as Time... but if you were to type Record into that cell, instead of the formula, it would show up as Record.

Anyway, if you switch the Input Tool to bring in the file as Microsoft Excel Legacy instead of just Microsoft Excel, that might fix it for you! Someone with far more technical expertise than I would need to tell you why that works haha. :)

Strange indeed!! I'm on version 11.0.5.26351, but I feel like the Excel Legacy option was available even before, maybe even in version 10?? This sounds like a technical question... :) You might post a different thread in the Community asking for some clarification on that!

So what I found in my case was, because I formulas, for example vlookups, that are embedded in my excel input file/sheet, allowed the first line to produce a #N/A for a non match. What I found was when I replaced all #N/A and NULL values with blanks, Alteryx digested those fields correctly.

I am not using a reference to the file's name on my own local computer. As a result, when the file gets synced to each shared Dropbox user's file, this "filename" reference should now reference whatever their file name is (including path location, etc.) Unless there is some literal translation that is getting embedded within the CELL function that I can't control, this shouldn't be an issue. What do you think?

I was originally thinking that you replaced the path to the file with the word "filename" in an attempt to expunge the actual name of the file. That being said, if filename is truly nothing more than a reference to itself, and Office doesn't try to replace that with the full path, then it should be fine. If Office is replacing filename and instead embedding the full path and saving it within the file, it won't work.

Either way, Dropbox isn't the cause. It simply can't change your file in that manner. All Dropbox does is move files around. There's not even anything special about the Dropbox folder. It's just a folder like any other on the computer.

I have discovered the details about this situation that points to a new problem. The problem is that when some of my colleagues received an Email notice from Dropbox that the Excel spreadsheet has been updated, if those users do not have the Dropbox app installed on their local machines, but DO have an Office 365 account, then the web link in the Email opens the Excel file in a cloud-based location presumably instead of using their local installation of Excel and the synced version of the file. As a result of this web-based Excel program, it cannot resolve the file name properly. A work-around solution was to have my colleagues install the Dropbox app on their local machines. That way, when the open the file that way, then Excel is working with their local, synced file. The larger problem that this points to is that any document that expects to work with 'file information' when the physical file is being processed in a dynamic (think SaaS or cloud) implementation may discover that the non-local program may not have the ability to access information about the non-local file properties. With more and more people and organizations moving to SaaS programs, this may be something that needs to be considered.

Thanks for replying but I don't think your response is correct. I am using the CELL function with a "filename" parameter. The actual parameter is the literal keyword "filename" in quotes. I am not using a reference to the file's name on my own local computer. As a result, when the file gets synced to each shared Dropbox user's file, this "filename" reference should now reference whatever their file name is (including path location, etc.) Unless there is some literal translation that is getting embedded within the CELL function that I can't control, this shouldn't be an issue. What do you think?

Did this post not resolve your issue? If so please give us some more information so we can try and help - please remember we cannot see over your shoulder so be as descriptive as possible!

Thanks for the follow-up and I do agree with both your's and Rich's responses... The problem is when I raised this issue on Micro$oft's forums, they are saying that my assessment was correct - that the formula looks for the 'current' file's name when processing the "filename" keyword parameter of the CELL function so, if it is not working when used 'within some third party's sharing facility', then it must be that third-party's sharing or syncing process that is causing the problem... In other words... they are pointing the finger at Dropbox... And Dropbox is saying it's a Microsoft Excel issue... and us poor end user's are left without a solution... Not trying to shoot or shout at the messengers... (I really do appreciate your efforts!!!) I'm just frustrated that I can't find a solution.

c80f0f1006