Excel help. Need to follow referenced cells when they are shifted down.

%3CLINGO-SUB%20id%3D%22lingo-sub-1142940%22%20slang%3D%22en-US%22%3EExcel%20help.%20Need%20to%20follow%20referenced%20cells%20when%20they%20are%20shifted%20down.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1142940%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20new%20member%20first%20time%20post.%20Thanks%20for%20considering%20my%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20sheet%20that%20draws%20its%20data%20from%20Syspro%20(our%20accounting%20software)%20and%20it%20populates%20columns%26nbsp%3Band%20rows.%20This%20sheet%20is%20about%2020%20columns%20wide%20and%20350%20rows.%20Since%20I%20don't%20need%20all%20that%20data%20I%20have%20another%20sheet%20that%20only%20uses%20the%20rows%20and%20columns%20I%20need.%20The%20rows%20are%20in%20numerical%20order%20by%20'item%20number'%20in%20column%20A.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESay%20the%20rows%20start%20at%26nbsp%3B100%26nbsp%3Band%20go%20to%20900%20but%20not%20using%20every%20number%20since%20there%20are%20not%20items%20created%20yet.%20Every%20so%20often%20when%20a%20new%20item%20is%20created%20in%20Syspro%2C%20it%20automatically%20populates%20the%20first%20sheet%20and%20inserts%20it%20in%20the%20row%20in%20numerical%20order.%20Now%20my%20second%20(smaller)%20sheet%20that%20references%20less%20columns%20and%20rows%26nbsp%3Bis%20thrown%20off%20by%20any%20row%20that%20comes%20after%20the%20new%20item%20created.%20Say%20the%20new%20item%20number%20was%20425%20and%20it%20was%20automatically%20inserted%20into%20row%20300%20(previously%20used%20by%20item%20number%20426)%2C%20now%20if%20a%20cell%20on%20sheet%202%20used%20to%20reference%20row%20300%20as%20item%20426%20and%20all%20its%20corresponding%20data%2C%20it%20will%20now%20reference%20the%20same%20row%20(300)%20but%20will%20reference%20item%20425%20since%20that%20was%20automatically%20inserted%20into%20row%20300.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20this%20happens%20I%20have%20to%20manually%20go%20in%20and%20select%20each%20cell%2C%20and%20enter%20the%20new%20formula.%20This%20is%20time%20consuming%20and%20takes%20hours!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESince%20the%20cells%20do%20not%20reference%20every%20row%20and%20they%20skip%20some%2C%20we%20cannot%20just%20change%20the%20formula%20in%20one%20cell%20and%20drag%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20goal%20is%20to%20find%20a%20way%20to%20have%20them%20follow%20the%20data%20they%20reference%20even%20if%20they%20are%20shifted%20down%20because%20one%20was%20added%20in%20their%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20one%20has%20me%20stumped%20but%20I%20feel%20like%20it%20should%20be%20a%20simple%20answer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1142940%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-1142961%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help.%20Need%20to%20follow%20referenced%20cells%20when%20they%20are%20shifted%20down.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1142961%22%20slang%3D%22en-US%22%3EALSO---%20I%20do%20understand%20that%20if%20I%20were%20to%20manually%20insert%20a%20row%20myself%20that%20all%20the%20formulas%20would%20follow%20the%20data%20instead%20of%20the%20row.%20But%20since%20this%20sheet%20is%20populated%20from%20Syspro%20this%20does%20not%20work.%20Thanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1143129%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help.%20Need%20to%20follow%20referenced%20cells%20when%20they%20are%20shifted%20down.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1143129%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F540158%22%20target%3D%22_blank%22%3E%40Jeffmyers%3C%2FA%3E%26nbsp%3BI%20suspect%20that%20your%20import%20from%20the%20accounting%20system%20is%20dumping%20an%20entirely%20new%20(sorted)%20table%20in%20your%20first%20sheet.%20In%20other%20words%2C%20it%20does%20not%20insert%20a%20row%20in%20the%20Excel%20sheet.%3C%2FP%3E%3CP%3EYou%20need%20to%20make%20a%20VLOOKUP%20(or%20LOOKUP%20or%20a%20combination%20of%20INDEX%20and%20MATCH)%20function%20in%20your%20other%20sheet%20so%20that%20it%20always%20finds%20item%20number%20425%20irrespective%20of%20the%20row%20number%20in%20the%20first%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello, new member first time post. Thanks for considering my issue.

 

I have a sheet that draws its data from Syspro (our accounting software) and it populates columns and rows. This sheet is about 20 columns wide and 350 rows. Since I don't need all that data I have another sheet that only uses the rows and columns I need. The rows are in numerical order by 'item number' in column A.

 

Say the rows start at 100 and go to 900 but not using every number since there are not items created yet. Every so often when a new item is created in Syspro, it automatically populates the first sheet and inserts it in the row in numerical order. Now my second (smaller) sheet that references less columns and rows is thrown off by any row that comes after the new item created. Say the new item number was 425 and it was automatically inserted into row 300 (previously used by item number 426), now if a cell on sheet 2 used to reference row 300 as item 426 and all its corresponding data, it will now reference the same row (300) but will reference item 425 since that was automatically inserted into row 300.

 

When this happens I have to manually go in and select each cell, and enter the new formula. This is time consuming and takes hours!

 

Since the cells do not reference every row and they skip some, we cannot just change the formula in one cell and drag it.

 

My goal is to find a way to have them follow the data they reference even if they are shifted down because one was added in their row.

 

This one has me stumped but I feel like it should be a simple answer.

 

Thanks!

2 Replies
Highlighted
ALSO--- I do understand that if I were to manually insert a row myself that all the formulas would follow the data instead of the row. But since this sheet is populated from Syspro this does not work. Thanks
Highlighted

@Jeffmyers I suspect that your import from the accounting system is dumping an entirely new (sorted) table in your first sheet. In other words, it does not insert a row in the Excel sheet.

You need to make a VLOOKUP (or LOOKUP or a combination of INDEX and MATCH) function in your other sheet so that it always finds item number 425 irrespective of the row number in the first sheet.