(My personal favourite is the uptick ` as I don't use it otherwise). you'll need to ensure that any further additions refer the same formula so either save it elsewhere on the sheet as plaintext - you can do this by adding any character before the = B and C would have to have an Index-Match formula in, with a iferror outside it to keep it clean - Like this =IFERROR(INDEX($I$2:$I$4,MATCH($A2,$H$2:H$4,0)),"")Īn extra point: if size is really a huge issue, I'd suggest either looking into PowerPivot and learning some of that (try Excelisfun on youtube for a quick start - assuming it's relevant, it may even save you creating this spread sheet at all).Īlternatively, and assuming you don't need the details to change dynamically, simply copy paste the relevant completed fields as values - this will remove the formulas. Instead, as a non-vba example, I'd suggest you use the product key column (A) to fill columns B and C. Non-vba wise, however, you can't do everything you've asked for here. I tried to get help for doing this without programming here, without success so far. I can't do macros neither VBA, so if you could be so kind and put up the whole code, please.
From using macro/VBA I hope for a small file size and clean look. The issue is a massive file size and a display of #N/A in empty rows. The cell range also needs to include the return value you want to find. The first column in the cell range must contain the lookupvalue. You can use a named range or a table, and you can use names in the argument instead of cell references. Important: there will be 1000's of rows added, I just made a short description above.Ĭurrently I'm using a CSE formula for column B The range of cells in which the VLOOKUP will search for the lookupvalue and the return value.
I already use Defined names and Data validation for columns A,B and C which works for me as a choice list and also to control column B and C for allowed entries only, so their content is not mis-typed and has the strict format needed.
For empty cells in column A there should be nothing in cells of column B and C, not even #N/A displayed. And all this should work without me using a formula in columns B and C. This automatically filled content in cell of column C and B depends on what is the content of cell in column A as can be seen on the attached image("help-table"). (using Vlookup possibly)Īs the user proceeds adding new records in new rows, the appropriate cell of columns (B and C) should "fill itself" automatically as soon as there's an entry in the same row in column A.
#How to use vlookup in excel 2010 youtube how to
My question is, How to achieve with VBA (or macro): Manual entry in cells of column A should trigger automatically filled content in cells of columns B and C based on the content of A and based on the dependencies described in the help-Table.
#How to use vlookup in excel 2010 youtube code
The help-table on the attached image shows that a certain product code = what product name of what size, as a guide. Further description of the issue: certain Product code = certain Product name of certain Size.