calculate row based on value in a cell

[robcarr]

hi,

further to my last post

this is the formula I have now to calculate the totals for the give date period.

IF(B4="","",SUMIF(rdates,B$4,'Main Data'!$C3:$IV3))

this currently get the data from row 3 on main data, row three maindata holds data for 1 of my campaigns, i have five campaigns, and instead of creating 5 separate sheets to show the data for each campaign, what i would like to do is to get the data based on a value in A4, A4 will contain the campaign name, which matches A2 in the main data for the campaign that the data is currently reporting on.

each campaign has 6 rows of data plus 1 header row, the header row contains campaign name and then dates

so it would look like this.
column A B c
campaign name Blank firstdate and so on
Forecast
Offered
Answered
SLA%
Abandon Rate %
calls within SLA
campaign name Blank firstdate and so on
Forecast
Offered
Answered
SLA%
Abandon Rate %
calls within SLA


what i am thinking needs to be done is to take this part of the formula

'Main Data'!$C3:$IV3

and make it find the campaign and the from this formula above start its calculation 2 columns over and 1 row down.



Hope this is of use, Rob.

 

[Rofeu]

Hi,

you can use something like

=INDIRECT("'Main Data'!$C"&MATCH(A4,'Main Data'!A:A,0)&":$IV""&MATCH(A4,'Main Data'!A:A,0),true)

Haven't tested, so might be some typos

Cheers,

Roel

 

[robcarr]

when I enter the formula it auto corrects to

=INDIRECT("'Main Data'!$C"&MATCH(A4,'Main Data'!A:A,0)&":$IV"""&MATCH(A4,'Main Data'!A:A,0),TRUE)

adds extra " before last match.

however it shows as #ref! i aint good at these formulas so not sure what it is doing


Hope this is of use, Rob.

 

[Rofeu]

Hi,

the formula returns a range, so it won't be able to show the result, you need to incorporate it in your formula like this:

=IF(B4="","",SUMIF(rdates,B$4,INDIRECT("'Main Data'!$C"&MATCH(A4,'Main Data'!A:A,0)&":$IV"&MATCH(A4,'Main Data'!A:A,0),TRUE)))

Cheers,

Roel

 

[robcarr]

hi I put it in and it finds 01/07 in the format of 39264, it does seem to find the same date for each campaign,



Hope this is of use, Rob.

 

[Rofeu]

Ok, maybe I'm not understanding what you're trying to do.

The part that added does the following:

the MATCH takes the value in A4 and looks it up in Column A on the sheet 'Main Data' and returns the rownumber it's on. This is then appended to the string that holds the address. i.e. Campaign1 is listed on row 5 in 'Main Data, it would return "'Main Data'!$C5:$IV5", the INDIRECT turns this string into an actual reference, which is then used in your SUMIF.

If this is not what you're looking for, please try to explain more precisely what you're after. Also, what will be in B4 and what is the named range rdates refer to?

Cheers,

Roel

 

[robcarr]

rdates refers to date range on main data basically row 2

the formula as original post find the data in row 3 that matches the date that appears in b4
i have 6 cells of data that look for various pieces of info

Forecast
Offered
Answered
SLA%
Abandon Rate %
calls within SLA

forecats for 1st campaing is on row 3 all the way down to row 8 for calls within sla,
what i was hoping to do was to enter the campaign name in A4 and then adjust the row that data is taken from based on the value in A4

so if it was coded it would be like

if a4.value = "campaign 1" then
rows(3:3).activate
else if a4.value = "Campaign 2" then
rows (10:10).activate
end if

basically the data i need to check starts with row 3 for cmapign 1 and then each campaign is 7 rows down from the first row.

this goes on for 6 campaigns.

Hope this is helpful, and I hope ihavent confused anymore, as i do tend to do this.

thanks for input so far.

rob.



Hope this is of use, Rob.

 

[SkipVought]

Rob,

Doing calculations on a disjointed report (non-tabular) MULTIPLES the level of difficulty.

Do you have ALL this information in a TABLE (Sheet)?

It's a WHOLE lot easier to generate a report from a table of data than to take an existing report and try to do handstands & cartwheels to manipulate the data.

Excel's lookup formulas and other data analysis functionality is designed to work on TABLES. You may need to rethink your design.

Skip,

_[red][/red]

 

[Rofeu]

Hi,

first of all, I have to agree with Skip.

Having said that, from your description of the data and the problem:

You have three input cells:
A25 = Date
A26 = Campaign
A27 = Dataset (i.e. Forecast)

use the following formula:

=OFFSET(A2,MATCH(B26,A3:A24,0)+MATCH(B27,A4:A9,0),MATCH(B25,B2:K2,0))

on the following data (as I understood from your description):

A B
rDates ----->
1
Forecast
Offered
Answered
SLA%
Abandon Rate %
calls within SLA


Cheers,

Roel