Excel Formula Parameter

[PtotheC]

I have a spreadsheet with several tabs. All but one of the tabs collect the same data, but for different countries and then they have a column within for each day. This is just typed into the spreadsheet and is not in ODBC data set.

The last tab is simply a summary. So the columns are added together. We then just choose that columns for the days that represent the month.

The problem comes when adding in additional day (i.e. the data collected for today) as the summary sheet has to have the formula amended each time to encapsulate the current data for the month - then I change the last part of the formula on the summary to include today’s data.

What I would really like to do is add “parameters” to the summary formula and then have a [From Column] and [To Column] fields on the spreadsheet (these define the parameter) so that I only have to change the one filed or parameter (The [To Column] field) that will then update the formula in the summary.

Example of the data for one country (county 2, 3 etc each has their own tab):

Day 1, Day 2…,
Monday X Feb Tuesday X Feb

Data Fields, Percentage, Percentage,
Test 1 35% 28%
Test 2 38% 34%
Test 3 36% 32%

------------------------------------------------------------------------

Example of the summary:

Month,
February

Data Fields, SumOfPercentage for Country 1, SumOfPercentage for Country 2,

Test 1 [Sum of all the data currently collocated for Feb for Test 1]* [Sum of all the data currently collocated for Feb for Test 1]*
Test 2 [Sum of all the data currently collocated for Feb for Test 2]* [Sum of all the data currently collocated for Feb for Test 2]*
Test 3 [Sum of all the data currently collocated for Feb for Test 3]* [Sum of all the data currently collocated for Feb for Test 3]*

------------------------------------------------------------------------

*So as we add a column for the next day in the data, we do not want to amend the formula in the summary/percentage 3 times for each country, just in one place where the “parameter” is setup. This presumably would then make the summary look like:

Month,
February


Data Fields, SumOfPercentage for Country 1, SumOfPercentage for Country 2,

Test 1 [Sum of all the data currently collocated for Feb for Test 1]* [Sum of all the data currently collocated for Feb for Test 1]*
Test 2 [Sum of all the data currently collocated for Feb for Test 2]* [Sum of all the data currently collocated for Feb for Test 2]*
Test 3 [Sum of all the data currently collocated for Feb for Test 3]* [Sum of all the data currently collocated for Feb for Test 3]*

[From Column] AU O

[To Column] BH AB

------------------------------------------------------------------------

The “[Sum of all the data currently collocated for Feb for Test X]” would look at the parameters and know what column(s) to use. So I also assume that the formula may look like:

=SUM('Country 1'! [From Column]20: [To Column]20)

The 20 that follows the parameter would be the number of the Row.

What I have tried so far:
1) Just using a Pivot Table – This fails as the data is too big
2) Using the “Solver Add-In” – Does not have the functions I would need (I think?)
3) Many different formulas in different way and variations


I have attached some "test" data as an attachment.

 

[Brianwarnock]

No attachment

Brian

 

[PtotheC]

Give that a try now...

 

[Brianwarnock]

I feel that you should be able to do this using the Indirect function, but haven't worked out the synrax yet.

Brian

 

[Brianwarnock]

Yeah you are looking at writing formulas like
=SUM(INDIRECT("UK!"&$I$2&":"&($I$3)))

I think you can break it down further to address indirectly only the row or column.
eg
=SUM(INDIRECT("UK!"&$I$2&"2"&":"&$I$3&"2"))

Brian

 

[PtotheC]

Yeah you are looking at writing formulas like
=SUM(INDIRECT("UK!"&$I$2&":"&($I$3)))

I think you can break it down further to address indirectly only the row or column.
eg
=SUM(INDIRECT("UK!"&$I$2&"2"&":"&$I$3&"2"))

Brian

Thanks Brian, I will give that a try.