dynamic cell refs

[Raggy]

I have data on one sheet called 'Bill Bloggs - Mandatory" and his name is in Cell B4, now I want to be able to fill the cells across using the same formula to pull the data from the sheet ;Bill Bloggs Mandatory'. The sheet where the formula is called 'Staff Record'.
I can do the parametric for the name or the sheet to use as there will be other sheets such as 'Bill Brown - Mandatory' for names in the staff name column but cant figure out how to fill them across parametrically. I can see in my formula C7 is 'hard coded' but how to I make C7 parametic to pull of the data from the other sheets. Use another 'INDIRECT' for that one as well? My attempts have failed so far!

 

[awoohaw]

Try:

=INDIRECT("'" & $B7 & " - Mandatory'!C7")

 

[Raggy]

Yes I got that bit to work si I can copy the cells down, but it was the C7 ref wich is in another sheet I need to make parametric as well. If I copy the cells across I would have to manually update C7 to D7 and E7 etc

 

[awoohaw]

does it move over and down, or just down?

Yes I got that bit to work si I can copy the cells down, but it was the C7 ref wich is in another sheet I need to make parametric as well. If I copy the cells across I would have to manually update C7 to D7 and E7 etc

It is hard to tell what cell your FIRST formula is. But you need to calculate the first two components of this formula and include it in the concatenation of C7 in your INDIRECT formula:

ADDRESS(7,3,4)

So, if your FIRST concatentation formula is in cell C4, Somethng like this:

ROW(C4)+3 COLUMN(C4)+0 (I added the "+ 0" to show you where to add or subtract a number.

=INDIRECT("'" & $B7 & " - Mandatory'!" &    ADDRESS(ROW(C4)+3,COLUMN(C4)+0,4)  )

 

[awoohaw]

does it move over and down, or just down?



It is hard to tell what cell your FIRST formula is. But you need to calculate the first two components of this formula and include it in the concatenation of C7 in your INDIRECT formula:

ADDRESS(7,3,4)

So, if your FIRST concatentation formula is in cell C4, Somethng like this:

ROW(C4)+3 COLUMN(C4)+0 (I added the "+ 0" to show you where to add or subtract a number.

=INDIRECT("'" & $B7 & " - Mandatory'!" &    ADDRESS(ROW(C4)+3,COLUMN(C4)+0,4)  )

If this doesn't work. please post an xl2bb mini workbook of 3 of your worksheets. I'm not going to try to recreate all of that manually.