Advice on updating nested indirect functions with cell references

[hodgeshr]

I have been asked to update an existing worksheet (saved as an excel 2003 worksheet although I am working in excel 2010). The updates are very simple - I have been asked to add an additonal 5 columns. This first element of this is fine and I have managed to update the various drop downs within the 'data' sheet as these were based on tables within the spreadsheet. Unfortunately there is a linked worksheet for summarising the data (so that charts can be generated). Although I understand the logic of the formula included, I am having problems as I imagined that I could simply overwrite the existing column reference with the new one for example:

=SUM(IF((INDIRECT("Data!G2:G"&$A$1)=$B18)*(INDIRECT("Data!$T$2:$T"&$A$1)="Y")*(INDIRECT("Data!$X$2:$X"&$A$1)=H$5),1,0))

In my case:

'Data', column G relates to the gender (F/M/N)
'Data', column T relates to whether they have placed an order (Y/N)
'Data', column X relates to the number of orders, from 0 upwards

My summary table includes the following pieces of information in specified cells

$A$1 - the number of completed rows based on a CountA formula
$B18 - in this case is looking for all the Females (F)
H$5 - in this case is looking for where the number of orders = 3

The general principle of the formula is therefore saying if the value in column G (between 2 and the final completed row) is 'F', then multiply with the value in column X if this equals '3' and if the value in column T is 'Y'.

So following this logic, if I've added 5 columns to the left of the originals on the 'Data' sheet, then the column references on my summary sheet should also shift over 5 places giving:

=SUM(IF((INDIRECT("Data!L2:L"&$A$1)=$B18)*(INDIRECT("Data!$Y$2:$Y"&$A$1)="Y")*(INDIRECT("Data!$AC$2:$AC"&$A$1)=H$5),1,0))

I have literally overwritten the original values in the formula, but now it doesn't seem to work ... and yes I have added dummy data into the 'Data' spreadsheet to that it should have values.

Is there something really obvious that I am missing? I haven't previously used the indirect function

Thank you in advance for any advice you are able to offer.

Regards

Helen

 

[NARAYANK991]

Hi ,

The formula should work provided you enter it as an array formula , using CTRL SHIFT ENTER ; have you done this ?

Secondly , if you say you have inserted 5 columns to the left of column G , then even the reference to H$5 needs to be shifted 5 columns to the right , to become M$5 ?

Narayan

 

[hodgeshr]

It works! Thankyou so much.

In terms of the second part of your response. Sorry I did not make myself clear ... I've added 5 columns to the 'Data' sheet, but the 'Chart' sheet which is the one where I have been trying to update the formula is essentially a summary table which has gender as the columns (headings in column B) and number of orders as the rows (headings in row 5). The updating has not affected the relative positon of the table on the 'Chart' spreadsheet, only the cell references that it is looking at on the 'Data' sheet.

I'm not too sure why your suggestion of using CTRL, SHIFT, ENTER has worked, but it has and I really am incredibly grateful. Hopefully I can now crack on with updating the formulae and it'll be another job off my long list.

Thank you once again

Helen