http://forums.visokio.com/discussions/tagged/copying-rows/feed.rss Mon, 30 Oct 17 17:34:13 -0400 Tagged with copying-rows - Visokio Forums en-CA http://forums.visokio.com/discussion/2386/scripting-copying-values-from-above-if-nulls Wed, 23 Oct 2013 12:17:19 -0400 SimonWiggins 2386@/discussions
I've been trying to find a way to automate copying values down from above if the row is null.
The problem I have is that you can't execute 'for' or 'while' loops in functions in the field organiser (short of scripting)

For example in a column I have:
[Fieldname]
Data1
null
null
null
Data2
null
null
etc..

(where the nulls are empty cells)

I know I can use something of the form:

IF([Fieldname]!=null,[Fieldname],
[Fieldname]=null,IF(
Cell([Fieldname],CURRENTROW()-1)!=null,Cell([Fieldname],CURRENTROW()-1),
Cell([Fieldname],CURRENTROW()-2)!=null,Cell([Fieldname],CURRENTROW()-2),
Cell([Fieldname],CURRENTROW()-3)!=null,Cell([Fieldname],CURRENTROW()-3),
Cell([Fieldname],CURRENTROW()-4)!=null,Cell([Fieldname],CURRENTROW()-4),
Cell([Fieldname],CURRENTROW()-5)!=null,Cell([Fieldname],CURRENTROW()-5),
"Error field may contain more than 6 empty rows"),"Error")

but the limitations of this are if the latest data is pulled in and has more than 6 nulls before a value is above then it will print the error. Obviously also it creates an unnecessary extra field and the old one needs to be deleted in a second Field Organiser. Obviously I could copy paste and edit it up to 100 or more but this isn't very elegant.

I know I can use the Custom Script block to write a JavaScript function and as such have been learning javascript but am having a hard time applying it to my problem.

My question is:
Is there an easy/elegant way of doing this in formulas that I have overlooked?
Or is it a case of using JavaScript to solve my problems?
Is it better done in a 'script' fomula or in a 'Custom script' block bearing in mind I want to do it for 3 columns and keep the rest of the data in line?

I've attached a sort of example of what I mean of some basic data and how I would and have solved it in the past but not copied the formula down long enough therefore it errors after 6 nulls.

Hopefully this makes some sort of sense to someone! Thanks in advance for absolutely any help!

Simon]]>