Updating Array Formula

**warhead92100** · 01-01-2013, 09:38 AM

Hi Guys,

I have opened another thread for this as I have tagged the previous one as solved.

In the previous thread, a formula was given (Thanks to DILIPandey). It works fine, however, I was unable to tweak it as I needed to add some sheets to the formula.

Here's the old thread: http://www.excelforum.com/excel-form...le-sheets.html

Here's the formula I need to update:

=IFERROR(INDEX('S1'!$E$2:$XFD$2,0,MIN(IF(('S1'!$D$3:$D$6=Summary!E$2)*('S1'!$B$3:$B$6=Summary!$C3)*('S1'!$E$3:$G$6<>""),COLUMN('S1'!$E$3:$G$6),""))-4),0)

I would like to add S2 S3 and S4 versions of that code so that it will also look into those sheets.

Another thing, would it be possible to add a formula that will load a value for Ph1 - Ph11 from the index sheet to S1-S4 sheets? Also, I would like the formula to determine the date the value in C column has been added and return a value under that date and stop any consecutive fetching after a value has been added to a row. Please see sample.

Also, would anyone point me to a guide on excel formulas, especially look ups with specific conditions.

Thanks in advance, happy new year guys!

**warhead92100** · 01-02-2013, 04:02 AM

Here's the codes I need to merge,

=IFERROR(INDEX('S1'!$D$2:$XFD$2,0,MIN(IF(('S1'!$C$3:$C$20=Summary!E$2)*('S1'!$B$3:$B$20=Summary!$C3)*('S1'!$D$3:$Y$20<>""),COLUMN('S1'!$D$3:$Y$20),""))-4),0)

=IFERROR(INDEX('S2'!$D$2:$XFD$2,0,MIN(IF(('S2'!$C$3:$C$20=Summary!E$2)*('S2'!$B$3:$B$20=Summary!$C4)*('S2'!$D$3:$Y$20<>""),COLUMN('S2'!$D$3:$Y$20),""))-4),0)

any help is greatly appreciated.

Thanks.

**olasa** · 01-02-2013, 08:36 AM

Consider a database format - for flexibility.
See sample - not solution.
//Ola

Or try something like ='S1:S4'!$D$3:$D$10, with the risk of messy formulas.

**warhead92100** · 01-03-2013, 07:41 AM

Thanks Ola,

However, I got more confused with the pivot table.

Would you, by any chance know why this code says too many arguments?

=IF(INDEX('S1'!$D$2:$XFD$2,0,MIN(IF(('S1'!$C$3:$C$20=Summary!F$2)*('S1'!$B$3:$B$20=Summary!$C3)*('S1'!$D$3:$Y$20<>""),COLUMN('S1'!$D$3:$Y$20),""))-4),(INDEX('S2'!$D$2:$XFD$2,0,MIN(IF(('S2'!$C$3:$C$20=Summary!F$2)*('S2'!$B$3:$B$20=Summary!$C3)*('S2'!$D$3:$Y$20<>""),COLUMN('S2'!$D$3:$Y$20),""))-4),(INDEX('S3'!$D$2:$XFD$2,0,MIN(IF(('S3'!$C$3:$C$20=Summary!F$2)*('S3'!$B$3:$B$20=Summary!$C3)*('S3'!$D$3:$Y$20<>""),COLUMN('S3'!$D$3:$Y$20),""))-4),(INDEX('S4'!$D$2:$XFD$2,0,MIN(IF(('S4'!$C$3:$C$20=Summary!F$2)*('S4'!$B$3:$B$20=Summary!$C3)*('S3'!$D$3:$Y$20<>""),COLUMN('S4'!$D$3:$Y$20),""))-4))))

Also whenever I use ='S1:S4'!$D$3:$D$10, it becomes 'S1:[S4]S4'!

=IFERROR(INDEX('S1:S4'!$D$2:$XFD$2,0,MIN(IF(('S1:[S4]S4'!$C$3:$C$20=Summary!E$2)*('S1:[S4]S4'!$B$3:$B$20=Summary!$C6)*('S1:[S4]S4'!$D$3:$Y$20<>""),COLUMN('S1:S4'!$D$3:$Y$20),""))-4),0)

Thanks for the help!

**warhead92100** · 01-03-2013, 09:02 PM

I have figured out how to merge all the formulas, however it is now returning the wrong values, the column part is not coping up, I have 22+ columns and I want to have the values of corresponding column header.

Can anyone help?