Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula/Link work-around
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula/Link work-around
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 "Ray" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula/Link work-around
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 "DazzaData" wrote: 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 "Ray" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel link to work can't get full worksheet | Excel Discussion (Misc queries) | |||
How to link a url to the work sheet | Setting up and Configuration of Excel | |||
Message Input with link to different work sheet | New Users to Excel | |||
how do you link rows of cells to other work pages? | Excel Worksheet Functions | |||
Paste link does not work properly | Excel Worksheet Functions |