This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(156.8, 73%, 25%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EE%3C/text%3E%3C/svg%3E)
In Excel 2019, I have a master Sales file that auto feeds information from all 5 stores monthly sales excel spreadsheets. When i open up a store Sales spreadsheet and then open my master Sales spreadsheet, after i click update on the master, all the formulas change on my master. It changes the row # so it does not pull from the correct row. What am I doing wrong?
Better yet, how do i keep a formula constant in the destination file but allow the row # to change in the source file with the changing data?
Hi Erin,
As the supplementary information is not an answer, so I convert it as a comment.
Hi Erin,
Could you please provide the screenshots which show us how do you feed the information from store Sales spreadsheet into the master Sales spreadsheet? Especially the formulas you use.
Please also note, cover the sensitive information when there is.
@Erin ,
Is there any update?
@Erin ,
Thanks for your sharing.
Please backup your master file first. Then try to add absolute reference in your formula to have a check. Such as 'B:\[2.FY19-20 BOT WAUSAU.xlsx]AUG 20'!$F$188.
Or you may also try to use Indirect function, like the formula =INDIRECT("'B:\[2.FY19-20 BOT WAUSAU.xlsx]AUG 20'!$F188").
Thanks, I have tried the first suggestion. Then I was told that ($A$1) allows for neither the column or the row to change and was told to remove $ in front of the row #. I will try your 2nd suggestion.
@Erin ,
Thanks for your reply.
Did you try the second suggestion, the Indirect function? Any questions please let me know.
If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Emily,
I tried the indirect function. Here is what happened...
@Erin ,
Did you open the workbook "2.FY19-20 BOT WAUSAU.xlsx"? The Indirect function would get the value after the source file is opened.