#REF! 10 sheets linked to each other. When I delete a row i have to update each table in each sheet.

Question

Help needed! These sheets are used for our weekly payroll.

The ten sheets are linked to each other. 7 of the ten sheets have 9 individual tables. The first two sheets have the employees names. Once we delete a former employee or add a new employee to the list this is where our problems occur. #REF! pops up in all of the sheets. In order to remove #REF! we need to update each individual table. Thats 9 tables in each sheet (8 sheets). That's 72 tables which need to be updated which is a waste of time.

Anyone able to help? I'll send someone our excel file to see if someone can fix the problem.

Thanks

detlef_lewin · Answer

Hi Tom&nbsp;Could you provide a workbook without the #REF!-errors?&nbsp;

tom cunniffe · Answer

detlef_lewin · Answer

Tom, your workbook is just a bunch of criss-cross referencing and nothing what could be considered as a model.

My advice is that you redo the workbook and establish a model.

That includes using lookup fuctions as INDEX()/MATCH() or VLOOKUP() or pivot tables.

tom cunniffe · Answer

Never heard of; Index, Match or VLookup functions before. Is it a matter of inputting those functions in my already strcutured worksheet or will I have to start from scratch?

jkpieterse · Answer

Hi Tom,&nbsp;I think you would be better off starting from scratch again. Your current file is a very intertwined set of direct cell-to-cell formulas which "break" as soon as you try to do important things like adding or removing people. Even doing more simple things like sorting the information is problematic. Without thorough knowledge about what your Excel file needs to do it is quite hard to advise how to set it up properly however. I would start by looking in Help for what the VLOOKUP function can do for you.

Forum Discussion

#REF! 10 sheets linked to each other. When I delete a row i have to update each table in each sheet.

Share

Resources