Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 03-14-2018, 02:34 AM
IBRA2018 IBRA2018 is offline Windows 8 Office 2016
Novice
 
Join Date: Mar 2018
Posts: 17
IBRA2018 is on a distinguished road
Unhappy How to create an Auto list with No Blank Cell -- HELP

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
File Type: gif 1.gif (84.0 KB, 39 views)
File Type: gif 2.gif (76.9 KB, 35 views)
Reply With Quote
  #2  
Old 03-14-2018, 05:31 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 275
ArviLaanemets is on a distinguished road
Default

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.
Reply With Quote
  #3  
Old 03-14-2018, 07:59 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 575
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

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.
Reply With Quote
  #4  
Old 03-26-2018, 09:33 PM
IBRA2018 IBRA2018 is offline Windows 8 Office 2016
Novice
 
Join Date: Mar 2018
Posts: 17
IBRA2018 is on a distinguished road
Unhappy

Quote:
Originally Posted by ArviLaanemets View Post
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.

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
File Type: xlsx Project_Status.xlsx (88.3 KB, 3 views)
Reply With Quote
  #5  
Old 03-26-2018, 11:15 PM
IBRA2018 IBRA2018 is offline Windows 8 Office 2016
Novice
 
Join Date: Mar 2018
Posts: 17
IBRA2018 is on a distinguished road
Default

Quote:
Originally Posted by NoSparks View Post
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.
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
Reply With Quote
  #6  
Old 03-26-2018, 11:45 PM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 275
ArviLaanemets is on a distinguished road
Default

Here you go!
Attached Files
File Type: xlsx Project_Status.xlsx (87.6 KB, 4 views)
Reply With Quote
  #7  
Old 03-28-2018, 12:15 AM
IBRA2018 IBRA2018 is offline Windows 8 Office 2016
Novice
 
Join Date: Mar 2018
Posts: 17
IBRA2018 is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
Here you go!

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?
Reply With Quote
  #8  
Old 03-28-2018, 12:41 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 275
ArviLaanemets is on a distinguished road
Default

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.
Reply With Quote
  #9  
Old 03-28-2018, 01:13 AM
IBRA2018 IBRA2018 is offline Windows 8 Office 2016
Novice
 
Join Date: Mar 2018
Posts: 17
IBRA2018 is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
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).

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


Quote:
Originally Posted by ArviLaanemets View Post
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.
I fixed that and applied at the way it should be
Reply With Quote
  #10  
Old 03-28-2018, 06:33 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 275
ArviLaanemets is on a distinguished road
Default

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
File Type: xlsx Project_Status.xlsx (100.1 KB, 5 views)
Reply With Quote
  #11  
Old 03-28-2018, 12:14 PM
trevorc trevorc is offline Windows 7 32bit Office 2013
Advanced Beginner
 
Join Date: Jan 2017
Posts: 43
trevorc is on a distinguished road
Default

Can you create a table for the data on sheet 2 and then filter out the blanks?
Reply With Quote
  #12  
Old 04-02-2018, 02:26 AM
IBRA2018 IBRA2018 is offline Windows 8 Office 2016
Novice
 
Join Date: Mar 2018
Posts: 17
IBRA2018 is on a distinguished road
Default

Quote:
Originally Posted by trevorc View Post
Can you create a table for the data on sheet 2 and then filter out the blanks?
Are you asking me or ArviLaanemets
Reply With Quote
  #13  
Old 04-02-2018, 02:31 AM
IBRA2018 IBRA2018 is offline Windows 8 Office 2016
Novice
 
Join Date: Mar 2018
Posts: 17
IBRA2018 is on a distinguished road
Unhappy

Quote:
Originally Posted by ArviLaanemets View Post
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.
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
File Type: xlsx TEST2.xlsx (11.0 KB, 2 views)
Reply With Quote
  #14  
Old 04-02-2018, 06:17 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 275
ArviLaanemets is on a distinguished road
Default

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!
Attached Files
File Type: xlsx Project_Status.xlsx (45.7 KB, 2 views)

Last edited by ArviLaanemets; 04-02-2018 at 11:58 PM.
Reply With Quote
  #15  
Old 04-02-2018, 08:45 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 575
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

a macro option...
Attached Files
File Type: xlsm Project_Status.xlsm (99.6 KB, 2 views)
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 06:36 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft