Excel - value from cell after adding row

New Contributor

Hello!

 

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,
Kamil

3 Replies

@Jurgus 

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:

87e8ac9f-4f21-4967-9e25-45b5e292f677.jpg

 

After Adding Row

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

9806ea83-6612-4cc2-964e-ccd7b302719f.jpg

Best Regards,
Kamil

@Jurgus 

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