Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

It doesn't make any sense. I can do it in Excel 2010 on Windows, but not Excel 2011 on OS X.

Workbook I used: http://cl.ly/Ewfe

Screenie: enter image description here

share|improve this question
    
I notice even if you adjust this in windows excel to what you want and open in the Mac version it insists the measurement must be between 0 and 5 even though it displays the larger period –  datatoo Mar 12 '12 at 21:18

1 Answer 1

up vote 1 down vote accepted

I made a bash-script doing this on OS X in case any of you would like to do this:

You use it like this:

backward_forecast workbook.xlsx forecast_number

Script:

#!/bin/bash
if [[ $# -lt 2 ]]
then
    echo "Usage: backward_forecast workbook.xlsx forecast_number"
    exit 1
fi
extension="${1##*.}"
if [[ "$extension" != xlsx ]]
then
    echo "Unknown extension: $extension"
    exit 2
fi

OLD_IFS="$IFS"
IFS=$'\n'
charts=( $(zipinfo -1 "$1" 2> /dev/null | grep '^xl/charts/.*\.xml') )
IFS="$OLD_IFS"

chart_files="${#charts[@]}"
case "$chart_files" in
    0)
        echo "No charts found in $1."
        exit 3
    ;;
    1)
        process_files=( "${charts[0]}" )
    ;;
    *)
        options=""
        i=0
        for c in "${charts[@]}"
        do
            i=$(($i+1))
            tmp="${c##*/}"
            options+="$i) ${tmp/.xml}"$'\n'
        done
        while true
        do
            echo "Multiple chart files found."
            echo "Please select the chart you want to change:"
            echo
            echo "$options"
            echo
            read -p "Chart number (* = all): " input
            if [[ "$input" == "*" ]]
            then
                process_files=( "${charts[@]}" )
                break
            elif [[ "$input" -gt 0 && "$input" -le "$chart_files" ]]
            then
                input=$(($input-1))
                process_files=( "${charts[$input]}" )
                break
            else
                clear
                echo "Bad selection number!"
                echo
            fi
        done
    ;;
esac

cd /tmp
mkdir -p xl/charts
for f in "${process_files[@]}"
do
    unzip -p "$1" "$f" | sed 's/<c:backward val="[^"]*"\/>/<c:backward val="'"$2"'"\/>/' > "$f"
    zip -q "$1" "$f"
    rm -f "$f"
done
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.