User Profile
fatalpotato_1389
Copper Contributor
Joined 7 months ago
User Widgets
Recent Discussions
HELP! Formula or Conditional Formatting?
Hi! I am coming across a road block and it seems like what I am trying to do is simple enough, but everything I have tried does not work. I am wondering if maybe it's because there is already a formula but I have hidden with =IFNA.. yada yada. I will provide screenshots so you can see what I am trying to do. The examples I provide are just that.. examples for the sake of this exercise. Example: Bit of Background: I sell booksets to bookstores across the US and Canada and I have 4 types of booksets. Book set 1 contains 2 books in a set; Vol. I & Vol. II. Book set 2 also contains 2 books in a set; Vol. III & Vol. IV. Book Set 3 contains the full set Vols. I-IV and finally Book Set 4 contains the same as Book Set 3 except the books are signed. What I am trying to do: A book store in Canada bought 250 units of book set 1. I am creating an "invoice" however, for customs purposes, I have to list what is included in the set and their descriptions (Cells A6-A9). Now, I have VLookup formulas in those cells, including cells A10-A12 as I have other bookstores that purchase Bookset #3 and Bookset #4 but I have them hidden w/ IFNA formula. I also have simple formulas in cells B6, B8, B10, & B12 (=B2) See screenshots below. How can I hide the quantities that are in B10 & B12 when A10 and A12 does not return a value? I have tried IF formulas, I have tried conditional formatting, and nothing I have tried worked so I am stumped. I know I can simply just highlight the cell and make it white but then I'd have to remember to undo that when I sell a book set that contains all 4 books and need the values that are in B10 & B12, if that makes any sense at all. More screenshots provided below Any help at all would be much appreciated! Thanks!790Views0likes3CommentsRe: Help with Formula
Jdsims Hi, it sounds like you need to do a VLOOKUP or even an XLOOKUP. I'm not too familiar with XLOOKUP so you might want to search tutorials on that but I can help with VLOOKUP. Let me know if this helps. The easiest way I learned until I got a hang of how to create the formulas is as follows: First, right click on the cell you want the information. There will be a bar that says "Search the Menus" and start typing VLOOKUP as below. You want to click on the first one and a table will appear like below Lookup_value would be, in your case, E2. Table_Array would be where the information is pulling from. If the information is on the 2nd workbook, you'll want to go into that 2nd workbook and highlight the entire table. For example, if you are using Columns A-D, highlight everything. Col_index_num: Here you will enter a numerical number. In your case, you want the information to generate the name of the company. You will need to figure out in which column the name of the company is. Think of column A as #1, column B as #2, etc... if the name of the company is in column B, in Col_index_num, enter 2. Range_lookup: if you want an exact match, enter FALSE. Once you have everything, press ok and it SHOULD give you what you need. If I did not explain this well enough, there are plenty of tutorials on youtube and I'm sure they'll be able to explain way better than I could! Hope this info helped!!264Views0likes0Comments
Recent Blog Articles
No content to show