Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset Function
Hi, I am trying to use the offset function to pick out data from a seperate worksheet. I use a variable in a cell that determines the location of the relevent cells and found this works well. My problem is that the offset function picks out the wrong data because of some extra columns that I have, for instance...... If I decide that the relevent cells are 3 backwards from the offset start point this works fine ....eg assuming the offset start point is the 1 under april Spreadsheet 1 Spreadsheet 2 Jan Feb Mar Apr ..... Reference 3 back gives 3 (under jan) 3 2 1 1 However by adding additional columns I do not pick up the information I require... eg assuming the same case but in a different time Spreadsheet 1 Nov Dec Year total BLANK COLUMN Jan Feb 3 2 15 2 3 Now assuming the start point of Feb (the 3) and going 3 cells back will give the yearly total of 15 ... whereas I am actually after the 3 under Nov. I have been adjusting this so far by adjusting any offsets that cross these blank columns and yearly totals but I would love to hear of a way that can automate Excel so I do not need to trawl through the calculations. Any help would be much appreciated!!! J -- jagbabbra ------------------------------------------------------------------------ jagbabbra's Profile: http://www.excelforum.com/member.php...o&userid=32525 View this thread: http://www.excelforum.com/showthread...hreadid=545017 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset Function
You may use names with constants. For example, you can define the name
OffsetCols to 3 (menu Insert-Name-Define, type =3 in the Refers to box) and use that value in your calculations. If you want to change the offset, just go the same menu and change the value there, all your calculations will reflect the new value. Hope this helps, Miguel. "jagbabbra" wrote: Hi, I am trying to use the offset function to pick out data from a seperate worksheet. I use a variable in a cell that determines the location of the relevent cells and found this works well. My problem is that the offset function picks out the wrong data because of some extra columns that I have, for instance...... If I decide that the relevent cells are 3 backwards from the offset start point this works fine ....eg assuming the offset start point is the 1 under april Spreadsheet 1 Spreadsheet 2 Jan Feb Mar Apr ..... Reference 3 back gives 3 (under jan) 3 2 1 1 However by adding additional columns I do not pick up the information I require... eg assuming the same case but in a different time Spreadsheet 1 Nov Dec Year total BLANK COLUMN Jan Feb 3 2 15 2 3 Now assuming the start point of Feb (the 3) and going 3 cells back will give the yearly total of 15 ... whereas I am actually after the 3 under Nov. I have been adjusting this so far by adjusting any offsets that cross these blank columns and yearly totals but I would love to hear of a way that can automate Excel so I do not need to trawl through the calculations. Any help would be much appreciated!!! J -- jagbabbra ------------------------------------------------------------------------ jagbabbra's Profile: http://www.excelforum.com/member.php...o&userid=32525 View this thread: http://www.excelforum.com/showthread...hreadid=545017 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset Function
Are your dates true date values formatted as "mmm"? If not, first
change the dates so that the month and year are entered, and format as desired. For example, enter the dates as follows... November 2005 December 2005 January 2006 February 2006 etc. Then, assuming that A10 contains the date of interest, such as February 2006, and B10 contains the offset, such as 3, try... =INDEX(A2:F2,LARGE(IF(ISNUMBER(A1:F1),IF(A1:F1<=A1 0,COLUMN(A1:F1)-COLUMN( A1)+1)),B10+1)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , jagbabbra wrote: Hi, I am trying to use the offset function to pick out data from a seperate worksheet. I use a variable in a cell that determines the location of the relevent cells and found this works well. My problem is that the offset function picks out the wrong data because of some extra columns that I have, for instance...... If I decide that the relevent cells are 3 backwards from the offset start point this works fine ....eg assuming the offset start point is the 1 under april Spreadsheet 1 Spreadsheet 2 Jan Feb Mar Apr ..... Reference 3 back gives 3 (under jan) 3 2 1 1 However by adding additional columns I do not pick up the information I require... eg assuming the same case but in a different time Spreadsheet 1 Nov Dec Year total BLANK COLUMN Jan Feb 3 2 15 2 3 Now assuming the start point of Feb (the 3) and going 3 cells back will give the yearly total of 15 ... whereas I am actually after the 3 under Nov. I have been adjusting this so far by adjusting any offsets that cross these blank columns and yearly totals but I would love to hear of a way that can automate Excel so I do not need to trawl through the calculations. Any help would be much appreciated!!! J |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
numerical integration | Excel Discussion (Misc queries) | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
offset function | About this forum | |||
clock | Excel Worksheet Functions | |||
offset function | Excel Worksheet Functions |