Any help please

I want to create a header/footer for a sheet taken from the info in a particular cell.

Its straight forward enough to create it from filename/tab name etc but cant see how I can take it from a cell

Any thoughts

Thanks