Changable Cell reference

  • Thread starter Thread starter Nigel Graham
  • Start date Start date
N

Nigel Graham

I have a spreadsheet which brings together thousands of
bits of information to produce data in a format management
want on a monthly basis.
There are over 6,900 formulas which lookup data from
different sheets. What I would be able to to is to change
the data source more easily.
e.g. Cell C5 has this formula =SUMPRODUCT(('FY3'!
$A$1:$A$450=C$573)*('FY3'!$C$1:$C$450=$A$1),'FY3'!
$D$1:$D$450)
this looks up Cell C573 for the date (Month) then cell A1
to see which store we are examining (there are 32 stores)
it then goes on to sum the values matching C573 and A1 in
the cells D1 to D450. What I would like to do is for the
part of the formula 'FY3'!$D$1:$D$450 could be linked to
another cell which could be changed by a dropdown to the
right somewhere. Doing this would mean, once set-up I
could change the cells to be summed to another category
without having to have a separate block of calculations
for every category. Effectively I am looking for
the 'FY3'!$D$1:$D$450 part to be replaced dynamically with
say 'FY3'!$G$1:$G$450.
Although the spreadsheet works fine now it is a little
difficult when management want to focus on different
products or performance information.
If this is too difficult to explain here feel free to mail
me an example directly.
Thank you for your help in advance.
 
Hi
you may use INDIRECT or a combination of MATCH/OFFSET for
this. This depens on what you want to display in your drop
down box.

BUT you may consider using a pivot table instead as this
would create a drill-down report (also depending on your
existing data structure)

you may provide some more information how your source data
is structured
 
The data is drawn from 3 sheets FY1 FY2 FY3 all have
identical data structure but include data for each
Financial Year. FY3 is updated monthly to include the
latest months figures. Column C contains April D = May
etc then Row5 this Financial Year Row 6 last FY and Row 6
FY prior to that. This allows a buildup of data over the
year and includs Trending functions so the remaining
months can be predicted using the previous 2 years and
what data is available this FY.
The Data In sheets FY1-3 have a total of 200 columns which
contain A=Month B=Region C=Store D=Total Sales E=Returns
F=Prduct1 G=ReturnsProduct1 H=RepairsP1 I=RefundsP1 etc....
Each Header is in plain english to describe it. What I
was looking for was a Valid list to select the Column
needed in each Row on the data collation sheet.
I wanted to use the valid list to show the part of the
function to reflect which column to sum so if I wanted to
sum the total sales and show a % of Repairs I could
select 'Total Sales' from a dropdown on Row 5
and 'RepairsP1' on row 5 so Row 6 would show the Percent
of returns for that product in all Columns C to N and Rows
5-6 8-9 and 11-12 by only changing two dropdowns.
Complex or what? This may take some setting up but would
be easier when changing management whims or even the
structure of the Yearly sheets would not maen recoding the
collection sheet as the new headers could be added to the
valid list with their relevant Column letters.
I have looked at Pivot Tables but it does not allow for
alterations in the structure and would case changes in
structure meaning a complete re-write of the printable
part of the spreadsheet as the rows and columns would
change with input of different data structures. This way
the changes are catered for dynamicly.
 
Hi
this is possible using a combination of MATCH and OFFSET
as range within your SUMPRODUCT formula

If you like you could email me your file and I'll have a
look at it this evening and set-up an example for you.
email: frank[dot]kabel[at]mummert[dot]de
 
Back
Top