Cross-sheet reference not working

Dear Mates,

I have this issue.

Along the way, I have been building my excel (results and reference) on the same sheet. However, now i need to seperate the (results) and (reference) on different sheet (Same Workbook).

After i cut and paste the reference portion to another sheet. Both sheets no longer referene each other.

The cells formulas are not even shown as highlighted.

What could be the issue?

Hi bebo021999,

Eg. Before. (results and reference), same sheet, works
=VLOOKUP($B$4&$B$7&$B$1&$B$9,$CL$3:$CM$101,2,FALSE)

Eg. After (results and reference), different sheets, doesnt work.
=VLOOKUP($B$4&$B$7&$B$1&$B$9,Reference!$H$3:$I$101,2,FALSE)

Hi bebo021999,

I will prepare some screenshots to illustrate.

Before:
Tables and Reference all in one sheet
Tables are blue area, Reference are red areas.
Forumlas in blue area are taking references/ array of tables in reference area (red area)
All is working fine

After:
Moving reference (red area) to another sheet.
Formulas in blue areas are not taking reference from my red areas anymore.
The forumla bar no longer highlight and take reference from the array table anymore.

1. Attaching a sample file as explained in the yellow banner at the top will be more helpful than the screenshots
2. "The formula bar no longer highlight and take reference from the array table anymore." This is because it's on another sheet - this is normal.
3. We still don't know what you mean when you say, "doesn't work".

Hi Gregb11,

You are right. The formula is working normally wven the reference table in another sheet is not being highlighted (in the formular bar).

I am curious, how can i make the reference area in another sheet shown highlighted (in the forumla bar)? This will help me to track and identify the tables i am referencing.

I am curious, how can i make the reference area in another sheet shown highlighted (in the forumla bar)?

I don't think you can. Do you still have a problem? You haven't addressed the other 2 points.