How do i keep a date and number in a cell when the 'project' it relates to all moves down

[clairehickling]

Hi

In the process of setting up a spreadsheet and we have a formula that works out our wip figure which is fine and works until a new job is added to the list.

However, the list of jobs on the left hand side links to another spreadsheet where new jobs are added when they are requested. So when a new job is added and the spreadsheet refreshes, all of the jobs move down a row including the cost per month as we have a v look up formula in there. However, because we are entering the date we last invoiced in each month ourselves and isnt looking for the date from another spreadsheet, it isnt looking for the job reference and therefore is remaining in the cell we first entered the date in . How do we do it so that it shifts down a row each time a project is added.

We have thought about adding another v look up formula but the spreadsheet is already really big and so if possible do not want to do this.

Please help!!!

[oeldere]

The new job is added above the other data?

Show an excel file with an example of the vlookup and please explain which cells will be added new in the related table.

[clairehickling]

yes the job is added above, so for example, the new job number would be 1220 and it would be inserted between job number 1219 and 0.

Currently, when a new job number is added, all of the jobs shift down apart from the wip figure and the opening wip

I cannot upload the spreadsheet as it is confidential - however the formula for the v look up is =IFERROR(VLOOKUP(A471,'L:\Secure Admin\Financial\Consolidated Timesheets\2017-2018\[01_July 17.xlsx]JULY'!$A$2:$ZZ$5000,36,FALSE),0)