Question

Locked

Excel Formula help

By btol1 ·
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!$E3
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!$E3
=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!$E4
=CELL("contents",Sheet1!$E21)
=CELL("contents",Sheet1!$E29)
any help would be appreciated.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Weird

by Bizzo In reply to Excel Formula help

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

Collapse -

follow the link ...

by AyukNotna In reply to Excel Formula help

Hi btol1,

check the link below plenty of helpful tips.

http://excel.tips.net/ci.html

goodluck

Collapse -

=CELL("contents",INDIRECT(ADDRESS(ROW()*8-2,5,1,1,"Sheet1")))

by DelbertPGH In reply to Excel Formula help

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.

Collapse -

That works.

by btol1 In reply to =CELL("contents",INDIRECT ...

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.

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums