Excel - value from cell after adding row

New Contributor



Firstly, I will explain what I'm trying to do
My goal is to create sheet that will get a lot of data from different excel files(same layout but different values in cells) and then with the data I will create summarize/report.

It was going well but I found a issue,
For example:
in Cell A1: Project Name - User writes here name of proect.

in Cell A2: Activity - User writes here hours how much he was working.

in Cell A3: Activity - similar as in A3
in Cell A4: Hours - there is sum of hours that user spent on project.


so in my Excel I took value A1 to get project name and value from A4 to get hours and it was perfect
but ... I didn't expect that someone will add extra row
and now
A1 - Project Name

A2 - Activity
A3 - Activity

A4 - Activity

A5 - Hours


and unfortunately my excel sheet is still focused on A4 and it won't get hours.
Is there any possibility to make it work?

If u don't understand what I mean, let me know I will try to explain it further.
Maybe solution is really easy but I don't see it

Best Regards,

3 Replies


Will the rest of the column below the cell with total hours be empty? If so, you can use a formula like this:


=LOOKUP(9.99999999999999E+307,'Sheet Name'!A:A)

@Hans Vogelaar   

nah, there won't be empty cell after cell with hours


I attached images how it looks, maybe it will help to understand
Blue Cells = data that I'm importing


Before Adding Row:



After Adding Row

Blue cell with hours changed position from C22 to C23 and also
project name from A25 to A26


Best Regards,


That makes it difficult. Tell the users not to add rows...