Quote:
Originally Posted by ArviLaanemets
You left named ranges unedited. I defined all referred ranges as dynamic ones for all formulas, so now the formulas adjust automatically when you add data into sheet 2017.
Edit: About Dynamic Named Ranges/Dynamic Names.
A Name or Named Range is dynamic, when:
1. The value of Name or returned range of Named Range depends on range of data source, e.g. on number of rows in table (MaxRows, DataProj, etc. in attached file);
2. The value of Name or returned range of Named Range depends on position of active cell on worksheet when you define the Name/Named Range.
|
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:
Quote:
|
=IF(ROW()-ROW($A$1)>CntInProg,"",INDEX(DataProj,MATCH(ROW()-ROW($A$1),DataH1,0)-1))
|
To:
Quote:
|
=IF(ROW()-ROW($A$1)>CntInProg,"",INDEX(DataProj,MATCH(ROW()-ROW($A$1),DataF1,0)-1))
|
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"
Quote:
|
=IF(ROW()=EVEN(ROW()),0,COUNTIF('2017'!$G5:$G$6,"I n Prog"))
|
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
Quote:
|
=IF(ROW()-ROW($A$1)>CntInProg,"",INDEX(DataProj,MATCH(ROW()-ROW($A$1),DataH1,0)-1))
|
I tried to make the changes on
DataH1
Quote:
|
=IF(ROW()-ROW($A$1)>CntInProg,"",INDEX(DataProj,MATCH(ROW()-ROW($A$1),'1001'!DataH1,0)-1))
|
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