Hi,,



I need your help guys..
I have created 2 Sheets one is to be filled manually and the second one is to be filled automatically as a result of what has been filled in Sheet-1


I have used some formula in Sheet-2 which works great to read from Sheet-1 , but it lists the Numbers or results as it appears in cells No. in Sheet-1 which leads to blank cells in between...

I have a attached 2 screen shot for the sheets

Sheet-1 (image 1) is the Master sheet
Sheet-2 (image 2) is the Result sheet where thinks is filled automatically (reads from Sheet-1)


in image 2 you can see the formula I used to read from Sheet-1



Thanks

Attached Images

	1.gif (84.0 KB, 46 views)
	2.gif (76.9 KB, 42 views)

On Sheet1, add 4 hidden columns, where you calculate the order number of project in given status. P.e.
1st hidden column (E) for projects In Prog has formulas returning values
"",1,"","","",2,"","","",3,"","","", 4,"","","",5
2nd hidden column (F) for projects Pending has formulas returning values
"","","",1,"","","",2,"","","",3,"","","",4,"" ,""
3rd hidden column (G) for projects Completed has formulas returning values
"",1,"","","",2,"","","",3,"","","", 4,"","","",5
4th hidden column (H] for projects Not Assigned has formulas returning values
"","","",1,"","","",2,"","","",3,"","","",4,"" ,""

Now you create 4 named values, p.e. CntInProg, CntPending, CntCompleted and CntNA, which calculate the MAX values in corresponding hidden columns.

On Sheet2, p.e. into cell A2 you enter the formula like
=IF(ROW()-ROW($A$1>CntInPrg,"",INDEX(Sheet1!$A$6:$A$23,MATCH (ROW()-ROW($A$1),Sheet1!$E$6:$E$23,0))), and copy down for as much rows as you need.

Continue same for other statuses with next 3 columns.

I don't know the purpose or ultimate goal for this but my first inclination would be to use a macro rather than formulas.
With the exception of A2 being blank, it appears you're wanting the columns of your second sheet to be what filtering for those columns in the first sheet would be.

Hi,

Thanks for your reply

I tried to apply your steps but couldn't get it right.

So Please I have uploaded the xl file for you to help me with it

Attached Files

Project_Status.xlsx (88.3 KB, 9 views)

Thanks, but I'm not that good in xl formulas/programming

I'm trying my best to apply things form the internet ... That's why I need your help and guidelines

Here you go!

Attached Files

Project_Status.xlsx (87.6 KB, 10 views)

Thanks

I just have one problem...

in the 3 named values, p.e. CntInProg, CntPending, CntCompleted which you gave me I have added one value for Not Assigned which is CntNot Assigned when I did both with space CntNot Assigned and without space
CntNotAssigned I get error in the result

#Name?

For "Not Assigned" (section-2) is defined a name CntNA.

Just now I did find out, that formulas on sheet Report were faulty - the first check for all columns was against CntInProg, but must be against different name (CntInProg/CntPending/CntCompleted/CntNA) for different columns.

Btw, to make this solution really working one, you have to define dynamic named ranges for all columns of table on sheet 2017 referred to in formulas. And this particular solution is working for this particular setup (Which is from my point of view a very strange one! Can't field work be never completed or Section-2 be In Progress? When different statuses can be assigned to any column, the report will be expanding and formulas will be more complex).

And why has every project in table 2 rows? To put a big "X" into some columns? Without this all calculations would be much simpler. Even more - you could define table on sheet (2017) as a Table, and take the advantage of usage of Table formulas.

Yes.. All Status can be applied to all sections ie..(Field Work, Section-1, Section-2)
and yes it expands according to the number of project in the year 2017 or 2018

I was hoping to find a way for that.

I tried to apply it in the real Worksheet and I noticed the problem


I fixed that and applied at the way it should be

I designed some additional sheets to demonstrate, how I would start with such task. Maybe you get some ideas.

In general - all data entry is done in tables. When you want to display it otherwise, you create a report. P.e. your sheet 2017 would be something like ReportProjectsYearly, where you select a year number, and all projects active in selected year are displayed. Having data entry in tables makes writing formulas much easier.

Attached Files

Project_Status.xlsx (100.1 KB, 11 views)

Can you create a table for the data on sheet 2 and then filter out the blanks?

Are you asking me or ArviLaanemets

I got totally confused ...

I was hoping that I can get something easy such as the attached file TEST

I found these equations which I feel they might work for me somehow if modified by experts....

If you fill up Column A even with leaving blank cell it reflects in order in Column C with No Blanks ...

Is there a way to modify my first request with this method in file attached "TEST"

Attached Files

TEST2.xlsx (11.0 KB, 8 views)

OK. Here in attachment I added a report which is closest I was able to create with different statuses on different steps. I'm skeptic how good it is to use, but the decision is yours!

Your original table didn't qualify, as all those empty rows made calculations too cumbersome.

Edit: The design allows to enter projects from more than 1 year. The posted version marks latest status of any step of project as current. At first I started to design the workbook so, that you can select a date, for which current status is calculated, even added a sheet Calendary (now hidden) and created a Named Range lDates to select dates from there, but realized it would take too much time, and dropped the idea. But you can continue with it:
1. Currently the calendary sheet displays dates from 01.01.2017 to today. Too much dates IMHO. Design it to display last dates of month instead, p.e.
, which displays last days of month from current month down. And the table tCalendary can be made for fixed number of months, or until certain month of certain year;
2. On sheet Projects, instead of todays date displayed, the field B2 would have data validation list with =lDates as source. When a date for ProjDate is selected, tProjectSteps[IsCurrent] is calculated according to selected date;
3. On Projects sheet, add a field Closed;
4. In all formulas for status on sheet Projects, instead SUMIFS(from tProjectSteps) use (SUMIFS(from tProjectSteps)*AND(from tProjects)), where AND(from tProjects) is like
(It is not tested, the idea is, that displayed is status only, when project is active in year, where the date ProjDate belongs).

NB! The attached file is old!

Attached Files

Project_Status.xlsx (45.7 KB, 8 views)

a macro option...

Attached Files

Project_Status.xlsm (99.6 KB, 8 views)