Freymish✭

Try something like this: =IF(value = 0, REPLACE(TargetRow, 1, 1, ""), value) so subbing in you code: =IF([COUNTIFS(Header:Header, 0, Status:Status, "Yellow", [Current Phase?]:[Current Phase?], "1", Level:Level, "1") = 0, REPLACE([Column2]@row, 1, 1, ""), COUNTIFS(Header:Header, 0, Status:Status, "Yellow", [Current…

Not sure I understand why the number of respondents would affect the calculaton process. Maybe it would help to show how you are doing the calculation.

I did basically what you suggested. "You would still need to update the report definition each time a new sheet is created to include the new project sheet" I found a way around this, though it is a bit kludgy. I created a separate worksace for the project files and reference the workspace in the report. By doing that any…

我放在份增强一个请求d used the "Use a Workspace, and then the Report would include everything in the Workspace automatically" suggestion as my workaround. Thanks all!

Hi Tabitha, What you'll need to do is read the contents of the cell looking for the hyphen and then use the LEFT() or RIGHT() function to grab the part of the string you want. In the example below I am using the FIND() function to identify where in the the string the "-" is located. then I use the LEFT function to pull…

That will work fine. Is there a way to change the formatting on that field so it isn't just the same text all the way down? A nice to have of course.

I will try that. I didn't realize it did that. I've been so task oriented on this I haven't taken the time to read up on things like that. It's my "special talent" lol

I only have one sheet for all projects. I pull relevant information from that sheet into some others for reporting purposes so while the user could just go to the main sheet I would prefer that they be directed to the location in that sheet where that project's data is or maybe go to the main sheet with a filter for that…

OK. That worked. Thanks again!!

One last folow up to this. I'm getting #INVALID VALUE for the cells that don't have a project name. I tried the following but thet still show up: =IF(NOT(ISBLANK(INDEX(DISTINCT({Master Projects Range 2}), index@row))), INDEX(DISTINCT({Master Projects Range 2}), index@row)) Conversly, this yields the same result:…