# Im struggling

Copper Contributor

# 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

4 Replies

# Re: Im struggling

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.

# Re: Im struggling

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?

# Re: Im struggling

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.

# Re: Im struggling

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