GG I think you now have your answer. Mr Sub has identified you were looking at cell refernces I wrongly asummed you were looking for cell values. If you are looking for references then dbTrader has it with the "indirect address" formula, but it should refer to B1 not A1. Put dbTrader's formula in cell A1 as follows: =INDIRECT(ADDRESS(10*ROW(B1),2)) With A1 selected normally, drag down the little solid box in the bottom right of the A column cursor frame as far as you require. Cell A1 will = the value of Cell B10 Cell A2 will = the value of cell B20 etc etc.
thanks, guys. i think a line like this will work. i do have one more question, though. is it possible to put this line "=INDIRECT(ADDRESS(10*ROW(B1),2))" in say, sheet2, but be calculating data from sheet1? for example, say i have 2 sheets: sheet1 and sheet2 say in sheet1 A1 has =b10 and A2 has =b20 now here's the next step: can i put "=INDIRECT(ADDRESS(10*ROW(B1),2))" into sheet2 instead of sheet1 with references to sheet1? i am familiar with using, for example, =sheet1!A1 but i can't seem to get that to work right with "=INDIRECT(ADDRESS(10*ROW(B1),2))" sorry, i know this is confusing... here is what i'm trying to do, maybe you can tell by looking at this: in sheet2: =INDIRECT(ADDRESS(10*ROW(sheet1!B1),2)) will that work? i don't think it does. thanks
Thanks for giving me the opportunity to show some skills here: the only thing you are missing in the new formula when you added the dimension of sheets to the mix is a way to tell Excel to refer back to sheet1 to find the data. If you have your reference data - the stuff in b10, b20, ... on sheet1, and you are looking up on sheet2, one way to do this (and keeping in line with the indirect / address combo I introduced when it was all on one sheet) is as follows: =INDIRECT("sheet1!" &ADDRESS(10*ROW(Sheet1!B1),2)) as you see -- you were very close but while you told Excel to look on sheet 1 to see which row to key off of, you also needed to key Excel to go there to get the data. HTH, dB - Excel Dude
GG: Since I am now in back in my "zone" -- I used to have dozens of people line up all day on a very large Energy trading floor asking for this type of help and more ... Let me introduce you to =index(datatable,row,column) : (1) data This is your target - you would need to capture the entire range of the B column which you currently are extracting every 10th value - you could be sophisticated and insert a name Insert/Name/Define or just note where it is to allow Excel to fill it in for you. (2) row the column is derived as before by multiplying 10 (or whatever incremental value you desire) by the row where the current formula lives. 10*Row(a1) (3) column this is 1 since the data is a single column of values so on sheet2 the formula looks like: =INDEX(Sheet1!B:B, 10* ROW(A1),1)
dbTrader, thanks for all your help. i made a basic spreadsheet showing EXACTLY what i'm trying to do. basically, in sheet1, i have 5 minute bars. from these 5 minute bars i am creating daily bars. the daily bars are also created on sheet1. however, want i want to do is get the daily bar data created on sheet1 into sheet2. it turns out, the daily bars created on sheet1 are separted by 81 rows. i would like them to be successive rows in sheet2. instead of looking up each row number on sheet1 and putting them all into sheet2, i would like to make 1 or 2 rows on sheet2, to define the relationship of 81 rows, then just copy and paste that all the way down. in this worksheet, i only have 3 days, but in my real worksheet i have many days, so this would be very time consuming. what do you think would be the best way to do this? i will attach the workbook. be sure to look at sheet1 and sheet2. i added some comments on some cells, too, to help explain what i want to do.
This is so therapeutic - just what I needed on the week I am taking off to clear my head. I am so jazzed doing this stuff that I put together three options for you to use and perhaps gain some Excel knowledge from. Personally, I would put the dailies in Column H on the same sheet - I always stress that data should stay on the same sheet for efficiency and because then it is easier to analyze and check. I automated the date column such that after the inital date Exce l will look for the next largest date so that Weekends and Holidays are excluded. I also put in blanks so that you can stretch this (and all the formulas for OHLC) down the sheet in advance. The other columns do calculations to be able to find the correct l (Open/Close) cell or (High/Low) range. On sheet2 I basically redid the same in the first 5 columns - the only difference was the inclusion of the sheet1! before the ranges which refer to the 5 minute data. In columns G to K I used the Array Formula functions to do the High and Low ranges - this was just to show you what these are - if you are unfamiliar. They perform the exterior function of a subarray that you create through conditional statements - kind of like querying in other languages. To designate a formula as an array formula, you must edit the formula, or when you are initially typing it in, hit Shift Control and Enter simultaneously to get the curly braces to appear. If you go into the formula and hit enter to leave the braces will vanish and you will end up with either a logical error or an "Excel" error. Okay, this is starting to sound geeky but everyone who isn't a geek knows that Excel Rocks and that it is the antithesis of geek. So know your shit and don't suck I hope (a) the Excel file attached and (b) that this helps you makes $$$$. I remember your posts a few months back and I especially feel good if I can do anything to get you making $$$$. Trade on and Trade Well.
dbTrader, thanks! this is very helpful! i saved that file so i can learn from it in the future, too. i totally agree, EXCEL RULES! p. s. thank you to everyone else who replied in this thread, also!