Software

Ask something
Question Locked

Excel Formula help

0 helpful
5 replies

by btol1

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Excel Help
Im trying to auto fill a formula that gathers info from other cells, but is not a fixed cell. I need it to increase by eight cell numbers at a time. like here
=CELL("contents",Sheet1!$E6)
=CELL("contents",Sheet1!$E14)
=CELL("contents",Sheet1!$E22)
=CELL("contents",Sheet1!$E30)
=CELL("contents",Sheet1!$E38)
but when I auto fill comes out like this
=CELL("contents",Sheet1!$E6)
=CELL("contents",Sheet1!$E14)
=CELL("contents",Sheet1!$E22)
=CELL("contents",Sheet1!$E30)
=CELL("contents",Sheet1!$E38)
=CELL("contents",Sheet1!$E11)
=CELL("contents",Sheet1!$E19)
=CELL("contents",Sheet1!$E27)
=CELL("contents",Sheet1!$E35)
=CELL("contents",Sheet1!$E43)
=CELL("contents",Sheet1!$E16)
=CELL("contents",Sheet1!$E24)
=CELL("contents",Sheet1!$E32)
=CELL("contents",Sheet1!$E40)
=CELL("contents",Sheet1!$E48)
=CELL("contents",Sheet1!$E21)
=CELL("contents",Sheet1!$E29)
any help would be appreciated.

5 replies

by bizzo

Weird one this, I have no idea how to fix it.

A workaround would be to do this:

Have a hidden column in your second sheet, that contains the row numbers you need, 6, 14, 22, etc, (in this example, I'm using column A) autofill will work with this. And for your contents column, use
=INDEX(Sheet1!E:E,A1),
=INDEX(Sheet1!E:E,A2),
=INDEX(Sheet1!E:E,A3), ... etc

by delbertpgh

Here's the formula again:
=CELL("contents",INDIRECT(ADDRESS(ROW()*8-2,5,1,1,"Sheet1")))
You will have to fiddle with the row term. This example assumes that the formula in row 1 would want to get the data in E6, and row 2 would want E14, etc. However, if your formulas started six rows down the page, such that in row 7 you wanted E6, and in row 8 you want E14, etc., then you'd need (row()-6)*8-2 as your term.

by btol1

That works out great. It took me a minute to figure it all out but I re did the whole spread sheet this way and everything work as needed. Thanks a million Delbert.

This discussion is closed to new replies.