Description:
Please refer to attached spread sheet
I have the original array formula, which is an array formula, inserted in Sheet1 B4, the formula draws data from sheet2 base on sheet1 column A
And as I copy down, two variables in the formula will be changed: the sheet1 column A reference going A4, A5,A6....
and close to the right end of the formula where it says "1:1" going "2:2", "3:3"....
It's not working properly because when ever the data in Sheet1 column A changes, the formula should be reset back to "1:!"
and I want to do this:
create a macro that inserts the formula in b4 and copy down, as long as the reference in Sheet1 Column A changes, the formula is reset back to "1:1" and copy down the formula, until next change, repeat.
Any help will be appreciated.
Also if it can be done without VBA but require extra column as middle steps would be fine.
Book1.xlsx