How to create an Auto list with No Blank Cell -- HELP

IBRA2018 · #16 04-03-2018, 02:34 AM

Quote:

Originally Posted by NoSparks

a macro option...

Thanks I will look at the attached file and come back to you

IBRA2018 · #17 04-03-2018, 02:38 AM

Quote:

Originally Posted by ArviLaanemets

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.

Code:

=DATE(YEAR(TODAY()),(MONTH(TODAY())+1)-(ROW()-ROW(tCalendary[[#Headers],[Date]])-1),0)

, 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

Code:

AND(OR([@Closed]>=DATE(YEAR(ProjDate),1,1),[@Closed]=""),[@Created]<=DATE(YEAR(ProjDate),12,31))

(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!

Thanks again ....

1- I'm trying to go through the attached file and revise all the sheet
2- About the year 2017. This is an example only and the sheet can be named anything, but I create new sheet for every year

let me also go through and I will come back to you...

ArviLaanemets · #18 04-03-2018, 12:34 PM

Quote:

Originally Posted by IBRA2018

2- About the year 2017. This is an example only and the sheet can be named anything, but I create new sheet for every year

And create a report sheet for new year too (and maybe some other too, as you find that you need some additional reports)! And then after a year again new sheets etc.

Really you need a sheet ProjectSteps to enter most of info (sheet Projects is needed for data entry only when you register a new project - and when you close an ended one). Instead of designing a new report every year (Btw, the sheet Projects serves really also as a report), you design reports where you select year/date and maybe some other conditions, and a report for those conditions is displayed (a single sheet for 1 kind of report). And when the number of registered project steps grows too much and as result the workbook will slow down (it depends on your computer, but unless you have really crappy one some 20000 rows must work OK), you archive the workbook with different name, clear old data (or too old data), and continue with same workbook. You need to redesign the workbook only, when you want to add some new functionality to it.

An additional bonus for such design is, that it allows to handle projects, which take several years of work.

IBRA2018 · #19 04-04-2018, 11:43 PM

Quote:

Originally Posted by ArviLaanemets

And create a report sheet for new year too (and maybe some other too, as you find that you need some additional reports)! And then after a year again new sheets etc.

Really you need a sheet ProjectSteps to enter most of info (sheet Projects is needed for data entry only when you register a new project - and when you close an ended one). Instead of designing a new report every year (Btw, the sheet Projects serves really also as a report), you design reports where you select year/date and maybe some other conditions, and a report for those conditions is displayed (a single sheet for 1 kind of report). And when the number of registered project steps grows too much and as result the workbook will slow down (it depends on your computer, but unless you have really crappy one some 20000 rows must work OK), you archive the workbook with different name, clear old data (or too old data), and continue with same workbook. You need to redesign the workbook only, when you want to add some new functionality to it.

An additional bonus for such design is, that it allows to handle projects, which take several years of work.

Great .... I will follow your steps on this one ... and it seems to be useful. Eventhough; I'm totally lost with lots of sheets

Going back to the first worksheet you passed to me...

-------------------------------------------------------

I'm trying to use it first, but I got stuck in it ...
the Report sheet can't display more than four results.

What I did I added more feeds into the Sheet 2017, starting from Project No. 2017010009 but the results doesn't reflect as it should be for the rest which is under 2017010009 .

Files attached
2 images
1 xl file

NoSparks · #20 04-05-2018, 09:01 AM

You're not considering the macro solution ?

No extra columns
No extra sheets
No formulas

Just click a button.

ArviLaanemets · #21 04-06-2018, 12:14 AM

Quote:

Originally Posted by IBRA2018

What I did I added more feeds into the Sheet 2017, starting from Project No. 2017010009 but the results doesn't reflect as it should be for the rest which is under 2017010009 .

1. The formulas in 4 columns on sheet 2017 (H1...H4) meant to be hidden must have an additional check. P.e.

Code:

IF(OR(ROW()=EVEN(ROW());$G6<>"In Prog");0;COUNTIF($G$5:$G6;"In Prog"))

2. The names CntCompleted, CntInProg, CntNA and CntPending I created based on your example are non-dynamic currently. Change the last row number in formula from 21 to 29 (or redesign these names as dynamic ones.

3. In Report sheet, all formulas referred to ranges like '2017'!$A$6:$A$21 or '2017'!$L$6:$L$21. Change the last row number from 21 to 29 (or use dynamic ranges instead).

IBRA2018 · #22 04-09-2018, 10:33 PM

Quote:

Originally Posted by NoSparks

You're not considering the macro solution ?

No extra columns
No extra sheets
No formulas

Just click a button.

With do all respect I need to do some test on it so I can get used to it.

The work I'm doing will have lots of sheets and more cells.
SO I need to know how to edit the macro parts step by step

it looks great ... but I'm still a beginner and I need some times to deal with it and defiantly I will

IBRA2018 · #23 04-10-2018, 12:13 AM

Quote:

Originally Posted by ArviLaanemets

1. The formulas in 4 columns on sheet 2017 (H1...H4) meant to be hidden must have an additional check. P.e.

Code:

IF(OR(ROW()=EVEN(ROW());$G6<>"In Prog");0;COUNTIF($G$5:$G6;"In Prog"))

2. The names CntCompleted, CntInProg, CntNA and CntPending I created based on your example are non-dynamic currently. Change the last row number in formula from 21 to 29 (or redesign these names as dynamic ones.

3. In Report sheet, all formulas referred to ranges like '2017'!$A$6:$A$21 or '2017'!$L$6:$L$21. Change the last row number from 21 to 29 (or use dynamic ranges instead).

1- I tried to use this code but I got error ...

Sorry maybe I missed something
2- I have changed it to 45 because I added more rows

and it still the same thing ...

3- CntCompleted, CntInProg, CntNA and CntPending to make dynamic ??
Do you mean to make it as a drop-down list like the original words
- In Progress
- Pending
- Completed
- Not Assign

the file is attached ... Sorry for bothering you guys

IBRA2018 · #24 04-10-2018, 02:23 AM

Quote:

Originally Posted by NoSparks

You're not considering the macro solution ?

No extra columns
No extra sheets
No formulas

Just click a button.

How to edit the macro to display 2 sheets "2017" & "2018" results into the Report sheet
side by side results.

because in the future I will have more sheets and more columns added

plz find the attached file.

Now it displays "2017"

ArviLaanemets · #25 04-10-2018, 06:56 AM

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.

NoSparks · #26 04-10-2018, 07:11 AM

code adjusted...

IBRA2018 · #27 04-16-2018, 02:07 AM

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

IBRA2018 · #28 04-16-2018, 02:12 AM

Quote:

Originally Posted by NoSparks

code adjusted...

Thanks and I will try to do more tests and tell u about the results...

ArviLaanemets · #29 04-16-2018, 08:26 AM

Quote:

Originally Posted by IBRA2018

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

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.

IBRA2018 · #30 04-25-2018, 12:53 AM

Quote:

Originally Posted by ArviLaanemets

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Create Drop Down list to change cell color	bksmith	Excel	2	08-21-2017 02:37 AM
Have a cell filled black when blank in dropdown list	Aussie_81	Excel	14	04-06-2017 04:50 AM
Formulato say if cell is blank do this, if not blank do this.	mbesspiata	Excel	1	01-17-2015 05:02 AM
Auto add a list cell if the last list cell is filled	tasuooooo	Excel	1	07-31-2012 08:40 PM
Auto-populate an MS Word table cell with text from a diff cell?	dreamrthts	Word Tables	0	03-20-2009 01:49 PM