Including spaces in cell lookup/INDIRECT reference

I am using a function to find data in a specific cell on a specific sheet. I want to apply this to other cells via dragging the formula and having Excel work its magic adjusting the variables.
This works fin until one of the cells i am referencing from contains a space in its data. The I get the #REF! error.

Formula:

Please Login or Register  to view this content.

INDEX = the spreadsheet with a variety of names listed in order and reflects the names of each sheet i need to get data from.
A7 = the cell on INDEX which changes to A8, A9 etc as i copy the formula down my sheet (where the data will be output).
B100 = the cell on each of the various sheets where this specific data lives. When i drag the formula across the row, I need to manually change B to C, to D etc.

Basically i need a way to force the reference to INDEX!A7(etc) to accept spaces in the cells on sheet INDEX. I cannot remove the spaces as this will negatively impact my core data and is replicated from an external source anyway.

Is this possible?
Is there a way to make the B100 etc automatically change to C,D etc as i drag frtom left to right? This is not as important.

Thank you for the reply. Your first answer solved my problem. I ran out of time to try out the second formula but will do soon.

I understand about uploading an example, its just that this is a massive spreadsheet with many interconnected worksheets and formulas. When i tried to break it down into a simple example it all broke. I figured there was some syntax i was missing in my formula as it was working as expected for data without spaces. Thank you again.