Excel Defined Name Changes Randomly

Question

I am working with Excel 365 and attempting to use a Defined Name within a table for use in a rolling total.  I want to exclude the 1st row as it's the header.  So I defined one name as RollingRowAbove = N(E1).  This name is then used in the rolling total column as

=RollingRowAbove+[@[Tickets In]]+[@[Monthly In]]-[@[Tickets Out]]-[@[Monthly Out]]

However, if I move to another cell and open the name manager the reference changes.

Any ideas another solution?

Thanks,

Answer 1

Hi

With blue table named Table1

Defined named FIRST_DATA_ROW has formula:

=ROW(Table1[#Headers]) +1

Formula in E3:

=IF(ROW()=FIRST_DATA_ROW, [Amount], $E2 + [Amount])

Answer 2

Can you post an example of the workbook to OneDrive. If you have sensitive data in the workbook then create a copy and replace the sensitive data with dummy data.

Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
4. Go to this link.  https://onedrive.live.com
5. Use the same login Id and Password that you use for this forum.
6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
8. Right click the file on OneDrive and select Share.
9. Select "Get a Link" from the popup menu.
10. Click in the field displaying the link and Ctrl and A should highlight the entire and then Copy and Paste the link into your reply on this forum. (I suggest that you avoid the "Copy" button on the "Get a link" screen because it introduces additional steps that are not required.)