stop formulas from pulling wrong information

Question

Hello! I have a data spreadsheet that I pull information from for an import spreadsheet that is a separate excel. They do have to be separate files. I need to be able to sort, filter, and add/remove lines on the data file without the formula data changing on the import file. I had everything linked to pull from specific cells, but then when I removed lines on the data file the formulas for everything on the import file got shifted and messed up. I need a formula (if possible) to have everything auto correct when rows get deleted, so I don't have to keep redoing it. It's extremely time consuming to link everything and figure out the issues. How can I do this? I linked to example files below. The formulas below are what I am using on the import file. The information on the data file is hard entered so there are no formulas. Just specific lines for specific companies.

used in column C on import file:

='[data file example.xlsx]Master FOF'!$G$2

used in column F on import file:

=IF('[data file example.xlsx]Master FOF'!$H$2="", "", TEXT('[data file example.xlsx]Master FOF'!$H$2, "mmddyy"))

data file example:

https://storage.to/A6bQvuyfh

import file example:

https://storage.to/IZQOTNYmA

Answer 1

I figured this out with an XLOOKUP so I am all set.

Answer 2

I can't get either of those formulas to work. I get the pop up saying there's an error. I also really don't need to use the JE Group as reference at all. That column is only there for sorting purposes and has nothing to do with the data in question. Could we modify it to just pull data from various columns based on the name in column A on the data file that matches column A on the import file? The names are the same on both files. The import file pulls the data from columns C-J on the data file. On the data file each company has 12 lines - one for each month. On the import file there are 12 tabs - one for each month. That may be simpler, so the formula would pull based on the name and month.

Answer 3

n/a - comment posted twice

Answer 4

Hi Megan,

Thank you for reaching out.

The original formulas were linked to fixed cell locations, so deleting or sorting rows in the data file caused the results in the import file to shift.

To prevent that, use formulas that match the FUND name in column A and use the JE GROUP value in column B to select the correct amount and date.

• Enter this formula in C4 for the amount:=IFERROR(INDEX('data file exampl*'!$C$2:$J$1000,MATCH($A4,'data fil* example'!$A$2:$A$1000,0),2*INT($B*)-1),"")
• Enter this formula in F4 for the six-digit activity date:

=IFERROR(TEXT(INDEX(*data file example'!$C$2:$J$1000,MA*CH($A4,'data file example'!$A$2:$A*1000,0),2*INT($B4)),"mmddyy"),"")

• Copy these formulas to the JE GROUP rows ending in .1. Make sure the row references update when copied. For example, the formulas in row 8 should reference $A8 and $B8. For the negative amount rows ending in .2, use: =IF(*4="","",-C4)
• For the final import amount, use: =SUM(C4*D4)
• For the repeated activity date, use: =F4
• For the final import date, use: =IF(F4<>"",F4,G4)

I tested the formulas by sorting the source table and deleting a source row. The remaining amounts and dates continued to stay associated with the correct companies.

Please ensure the FUND names match exactly in both locations and that each FUND name is unique.

I hope your issue gets resolved soon. Any updates would be greatly appreciated.