SOLVED

How do I automatically fill columns based on a cell value from the same workbook.

%3CLINGO-SUB%20id%3D%22lingo-sub-2760293%22%20slang%3D%22en-US%22%3EHow%20do%20I%20automatically%20fill%20columns%20based%20on%20a%20cell%20value%20from%20the%20same%20workbook.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2760293%22%20slang%3D%22en-US%22%3E%3CP%3EWhen%20doing%20quotations%20I%20make%20use%20of%20standards%20(Std%20No.)%20where%20the%20different%20standards%20have%20their%20own%20BOMS%20(BOMS%20%3D%20Bill%20Of%20Materials)%20When%20I%20enter%20a%20standard%20number%20(Std%20No.)%20in%20cell%20F7%20(in%20this%20case%20Std%20No.%201787)%20it%20must%20then%20auto-populate%20column%20G%20from%20table%20T5%3AX20%20with%20all%20the%20values%20and%20blanks%20exactly%20as%20it%20is%20pre-populated%20in%20column%20V.%20Please%20see%20the%20attached%20example%20for%20more%20clarity.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2760293%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2760406%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20automatically%20fill%20columns%20based%20on%20a%20cell%20value%20from%20the%20same%20workbook.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2760406%22%20slang%3D%22en-US%22%3E%3CP%3EI%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1158991%22%20target%3D%22_blank%22%3E%40JanGreyling%3C%2FA%3E%26nbsp%3BIn%20G7%2C%20use%20this%20formula%20and%20copy%20it%20down%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DINDEX(%24T%248%3A%24X%2420%2CROW()-ROW(G%247)%2CMATCH(F%247%2C%24T%245%3A%24X%245%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIn%20case%20you%20ae%20an%20MS365%20subscriber%20it%20can%20become%20a%20bit%20easier.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2766156%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20automatically%20fill%20columns%20based%20on%20a%20cell%20value%20from%20the%20same%20workbook.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2766156%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1158991%22%20target%3D%22_blank%22%3E%40JanGreyling%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGood%20day%20Riny%2C%20thank%20you%20for%20the%20response.%3C%2FP%3E%3CP%3EI%20first%20copy%20and%20paste%20the%20formula%20but%20with%20no%20success.%20Then%20I%20typed%20the%20formula%20in%20also%20without%20any%20success.%20It%20does%20not%20auto-populate%20column%20G.%20In%20both%20cases%20when%20I%20look%20at%20the%20results%20in%20the%20formula%20(fx)%20it%20correctly%20displays%20the%20column%20result.%20Even%20if%20I%20change%20the%20std%20no.%20in%20cell%20F7%20the%20results%20are%20correct.%20Is%20there%20something%20I%20am%20doing%20wrong%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

When doing quotations I make use of standards (Std No.) where the different standards have their own BOMS (BOMS = Bill Of Materials) When I enter a standard number (Std No.) in cell F7 (in this case Std No. 1787) it must then auto-populate column G from table T5:X20 with all the values and blanks exactly as it is pre-populated in column V. Please see the attached example for more clarity.

4 Replies

I@JanGreyling In G7, use this formula and copy it down:

 

=INDEX($T$8:$X$20,ROW()-ROW(G$7),MATCH(F$7,$T$5:$X$5,0))

 

In case you ae an MS365 subscriber it can become a bit easier.  For instance:

=FILTER(T8:X20,T5:X5=F7)

@JanGreyling 

Good day Riny, thank you for the response.

I first copy and paste the formula but with no success. Then I typed the formula in also without any success. It does not auto-populate column G. In both cases when I look at the results in the formula (fx) it correctly displays the column result. Even if I change the std no. in cell F7 the results are correct. Is there something I am doing wrong?

@JanGreyling Well, I  can't tell what's wrong if you don't show what you've done. Anyhow, I have attached your file with the working formulae in it. The INDEX formulae in G8:G20 and the FILTER one in AA8.

best response confirmed by JanGreyling (New Contributor)
Solution
Riny, I realise my mistake. a Bit of language misinterpretation. I have simply copied the formula and paste it in cell G7 and not copying it down. Thank you for your help and the attached file.