Formula/Link work-around

[Ray]

Hi -

I've got an issue related to a large datasheet that's being used to
create a great number of 'slice & dice' type reports. This datasheet
holds (among other things) daily sales data for 30 stores -- so
there's alot of rows. A data-integrity issue was recently discovered
with the raw data and a 're-class' was done to fix the problem. Due
to systems setups (and Accounting rules), this re-class was entered on
one day. I've been able to (fairly) accurately divide this re-class
amount among the 30 stores on a daily level, but am now having trouble
adjusting my formulas/links to accomodate the additional data. Here's
my data setup:
** Column V contains links to external workbooks
-- ex: ='K:\US\DailySales\2007 Daily Sales\[211 Daily
Sales.xls]to DSR'!$G$17
-- row numbers in datasheet aren't necessarily the same as in
target wb

** Column BE contains the the daily adjustments to be added to col V
amounts
-- some rows are blank, as they are future dates

So, the formula/links in consecutive rows SHOULD BE:
='K:\US\DailySales\2007 Daily Sales\[211 Daily Sales.xls]to DSR'!$G
$17 + BE10
='K:\US\DailySales\2007 Daily Sales\[211 Daily Sales.xls]to DSR'!$G
$18 + BE11
='K:\US\DailySales\2007 Daily Sales\[211 Daily Sales.xls]to DSR'!$G
$19 + BE12
and so on ....

This datasheet is built to be re-usable from year to year without
changing links, so the modification should be (hopefully) one which is
easily reversible for next year's version. My thought was to do a
find/replace on Column V (containing the links), replacing the '='
with an INDIRECT of some kind. BUT, I am totally at a loss on how to
use the INDIRECT function.

Can anyone help me with this?

TIA,
Ray

 

[Guest]

Hi,

Think of the indirect as a 3 dimensional reference with x as rows, y as
columns and z as sheetname

a typical formula is =indirect( "'" & z & "'!" & y & x)

On the target sheet make row 1 the inputs for y
make Column A the inputs for x

Depending how you want to arrangethe source infdormation on the target
sheet, make either row 2 or column B the inputs for z

For a given piece of info on the target sheet the formula should read e.g.
e5, where the different sheet info is in rows

indirect( "'" &$b5 & "'!" & $e$1 & $a5)

where b5 = source sheet name, DSR1, e1 = target sheet row eg 72 & A5 =
target sheet column eg J

This resolves to

indirect( 'DSR1'!J72)

assuming b6 = DSR2, copying this formula down to e6, assuming that b6 = DSR2
it will now resolve to

indirect( 'DSR2'!J72) so it collects the same information from another sheet

If all ofthe target sheets are organised differently then you need to stage
this by first arranging the information from each sheet into a standard
format where corresponding information is in the same place in each sheet
i.e. Marchs sales of widgets always in e5

Hi -

I've got an issue related to a large datasheet that's being used to
create a great number of 'slice & dice' type reports. This datasheet
holds (among other things) daily sales data for 30 stores -- so
there's alot of rows. A data-integrity issue was recently discovered
with the raw data and a 're-class' was done to fix the problem. Due
to systems setups (and Accounting rules), this re-class was entered on
one day. I've been able to (fairly) accurately divide this re-class
amount among the 30 stores on a daily level, but am now having trouble
adjusting my formulas/links to accomodate the additional data. Here's
my data setup:
** Column V contains links to external workbooks
-- ex: ='K:\US\DailySales\2007 Daily Sales\[211 Daily
Sales.xls]to DSR'!$G$17
-- row numbers in datasheet aren't necessarily the same as in
target wb

** Column BE contains the the daily adjustments to be added to col V
amounts
-- some rows are blank, as they are future dates

So, the formula/links in consecutive rows SHOULD BE:
='K:\US\DailySales\2007 Daily Sales\[211 Daily Sales.xls]to DSR'!$G
$17 + BE10
='K:\US\DailySales\2007 Daily Sales\[211 Daily Sales.xls]to DSR'!$G
$18 + BE11
='K:\US\DailySales\2007 Daily Sales\[211 Daily Sales.xls]to DSR'!$G
$19 + BE12
and so on ....

This datasheet is built to be re-usable from year to year without
changing links, so the modification should be (hopefully) one which is
easily reversible for next year's version. My thought was to do a
find/replace on Column V (containing the links), replacing the '='
with an INDIRECT of some kind. BUT, I am totally at a loss on how to
use the INDIRECT function.

Can anyone help me with this?

TIA,
Ray

 

[Guest]

I cocked this up a little
it should read

For a given piece of info on the target sheet the formula should read e.g.
formula in e5, where the different sheet info is in rows

indirect( "'" &$b5 & "'!" & $e$1 & $a5)

where b5 = source sheet name, DSR1, e1 = target sheet COLUMN eg J & A5 =
target sheet ROW eg 72

Hi,

Think of the indirect as a 3 dimensional reference with x as rows, y as
columns and z as sheetname

a typical formula is =indirect( "'" & z & "'!" & y & x)

On the target sheet make row 1 the inputs for y
make Column A the inputs for x

Depending how you want to arrangethe source infdormation on the target
sheet, make either row 2 or column B the inputs for z

For a given piece of info on the target sheet the formula should read e.g.
e5, where the different sheet info is in rows

indirect( "'" &$b5 & "'!" & $e$1 & $a5)

where b5 = source sheet name, DSR1, e1 = target sheet row eg 72 & A5 =
target sheet column eg J

This resolves to

indirect( 'DSR1'!J72)

assuming b6 = DSR2, copying this formula down to e6, assuming that b6 = DSR2
it will now resolve to

indirect( 'DSR2'!J72) so it collects the same information from another sheet

If all ofthe target sheets are organised differently then you need to stage
this by first arranging the information from each sheet into a standard
format where corresponding information is in the same place in each sheet
i.e. Marchs sales of widgets always in e5

Hi -

I've got an issue related to a large datasheet that's being used to
create a great number of 'slice & dice' type reports. This datasheet
holds (among other things) daily sales data for 30 stores -- so
there's alot of rows. A data-integrity issue was recently discovered
with the raw data and a 're-class' was done to fix the problem. Due
to systems setups (and Accounting rules), this re-class was entered on
one day. I've been able to (fairly) accurately divide this re-class
amount among the 30 stores on a daily level, but am now having trouble
adjusting my formulas/links to accomodate the additional data. Here's
my data setup:
** Column V contains links to external workbooks
-- ex: ='K:\US\DailySales\2007 Daily Sales\[211 Daily
Sales.xls]to DSR'!$G$17
-- row numbers in datasheet aren't necessarily the same as in
target wb

** Column BE contains the the daily adjustments to be added to col V
amounts
-- some rows are blank, as they are future dates

So, the formula/links in consecutive rows SHOULD BE:
='K:\US\DailySales\2007 Daily Sales\[211 Daily Sales.xls]to DSR'!$G
$17 + BE10
='K:\US\DailySales\2007 Daily Sales\[211 Daily Sales.xls]to DSR'!$G
$18 + BE11
='K:\US\DailySales\2007 Daily Sales\[211 Daily Sales.xls]to DSR'!$G
$19 + BE12
and so on ....

This datasheet is built to be re-usable from year to year without
changing links, so the modification should be (hopefully) one which is
easily reversible for next year's version. My thought was to do a
find/replace on Column V (containing the links), replacing the '='
with an INDIRECT of some kind. BUT, I am totally at a loss on how to
use the INDIRECT function.

Can anyone help me with this?

TIA,
Ray