Macro to filldown information

[saru5133]

Hello,

PFE file where it explains on the requirement.

I have some information in sheet1 & 2. Macro code should help to fill the information of B2:B5 like B9:B16 picking up values from sheet2.
It should be any information entered in sheet1 should pick the values from sheet2 and list down with 4 combinations.

[sdckapr]

I don't see the need for a macor. Here is a simple formula solution.

Put this in B9 and autofill it to B24:
=INDEX($B$2:$B$5,INT((ROW()-ROW($B$9))/4)+1)&" - "&INDEX(Sheet2!$D$2:$D$5,MOD(ROW()-ROW($B$9),4)+1)

Steve

[saru5133]

Thanks sdc.
As mentioned above my requirement is B9. But in the formula there is a B9 cell reference, based on which the output is combined from sheet 1 & 2.

[HansV]

The reference to B9 in Steve's formula is not to the value of B9 but to the row number of B9. This is used to calculate which of the cells from Sheet2 should be used.

If Steve's formula does not do what you want, please try to explain in as much detail as possible what is wrong and what it should do instead.

[saru5133]

OK.
When iinformation is filled in column A/B of sheet1, it should provide the result as mentioned in B9 instead of combining B9. (but i see the formula constructed above is a combination of B2:B5 & B9, Sheet2, please correct if i am wrong).
So, if column A contains "1234 - Message" , output should be "1234 - Message - A","1234 - Message - B","1234 - Message - C" and "1234 - Message - D". This means it shoud add up rows.

Hope i am clear this time

[HansV]

As I wrote, Steve's formula only uses B9's row number. If you enter the formula in B9 and fill it down, you'll see that it uses the text of B2:B5.
If you then change the text in cell B2, you'll see the result of the formula in B9:B12 change correspondingly, and if you change the text in cell B3, you'll see the result of the formula in B13:B16 change with it, etc.

[saru5133]

OK, got it...i made a mistake in CTRL C+V .

Steve's formula is working absolutly fine...Thanks for making me understand.

Also,I tried it is working in this way as well
=INDEX($B$2:$B$5,((ROW()-ROW($C$2))/4)+1)&" - "&INDEX(Sheet2!$D$2:$D$5,MOD(ROW()-ROW($C$2),4)+1).

What if i want to increase the range / values in sheet2?

[HansV]

You could define a dynamic named range MyList in the Name Manager on the Formulas tab that refers to

=OFFSET(Sheet2!$D$2,0,0,COUNTA(Sheet2!$D:$D),1)

This will expand and contract as you add or remove items to the list on Sheet2. You can also create a name MyCount that refers to

=COUNTA(Sheet2!$D:$D)

You can then change the formula in B9 to

=INDEX($B$2:$B$5,INT((ROW()-ROW($B$9))/MyCount)+1)&" - "&INDEX(MyList,MOD(ROW()-ROW($B$9),MyCount)+1)

and fill down. If you change, add or remove items in column D on Sheet2, the formula results on Sheet1 will be updated automatically.

See the attached version.

Book1.xlsx