MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Array formula help


Posted by Danny on July 04, 2001 4:02 AM

Hi,

I have two sheets, one with data on in this format:-

Report Line Jan Feb Mar
Insurance
Salaries
Office Expenses
Insurance

and so on.

The other sheet contains a drop down box from which you choose a month
and this in turn is linked to a cell. I want a formula which will look
at the cell to the left (which is my report line) and sum the total of
any matches for all months. I've managed to get this to work for current
month but can't do it for YTD. This is the current month formula:-

=SUM(IF(RepLines='Summary (32C Act v Bud)'!A16,OFFSET(BudJan,0,$G$2-1),0))

Where Replines refers to the data sheet and BudJan refers to the first column
of data in the data sheet.

I know I've explained this badly but hopefully some clever person will help.

Cheers,
Danny.

PS Can email file if necessary.


Posted by Aladin Akyurek on July 04, 2001 8:21 AM

Danny,

I read your question over and over. I can't visualize the configuration of your data. Care to respecify the question in a more expanded form.

Aladin