How do fix the formating of certain cells using a formula pulling from another tab?

Hello,
Trying to automate this template. I know what I want, just not sure how to finalize the last couple of items. The goal is for the production team member to enter all the data in 'consumer template' and it will feed into the datasheet & end wall tabs (which I will lock the sheets once finalized). Right now they manually write the data on the printed version and someone types it afterwards (waste of time). I am getting the production person a tablet so that they can do enter the data at the production line table as they work.

1. I'm wanting to use a formula in the 'end wall template' tab so that it only pulls the rows with the rcc part numbers (column c) that contain the the following part numbers that have "......I33, I34, & I35..." to show up in the 'end wall template' tab.
2. How do I get the cells to show 'blank' in the datasheet & end wall template? Right now there isn't any data in the 'customer template' for columns AE through AI but the 'datasheet' sheet is showing "0.00" until I enter data into those fields. Other cells are doing it as well such as 'column z'
FULL-SHIPSET-TEMPLATE-DRAFT.xlsx
Marcia MorrisProject ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
I assume you want to check the part number in column B for those where it includes:

#####-######-I33###
#####-######-I34###
#####-######-I35###

If so:

=IF(OR(MID(B9,14,3)="I33",MID(B9,14,3)="I34",MID(B9,14,3)="I35"),Formula,"Blank")

You can set the formatting on the sheet as a whole to not show zeros:

File > Options > Advanced - Scroll down to about halfway and find "Display Options for this Worksheet", untick the "Show a zero in cells that have zero value"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Marcia MorrisProject ManagerAuthor Commented:
Rob... thank you. I've removed the unticked the 'show....zero value'.

I'm getting a circular reference error when I use the IF statement. I used the formula in 'end wall template' (column B). How does it know that I want it look up the data in 'customer template' (column C)? Also is there a way for it to not leave blank rows so that person doesn't have to go in and delete them since that sheet will be locked?
FULL-SHIPSET-TEMPLATE-DRAFT.xlsx
Rob HensonFinance AnalystCommented:
I was expecting the formula to go somewhere other than column B and using column B as the reference.

The lookup to the Customer Template is the "Formula" section that I left out.
Marcia MorrisProject ManagerAuthor Commented:
Rob....I'm not sure how to write the vlookup formula to pull the information that I want. I've added your formula to the cell (even thought I know it's missing the vlookup portion).
FULL-SHIPSET-TEMPLATE-DRAFT.xlsx
Rob HensonFinance AnalystCommented:
Sorry, you've still put the formula in column B so creating a circular reference because it refers to itself.

As noted before, I thought you were looking at putting the formula elsewhere pulling information based on values in column B.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.