Issue with referencing the correct cell In excel within a formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1478776%22%20slang%3D%22en-US%22%3EIssue%20with%20referencing%20the%20correct%20cell%20In%20excel%20within%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1478776%22%20slang%3D%22en-US%22%3EIm%20working%20on%20a%20file%20at%20work.%20I%20have%202%20tabs%20in%20an%20excel%20file.%20Tab%201%20contains%20an%20imported%20XML%20file.%20Tab%202%20contains%20some%20excel%20formulas%20that%20looks%20at%20data%20from%20Tab%201%20to%20bring%20into%20Tab%202.%20However%2C%20whenever%20I%20refresh%20the%20imported%20XML%20file%20in%20tab%201%2C%20and%20refresh%20the%20formulas%20in%20Tab%202%20it%20keeps%20changing%20and%20jumps%20to%20reference%20the%20next%20blank%20cell.%20For%20example%2C%20In%20cell%20A1%20in%20tab%202%2C%20I%20have%20a%20formula%20to%20pull%20in%20data%20from%20col%20%24H2%20from%20tab%201%20instead%20it%20changes%20to%20reference%20col%20%24H3105%20when%20I%20refreshed%20the%20imported%20xml%20file%20from%20tab%201%20and%20formulas%20from%20tab%202.%20So%20I%20checked%20the%20data%20set%20in%20tab%201%20and%20the%20data%20ends%20at%20row%203104.%20How%20can%20I%20resolve%20this%20issue%20so%20my%20formulas%20reference%20the%20start%20of%20the%20data%20set%20in%20tab%201%3F%20Could%20this%20be%20because%20excel%20doesn%E2%80%99t%20recognize%20the%20imported%20xml%20file%20as%20a%20different%20format%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1478776%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479479%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20referencing%20the%20correct%20cell%20In%20excel%20within%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479479%22%20slang%3D%22en-US%22%3EHey%20I%20have%20a%20solution%20for%20the%20same%20problem.%20Please%20provide%20the%20Excel%20file%20and%20I%20will%20help%20you%20out%20with%20this%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479512%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20referencing%20the%20correct%20cell%20In%20excel%20within%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479512%22%20slang%3D%22en-US%22%3EThank%20you%2C%20since%20it%20a%20work%20file%20on%20my%20work%20machine%20I%20won%E2%80%99t%20be%20able%20to%20provide%20it.%3CBR%20%2F%3E%3CBR%20%2F%3EWill%20you%20be%20able%20to%20give%20me%20instructions%20on%20how%20I%20can%20possibly%20resolve%20this%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20again.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479517%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20referencing%20the%20correct%20cell%20In%20excel%20within%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479517%22%20slang%3D%22en-US%22%3EOk%20then%20first%20create%20two%20tables%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479518%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20referencing%20the%20correct%20cell%20In%20excel%20within%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479518%22%20slang%3D%22en-US%22%3ESend%20me%20screenshots%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479560%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20referencing%20the%20correct%20cell%20In%20excel%20within%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479560%22%20slang%3D%22en-US%22%3EOk%2C%20I%20will%20send%20something%20over%20during%20the%20week.%20Thanks%20again%3C%2FLINGO-BODY%3E
Highlighted
New Contributor
Im working on a file at work. I have 2 tabs in an excel file. Tab 1 contains an imported XML file. Tab 2 contains some excel formulas that looks at data from Tab 1 to bring into Tab 2. However, whenever I refresh the imported XML file in tab 1, and refresh the formulas in Tab 2 it keeps changing and jumps to reference the next blank cell. For example, In cell A1 in tab 2, I have a formula to pull in data from col $H2 from tab 1 instead it changes to reference col $H3105 when I refreshed the imported xml file from tab 1 and formulas from tab 2. So I checked the data set in tab 1 and the data ends at row 3104. How can I resolve this issue so my formulas reference the start of the data set in tab 1? Could this be because excel doesn’t recognize the imported xml file as a different format?
5 Replies
Highlighted
Hey I have a solution for the same problem. Please provide the Excel file and I will help you out with this
Highlighted
Thank you, since it a work file on my work machine I won’t be able to provide it.

Will you be able to give me instructions on how I can possibly resolve this?

Thanks again.
Highlighted
Ok then first create two tables
Highlighted
Highlighted
Ok, I will send something over during the week. Thanks again