Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
references
I have a list of dates (consecutive weekdays - some holidays) in Column D in
Sheet2. In sheet1, I have a reference to those days. For example in A3 I have the formula =Sheet2!D3 which displays the correct date. I'd like to copy the formula to every 7th cell in Column A in Sheet1. What's happening now is that the copied formula increments by 7 The formula copied into the cell A10 is "=Sheet2!D10". How can I force it to increment only by 1, so that it references the dates in Sheet2 correctly? Thanks Bert |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
references
Put this in A3 of Sheet1:
=INDIRECT("Sheet2!D"&INT((ROW(A3)+4)/7)+2) and then copy it to A10, A17, A24 etc. Another way would be to enter your original formula and copy down contiguous cells, then insert 6 new rows between each of the rows with formulae in. Hope this helps. Pete On Mar 15, 12:39*am, "Bert" wrote: I have a list of dates (consecutive weekdays - some holidays) in Column D in Sheet2. *In sheet1, I have a reference to those days. *For example in A3 I have the formula =Sheet2!D3 which displays the correct date. *I'd like to copy the formula to every 7th cell in Column A in Sheet1. *What's happening now is that the copied formula increments by 7 *The formula copied into the cell A10 is "=Sheet2!D10". *How can I force it to increment only by 1, so that it references the dates in Sheet2 correctly? Thanks Bert |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
references
Fri, 14 Mar 2008 20:39:06 -0400 from Bert :
I have a list of dates (consecutive weekdays - some holidays) in Column D in Sheet2. In sheet1, I have a reference to those days. For example in A3 I have the formula =Sheet2!D3 which displays the correct date. I'd like to copy the formula to every 7th cell in Column A in Sheet1. What's happening now is that the copied formula increments by 7 The formula copied into the cell A10 is "=Sheet2!D10". How can I force it to increment only by 1, so that it references the dates in Sheet2 correctly? I don't know if this is the best way, but INDIRECT will get the job done. =indirect("Sheet2!D" & int(3+(row()-3)/7)) -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com Shikata ga nai... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel references within references? | Excel Discussion (Misc queries) | |||
Help with converting a block of cells with Absolute and mixed references to relative references | Excel Worksheet Functions | |||
References | Excel Discussion (Misc queries) | |||
references | Excel Discussion (Misc queries) | |||
How to rename references from range names to cell references | Excel Discussion (Misc queries) |