Google Spreadsheet Conditions in Arrayformula

Question:

( by Inspectigater )

Pardon my potential babbling... I might be trying to push google spreadsheets further than it's meant to be... :P

I'm trying to pull in a range of cells from another sheet (based off an if statement).  To do this I'm using the arrayformula function, by and large it's working great!  Here's an example of the formula I'm using:

=if(Sheet1!P3="Bacon",arrayformula(Sheet5!D2:K14))

This successfully pulls in the range of cells on sheet 5 (D2 through K14). However, I'm attempting to allow the J column to be modified by a value in the L column, so the J cell on sheet 5 looks like:

=if(L84<3,3,1*L84)

The idea is that when I pull in the array, a (quickly modifiable) value on the primary sheet (sheet1) will change how that cell acts. However, the problem I'm facing at the moment is while the cell entry on sheet5 is correct (=if(L84<3,3,1*L84)), the cell entry pulled in through the array on sheet1 is simply "3" (and is not modified by what is in the "L" column).

Is arrayformula the right function to be using here? Is there something I'm missing?

Any help is appreciated.

Thanks!

===========

Yeah, perhaps a working example is best.

Here, I've recreated the problem in a spreadsheet to focus on precisely what I wish to solve:

You'll notice two sets of data: Source Array and Pulled Data.

In black, is the arrayforumla function that is pulling the data from the source array data set.  The data has been pulled in.

Now, check out the red row titled Variable.  B5 is not "0" but rather is listed as "=1*C5" in the "Pulled Data" Array, the cell's value is listed as "0" instead of referencing the cell adjacent to it in order to pull in it's correct value (which in this case would be "15").
Does that make a little more sense?

Thanks for your help.

Solution:

If you want to move formulas and not values then don't use Arrayformulas in that way. Instead of that what you can do it select Range "B2:B5" press ctrl+C (copy it) and then go to cell A8 and press ctrl+V (paste it).

And if you have this repetitive task, and want to automate it, then you may try the following solution using ArrayFormula, or else you can have a solution using scripts. 

Have a look at the following screenshot:

I have the following formula in Cell A8:

=arrayformula(if({1,0};A2:A5;if(A2:A5="Variable";1*indirect("C"&row()&":C"&(row()+rows(B2:B5)-1));B2:B5)))

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive