Im struggling

Copper Contributor

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

 

 

4 Replies

@aidanppp 

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.

@Hans Vogelaar 

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?

 

 

 

 

 

 

 

@aidanppp 

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!!