Forum Discussion
Combining IF(AND
I have attached a sample copy of the file. On the Pallet Label tab is where I am trying to use the formula. You will see my two test formulas for the two separately. Ultimately the combination of the two will appear in the B2:O2 when D5 on the New Office Copy is TRUE. The formula that is currently in that box will need to be what shows when D5 on the New Office Copy tab is FALSE.
I am open to other options if there is something that will work better. The current formulas were because on occasion there may be the same Variety name that is used in more than one Brand and it needs to pull in the correct item#/GTIN# and bagging info. I am really not very familiar with formulas, just good at copying/editing from other spreadsheets, internet lookup and trial and error. However this one I haven't been able to get to work because of the if False/True variable and where the IF/AND should go.
I am sorry, but that workbook is not one that I can afford the time to decode/decipher. There's so much there I'd want to change to take better advantage of Excel.
I'm sure that you are very clear in your own mind on what you're asking. But the reality is that it's clear to you because phrases like " there may be the same Variety name that is used in more than one Brand and it needs to pull in the correct item#/GTIN# and bagging info" are intelligible to you. You know what each word refers to in that phrase. I don't.
The "product list" tab bears some resemblance to a legitimate Excel Table, but the blank rows are artifacts of a human attempt to make it more readable to humans; in the process making it less amenable to computer (Excel) usage.
And your formula is using as input to the formula a sheet ("New Office Copy") that is itself an output document....
It's quite possible that somebody else here on the site will be able to help with the specific formula you're requesting ... but if I were in your shoes, I'd look into getting some solid training in Excel before attempting to actually revise this whole workbook. You might be able to get a formula to do what you're asking, but my worry, quite seriously, is that you'd just be perpetuating a fundamentally flawed design.
Obviously it is clear to me what I am talking about. That is the difficulty of any job that has anything to do with trouble shooting problems and providing solutions. I hope that there is someone out there that will want to spend a little time maybe asking a few questions to better understand what I am trying to do. If it was easy, I wouldn't be looking for help.
So here is some more explanation on what I am doing. On my last post when I say Variety, Brand, Item#, GTIN#, those are columns on the tab "Product list". Product list has the data that is pulled into the tab "New Office Copy" which has corresponding fields. The blank rows in the Product list are there for a reason. When we have new items that need to be added to the list, they need to go into the correct area by Brand. The space is there to insure the new line is inserted above the range that was created in the Name Manager. And yes, New Office Copy is an output document that is used by our warehouse for bagging orders, the whole purpose of this file. On the New Office Copy, you have to first choose the Brand from the drop down, then choose the Variety from the drop down. This will automatically populate the GTIN# & Item#. The formulas in these two fields are searching for Brand and Variety to find a match in the Product List and pull in the correct data. When the Qty of Bags are entered in C8, it will populate the Treatment info, again searching by Brand and Variety, which is what I am needing pulled into the "Pallet Label" tab. 98% of the time, the New office copy is going to have cell D5 unchecked (TRUE). I am using a basic IF(AND statement and have no problem getting the treatment pulled into the pallet label in this instance. However, I need to use a IF D5 TRUE/FALSE statement because when cell D5 is checked (FALSE), some treatments are not being re-applied so those fields are blank, but they still need to be pulled into the pallet label because they are still valid. This is why I am trying to figure out how to do an IF(AND statement if cell D5 is FALSE using the 2 formulas in my first post, which are slight variations to the formula on the New Office Copy tab for Apron XL & Nit Gold under the treatment info, searching for the Brand and Variety. So basically a TRUE (basic IF/AND) / FALSE with a IF/AND 2 item match. As I said before, if there is a better way to accomplish this piece I am open.
It may seem to be a fundamentally flawed design, and yes, maybe there is a better way to do this, but it has worked splendidly in another document which does not have a need for the Pallet Label tab. I agree, I would not want anyone to spend hours trying to recreate all the formulas. Everything else on this new document is working as needed. I just need some assistance to figure out this last piece and hope someone who is much more skilled in excel formulas than me, would like to take on the challenge.
