question

MarcusRowe-2816 avatar image
0 Votes"
MarcusRowe-2816 asked Lz-3068 commented

Excel Formula for Autopopulating list

Hi Everyone

Looking for some advice, I'm currently making a checklist that tracks a project that my team is assigned, and I'm wanting to make it all as hands-off as possible.

I've managed to get far enough to have items cunt due dates or go red when late etc, however, the final stages are a little complicated.

I'm stuck trying to get the following things to works:

Row A in Book1 has a list of languages that may be needed, with the lead putting a Y or N in the B row if needed.
In order to track this progress, I want to build a table in Sheet 2, where Row A contains only the items marked as "Y" and does not have any blank spaces.

I've used "=IF(Sheet1!B3:B23="Y",Sheet1!A3:A23,"")" but this leaves blanks in the table is there a more clean way to do this, that's beginnerish(friendly).

Additionally, for items on sheet two based that arent languages, if these are N certain items in the checklist won't be needed, I've greyed these out with conditional formatting, but is there any way to either
A: Hide the row and have them not but part of any counts
B: Build on the following formula in the complete tab "=IF(ISBLANK($D28)=FALSE, "Y", "N")" to add N/A to in the case any items are marked as N on Sheet2

office-excel-itpro
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered

Hi @MarcusRowe-2816

Understand the top confidential aspect of things. On the other hand, how much time does it take to put in place a sample - with dummy data - that reflects your setup? Ex:

PIC

Asking the question as the above one might not reflect yours so risk is high that you and forum contributors waste a lot of time before finding a suitable solution

With the above setup, once I have marked the Irrelevant Tasks, I filter filter out the "Yes" => rows are hidden. Finally I hide the [Irrelevant] column:

PIC2

Then, in my Summary I easily derive the counts, ignoring the Irrelevant Tasks (so whatever there Status is, inc. Blank):

PIC3

Corresponding sample avail. here. If you run Excel 365/Web that's very straightforward (tab 'Summary 365')



demo.png (51.0 KiB)
relevanttasks.png (49.6 KiB)
projectsummary.png (16.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
1 Vote"
Lz-3068 answered

Hi @MarcusRowe-2816

Re. #1.For the languages and Y/N I used a Table named tblLanguages - not mandatory, you can use a range instead

PIC


On sheet2, with Excel 365/Online, in A3:

 =FILTER(tblLanguages[Language], tblLanguages[Required] = "y")

On sheet2, with Excel >/= 2010, in C3 (and copy down):

 =IF(ROWS($10:10) > COUNTIF(tblLanguages[Required], "y"), "",
     INDEX(tblLanguages[Language],
         AGGREGATE(15, 6, ROW(tblLanguages[Required])-ROW(tblLanguages[#Headers]) / (tblLanguages[Required] = "y"), ROWS($10:10))
     )
 )

Corresponding sample avail. here

Re. #2 Additionally, for items on sheet two based that arent languages...
I'm afraid I don't see/understand, I'm especially confused with sheet1/sheet2, rows vs. columns but not only. Could you try again and upload a picture of what you have and what you expect - this usually clarify thing?



demo.png (44.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

erinding-msft avatar image
1 Vote"
erinding-msft answered

Hi @MarcusRowe-2816

Please check Lz-3068's reply.

Based on his sample in Sheet 1, you could also try this formula =INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$B$3:$B$8="Y",ROW($3:$8),5^5),ROW(A1)))&"" in cell A3 in Sheet 2, press Ctrl+Shift+Enter, then drag it down to cell A8. Both Excel 365 and Excel >/= 2010 could use it.

Any updates, please let us know.


If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MarcusRowe-2816 avatar image
0 Votes"
MarcusRowe-2816 answered

@Lz-3068 @erinding-msft

Thank you both. I have the language thing all set and working now. For the second question, sorry I can't be super specific as some of the information is confidential however implied what I am trying to do is

On another sheet, I have a list of phases for each project, Phase A, B, and C.
Each phase requires certain tasks, and I'm trying to tack these.

I have set it up so each task will have a due date, count if it is late(and change red), change Green when checked done, etc.

On the winal sheet is a summary page which counts the tasks, basically at Phase A, B, and C how many tasks are marked as complete. (and the total of tasks).

The sheet with languages lists the types of projects we deal with, with a yes or no (it may meet multiple types). Currently, the checklists sheet lists all possible tasks, and my idea was just to mark the ones irrelevant to the project as completed manually and then autoformat them to grey. However, I asked by my boss if its possible to not count/hide the tasks.

So in a really whacky example
on the Spec sheet - Project uses frogs is marked as No.

In this case, the Phase one task "prepare pond", and Phase 2 "buy tadpoles," arent needed.

If its possible, I would like to have the rows with these tasks hidden and also have the complete status marked as N/A. I'm currently using the following formula to mark tasks as complete "=IF(ISBLANK($D7)=FALSE, "Y", "N") "with D being the finished date. So I guess I need to add a secondary condition here, if Frogs is N, then NA if not then this formula.

However Im a little lost on how to do that

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MarcusRowe-2816 avatar image
0 Votes"
MarcusRowe-2816 answered Lz-3068 commented

@Lz-3068

Thank you again, sorry I really should have just thrown made-up data together as you suggested, I honestly hit the point I was so confused about what I was doing that my brain had stopped working.

While my explanation was rather average your solutions have been of great help and I have got everything on this running now, thanks again

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@MarcusRowe-2816

No worries at all and glad to hear you now have something in place
At the bottom of the replies you got there's an Accept as answer button...
Thanks in advance

0 Votes 0 ·