View Single Post
 
Old 04-16-2018, 08:26 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 875
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Quote:
Originally Posted by IBRA2018 View Post
So when I changed it in the formula...so it can match the Column title it gave me error #NAME?
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.

Quote:
Originally Posted by IBRA2018 View Post
I created another sheet and gave it a name "1001" and I changed for formula to read from sheet "2017"
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.
Reply With Quote