Can I make a structured reference absolute in excel 07?

Question

I have a drop-down sidebar menu on opencart. How to make that menu not to close when I click on the category what I want? Here is the site. www.newopt.com.ua

Here is the code of category.php controller:

<?php
  class ControllerModuleCategory extends Controller {
private $categoryList;

protected function index($setting) {
    $this->language->load('module/category');

    $this->data['heading_title'] = $this->language->get('heading_title');

    if (isset($this->request->get['path'])) {
        $parts = explode('_', (string)$this->request->get['path']);
    } else {
        $parts = array();
    }

    if (isset($parts[0])) {
        $this->data['category_id'] = $parts[0];
    } else {
        $this->data['category_id'] = 0;
    }

    if (isset($parts[1])) {
        $this->data['child_id'] = $parts[1];
    } else {
        $this->data['child_id'] = 0;
    }

    if (isset($parts[2])) {
        $this->data['ch3_id'] = $parts[2];
    } else {
        $this->data['ch3_id'] = 0;
    }

    $this->load->model('catalog/category');

    $this->load->model('catalog/product');

    $this->data['categories'] = array();

    $categories = $this->model_catalog_category->getCategories(0);

    $this->categoryList = $this->model_catalog_category->getCategoriesAll();


    foreach ($categories as $category) {
        $children_data = array();

            $children = $this->childCategory($category['category_id']);

            foreach ($children as $child) {

                $level3 = $this->childCategory($child['category_id']);
                $l3_data = array();

                foreach ($level3 as $l3) {
                    $data = array(
                    'filter_category_id'  => $l3['category_id'],
                    'filter_sub_category' => true
                );



                    $level4 = $this->childCategory($l3['category_id']);
                    $l4_data = array();

                    foreach ($level4 as $l4) {
                        $data = array(
                        'filter_category_id'  => $l4['category_id'],
                        'filter_sub_category' => true
                    );

                        $l4_data[] = array(
                            'category_id' => $l4['category_id'],
                            'name'        => $l4['name'],
                            'href'        => $this->url->link('product/category', 'path=' . $category['category_id'] . '_' . $child['category_id']. '_' . $l3['category_id']. '_' . $l4['category_id'])
                        );

                    }

                    $l3_data[] = array(
                        'category_id' => $l3['category_id'],
                        'name'        => $l3['name'],
                        'href'        => $this->url->link('product/category', 'path=' . $category['category_id'] . '_' . $child['category_id']. '_' . $l3['category_id']),
                        'children4'    => $l4_data
                    );

                }
                $data1 = array(
                    'filter_category_id'  => $child['category_id'],
                    'filter_sub_category' => true
                );

                    $children_data[] = array(
                        'category_id' => $child['category_id'],
                        'name'  => $child['name'],
                        'href'  => $this->url->link('product/category', 'path=' . $category['category_id'] . '_' . $child['category_id']),
                        'children3'    => $l3_data
                    );

            }
            // Level 1
            $data2 = array(
                    'filter_category_id'  => $category['category_id'],
                    'filter_sub_category' => true
                );

                $this->data['categories'][] = array(
                'name'     => $category['name'],
                'children' => $children_data,
                'href'     => $this->url->link('product/category', 'path=' . $category['category_id']),
                'category_id' => $category['category_id']
            );

        }

    if (file_exists(DIR_TEMPLATE . $this->config->get('config_template') . '/template/module/category.tpl')) {
        $this->template = $this->config->get('config_template') . '/template/module/category.tpl';
    } else {
        $this->template = 'default/template/module/category.tpl';
    }

    $this->render();
}

protected function childCategory($cat_id) {
    $child = array();

    $numCategories = count($this->categoryList);

    $counter = 0;
    for ($i = 0; $i < $numCategories; $i++) {
        if($this->categoryList[$i]['parent_id'] == $cat_id){
            $child[$counter] = $this->categoryList[$i];
            $counter++;
        }
    }

    return $child;
}
}
?>

Solution 1

there is a difference between copying and dragging. Dragging is also called "filling" formulas. On Microsoft's support website you will find the following explanation:

Moving, copying, and filling structured references

All structured references remain the same when you copy or move a formula that uses a structured reference.

When you fill a formula, fully qualified structured references can adjust the column specifiers like a series as summarized in the following table.

So it basically means that the behavior you see is by design. If you want the reference to be absolute you should copy the formula and not drag/"fill" it.

Solution 2

You can make the reference absolute by duplicating the reference to column as if it were a range. Looks like the following:

A[[Foo]:[Foo]]

Put A[[Foo]:[Foo]] in your formula and drag across. The reference will remain on the [Foo] column. Note, you have to drag the formula. Copy/paste won't work.

This also works if you want an absolute reference on a cell in the same row of the same table. Assuming the formula is in the Table named "A", the following will anchor the cell in the same row of the formula.

A[@[Foo]:[Foo]]

There is an article and video on my site about this issue where I attempt to explain it clearly. :)

http://www.excelcampus.com/tips/absolute-formula-references-excel-structured-table/

Solution 3

I couldn't make the Control+drag idea work either. However, by selecting the original cell and the ones to be filled and using the Control+R, fill right, shortcut did seem to copy without changing the structured references across columns.

Solution 4

Try inserting the Table name with an INDIRECT function like;

INDIRECT("Table Name[Column Heading]")

In your Case, INDIRECT("A[Foo]")

Now you can drag it horizontally and the column reference stays static !!

Solution 5

It appears you cannot make a structured reference (.ie - A[Foo]) absolute. If you want to use absolute references in your formula, so you can use the fill handle, use the old method of absolute reference ($A$2 or $A2)

Example:

Table Name: DiscountPricing

    A       B           C                                   D
1   Item    Base Price  5%                                  10%


2   Pencil  $0.50       =[Base Price]-([Base Price]*        =[5%]-([5%]*
                        DiscountPricing[[#Headers],[5%]])   *DiscountPricing[[#Headers],[10%]])

3   Pen     $1          =$B3-($B3*                        =$B3-($B3*
                        *DiscountPricing[[#Headers],[5%]])  *DiscountPricing[[#Headers],[10%]])

See for additional info: http://www.technicalcommunicationcenter.com/2011/05/31/how-to-use-structured-references-in-ms-excel/

View more solutions

Comments

[Dane O'Connor]

Hmm, well I had seen that link. It doesn't work for me though. I can manually copy and paste but I can't drag the columns horizontally. Have you verified this your self? I just wanna make sure I'm not doing anything wrong.

[Dane O'Connor]

Thanks for the clarification. Unfortunately, unless I'm doing something wrong, even filling with CTRL adjusts the formulas like a series. Does it work for you? Looks like I'll just be copying.

[Dirk Vollmar]

This has nothing to do with pressing Ctrl or not. Just copy and paste the cell and you will the reference be the same.

[jdw]

This worked great for me. A formula of =COUNTIFS(Table1[Field1],TRUE,Table1[Field2],A1) adjusted the relative cell reference A1 properly (horizontally) and left the structured references as is, unadjusted.