#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
Quote:
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 |
#5
|
|||
|
|||
Quote:
I'm trying my best to apply things form the internet ... That's why I need your help and guidelines |
#6
|
|||
|
|||
Here you go!
|
#7
|
|||
|
|||
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? |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
Quote:
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:
|
#10
|
|||
|
|||
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. |
#11
|
|||
|
|||
Can you create a table for the data on sheet 2 and then filter out the blanks?
|
#12
|
|||
|
|||
Are you asking me or ArviLaanemets
|
#13
|
|||
|
|||
Quote:
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" |
#14
|
|||
|
|||
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) 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)) NB! The attached file is old! Last edited by ArviLaanemets; 04-02-2018 at 11:58 PM. |
#15
|
|||
|
|||
a macro option...
|
|
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 |