Nice,,,
I have tried to play arround with the file and add more sheets and also do some changes and I came to face these things:

1st:
I have tried to changed the the column title of H1 ...H2... H3 ... H4 to any name or to
F1 ...F2... F3 ... F4

So when I changed it in the formula:
from:
To:
so it can match the Column title it gave me error #NAME?

2nd
Instead of hiding Columns H1 ...H2... H3 ... H4 in sheet "2017"
I created another sheet and gave it a name "1001"
and I changed for formula to read from sheet "2017"
it worked fine and it can gets the correct reading from "2017"

NOW to get the the final result on Sheet "Report2" I must do some changing on this formula


I tried to make the changes on DataH1
but I get error




So I have these issues ... I feel I'm getting closer to what I'm looking for

Sheet "Reprot2" is reading from Sheet "1001"
the file is attached

Attached Files

Project_Status_updated.xlsx (98.8 KB, 9 views)

DataH1, ..., DataH4 aren't references to some cell, they are Names (look at Formulas > Name Manager). To rename a Name, you select the Name in Name Manager, click on Edit, and change the name of Name - the change is made automatically in all formulas where the Name is present.

Several problems with it.

1. Again, DataH1, ..., DataH3 are Names. They refer to ranges on worksheet, but they aren't on worksheet. You have to edit the formula returning the range for Name (Formulas > Name Manager, locate the Name, Edit, and edit <Refers to> field.
2. On sheet 1001 your table datarange starts from row 2. On Sheet 2017 the datarange starts from row 6. Without editing the formulas on sheet 1001 they refer to wrong rows on sheet 2017!
3. Having those columns in same table where data they refer to makes it easy to follow, that for every entry on sheet 2017 are calculated right step numbers - simply when you add new rows into sheet 2017, you copy formulas down to end of table. On separate sheet, you have to check yourself, has ith table 1001 right number of rows. Or you have to modify formulas in such way, that you have on sheet 1001 enough prepared formulas, which will display data for only those rows, where exist matching rows on sheet 2017. P.e. you fill on sheet 1001 10000 rows with formulas, which will check, is there Project.No on sheet 2017 for current row. When yes, then job step is calculated. When not, the empty string is returned.
4. When in future you decide to use Tables as I adviced earlier, then formulas in table are added automatically when you add new row(s) into table (this applies only, when the formula is same for whole column). So with tables you can hide columns with formulas, and forget about them, unless you screw the table up somehow (e.g. by inserting copied rows into table). When you use separate sheet, then forget about automatic in this case.

Hi again....

It is done. I have done some modifications and I edited as what I required and it works as I want it. I will now apply on large scale sheets ...

I think I'm there getting what I was looking for....
please see the attached file

Thaaaaaaaaaaaaaaaaaaanks

But plz do not close my topic I might come back to u if I face any problem

Attached Files

Project_Status_updated 25-4-2018.xlsx (89.2 KB, 10 views)