Fix Excel Formula

[Claribel Szwaja]

Aconstant is a value that is not calculated; it always stays the same. For example, the date 10/9/2008, the number 210, and the text "Quarterly Earnings" are all constants. An expression or a value resulting from an expression is not a constant. If you use constants in a formula instead of references to cells (for example, =30+70+110), the result changes only if you modify the formula. In general, it's best to place constants in individual cells where they can be easily changed if needed, then reference those cells in formulas.

A reference identifies a cell or a range of cells on a worksheet, and tells Excel where to look for the values or data you want to use in a formula. You can use references to use data contained in different parts of a worksheet in one formula or use the value from one cell in several formulas. You can also refer to cells on other sheets in the same workbook, and to other workbooks. References to cells in other workbooks are called links or external references.

By default, Excel uses the A1 reference style, which refers to columns with letters (A through XFD, for a total of 16,384 columns) and refers to rows with numbers (1 through 1,048,576). These letters and numbers are called row and column headings. To refer to a cell, enter the column letter followed by the row number. For example, B2 refers to the cell at the intersection of column B and row 2.

Relative references A relative cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to. If the position of the cell that contains the formula changes, the reference is changed. If you copy or fill the formula across rows or down columns, the reference automatically adjusts. By default, new formulas use relative references. For example, if you copy or fill a relative reference in cell B2 to cell B3, it automatically adjusts from =A1 to =A2.

Absolute references An absolute cell reference in a formula, such as $A$1, always refer to a cell in a specific location. If the position of the cell that contains the formula changes, the absolute reference remains the same. If you copy or fill the formula across rows or down columns, the absolute reference does not adjust. By default, new formulas use relative references, so you may need to switch them to absolute references. For example, if you copy or fill an absolute reference in cell B2 to cell B3, it stays the same in both cells: =$A$1.

Mixed references A mixed reference has either an absolute column and relative row, or absolute row and relative column. An absolute column reference takes the form $A1, $B1, and so on. An absolute row reference takes the form A$1, B$1, and so on. If the position of the cell that contains the formula changes, the relative reference is changed, and the absolute reference does not change. If you copy or fill the formula across rows or down columns, the relative reference automatically adjusts, and the absolute reference does not adjust. For example, if you copy or fill a mixed reference from cell A2 to B3, it adjusts from =A$1 to =B$1.

Conveniently referencing multiple worksheets If you want to analyze data in the same cell or range of cells on multiple worksheets within a workbook, use a 3-D reference. A 3-D reference includes the cell or range reference, preceded by a range of worksheet names. Excel uses any worksheets stored between the starting and ending names of the reference. For example, =SUM(Sheet2:Sheet13!B5) adds all the values contained in cell B5 on all the worksheets between and including Sheet 2 and Sheet 13.

You can also use a reference style where both the rows and the columns on the worksheet are numbered. The R1C1 reference style is useful for computing row and column positions in macros. In the R1C1 style, Excel indicates the location of a cell with an "R" followed by a row number and a "C" followed by a column number.

When you record a macro, Excel records some commands by using the R1C1 reference style. For example, if you record a command, such as selecting the AutoSum button to insert a formula that adds a range of cells, Excel records the formula by using R1C1 style, not A1 style, references.

You can turn the R1C1 reference style on or off by setting or clearing the R1C1 reference style check box under the Working with formulas section in the Formulas category of the Options dialog box. To display this dialog box, select the File tab.

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 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.

3a8082e126