[Solved] Excel rows issue formulas in project/job tracker database

[Jonquil]

Hello
I have an excel database that is used to job/project track where the formula stop at certain row number. Seems simple to edit formula row number. Well, I'm not that versed in excel, been using it for years, & when it works it is such a great tool, but I've exhausted my knowledge. And searched online for help to no avail. I KNOW it is probably simple and i'm stressed overthinking it.

We have started helping people from Helene and Milton and really need this tool.

In filling out online form, i signed a non disclosure agreement. And also the other required agreement.

Thank you!

 

[shamimarita]

Hello Jonquil,

It sounds like you're facing a formula issue that stops at a certain row, and it's great that you're trying to resolve it. However, with so many sheets in the file, it’s difficult to pinpoint the problem without more details.
Could you describe which sheet and row you're having issues with, and perhaps include a screenshot or image to clarify? This will help us assist you more effectively.

 

[Jonquil]

thank you for responding! the attached database has been edited with just the master page and one sheet.
"MISC"
whenever 'misc' is typed into the 'name' column in the master the information should then appear in the sheet named "misc".

there are 14 additional inputs for MISC in the master sheet past 9/4 that are not transferring to the sheet 'misc'.

thank you!

 

[shamimarita]

Hello Jonquil,

Thanks for your explanation. Your formula is correct but the issue is in the absolute range you mentioned in the formula.

Your formula:
=IFERROR(INDEX(Master!$B$4:$L$279,SMALL(IF(Master!$C$4:$C$279=MID(CELL("filename",O1),FIND("]",CELL("filename",O1))+1,256),ROW(INDIRECT("$1:$"&COUNTA(Master!$B$4:$B$279)))),ROW(1:1)),COLUMN()),"")

You have fixed the range to look up a value from row 4 to row 279, whereas your master sheet has more rows. That's why your formula fetches values to the misc sheet until 279 rows not more than that.

As it's your master sheet, you need to mention a larger range, I mentioned 500 rows. Remember to update the ranges when you add more rows to the master sheet.

Updated Formula: =IFERROR(INDEX(Master!$B$4:$L$500,SMALL(IF(Master!$C$4:$C$500=MID(CELL("filename",O1),FIND("]",CELL("filename",O1))+1,256),ROW(INDIRECT("$1:$"&COUNTA(Master!$B$4:$B$500)))),ROW(1:1)),COLUMN()),"")

 

[Jonquil]

thank you!!! i will take a look!!

oh my gosh thank you! this is so much help!

 

[shamimarita]

You're most welcome! I'm glad I could help. Let me know if you have any more questions or need further assistance! Let's keep helping each other to build a great Excel community!