Forum Discussion
Im struggling
Hi, Im trying to drag my mouse across while having my sheet number ascend but the cell number remain constant. example of what i am trying to achieve ='Day(28)'!H3 ='Day(29)'!H3 ='Day(30)'!H3 ...
Let's say the first formula ='Day(28)'!H3 is in a cell in row 2, for example in B2.
Change it to
=INDIRECT("'Day(" & ROW()+26 & ")'!H3") The +26 is used to return 28 from the row number 2. If you start in another row or if you want to begin with Day(1), change the +26 accordingly.
hi Hans,
i tried what you suggested and it does represent the cell value but it doesnt go up in the sheet numbers as i drag to the right.
Here is an example of what I have manually typed in (I have 40000 lines of data to enter, manually typing will take too long)
=indirect(“‘day(“&row()-2&”)’!h3”)
=indirect(“‘day(“&row()-1&”)’!h3”)
=indirect(“‘day(“&row()-0&”)’!h3”)
why will it not automatically do this as I drag to the right??
how do i make the -2,-1,+0,+1,+2,+3 change but nothing else?I thought you wanted to fill or copy downwards.
If you want to fill or copy to the right, use COLUMN() instead of ROW(), for example
=INDIRECT("'day(" & COLUMN()-2 & "'!H3")A worksheet has "only" 16384 columns, so you won't be able to fill 40000 columns.
- Hi Hans,
Thank you for taking the time to help me with the problem, im pretty sure u you have a black belt in excel, your a legend, have a good one!!
