Indirect Formula

[aarmartin]

I am trying to create a spreadsheet using an indirect formula. When I put the formula in the cell it works. when I drag the formula though, I can get the first cell reference to be relative but I cannot get the second cell reference to become relative. When I drag the formula down it is going A6,F5 to A7, F5 but I need it go A6, F5 to A7, F6. Any ideas on how to fix this?

 

[Joe4]

Welcome to the Board!

What is the exact formula you are using?
What cell are you placing it in?

 

[aarmartin]

=indirect("'"$$A7&"'!F6")
putting it in a blank cell

 

[Joe4]

The issue is that when you place the cell references between quotes like that, they become strings. Strings won't automatically increment as you drag the Indirect formula.
So, you will need to do something in the formula to increment it, using functions like ROW() & COLUMN() (which return the current row or column the formula is in) and maybe the OFFSET function.

But in order to help you write that, I need to know the exact cell address you are putting the first formula in (which "blank cell")?

 

[ValuationMan]

That formula has constituted has a few issues.

=indirect("'"$$A7&"'!F6")

$ is probably supposed to be &

Also your F6 is in the quotes to it will always be F6. The formula below now has F6 as a cell reference and you can add $ for the absolute reference.

=indirect("'"&$A7&"'!"&F6)