SOLVED

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

Copper 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 (Copper 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.
1 best response

Accepted Solutions
best response confirmed by JanGreyling (Copper 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.

View solution in original post