running 7.0.2.2
match when data is not on the same sheet? - got match/index worked out if the data is all on the same sheet but not if on different sheets - is this possible?
I’m getting Err 504
population data (range) is on sheet 6 formula is on sheet 5
thanks!
If you are still not very confident in writing formulas, use the Function Wizard - this avoids many mistakes
FYI Help contains a list of all error codes with explanations:
thanks for being very helpful, unlike the idiot that answered here first which was of no help!
the data was hyperlinked on both sheet 5 and sheet 6 so I didnt think that would be the issue, but sure enough it was, changed the text to unformatted text on sheet 6, and the formulas on sheet 5 with sheet 6 data worked! Also changed text to unformatted text on sheet 5 to make sure sure the gremlins were not just playing bad tricks on me!
thanks again for the help! 
Oh, what a bad term you used! Kindly correct your comment before no one sees it. That guy really wanted to help. But as a teacher with a very long experience, he first of all considered you an idiot and tried not to give a solution, but to push you to find the true cause of your problem. Look at his karma - it is one and a half times more than mine. I assure you there are real reasons for this - he loves and knows how to help people, but not everyone can understand this.
Study the example, and either find the way you can solve your problem, or report exactly what you need to be basically different.
Your image is unfortunately of no use for me.
hello @stmoose,
this is a try for a ‘kiss’ solution, not sure if it works (in all cases),
!!! make a backup to not to break things !!!,
select an area completely! covering the referenced range, (mousedrag or shift-cursor),
ctrl-x,
go to the desired target sheet / location,
crtl-v,
it’s magic, in some / most cases calc automagically adapts all formulas as needed,
beware! imho it doesn’t work for move to other files,
undo - ctrl-z - has shortcomings after such action (which is a bug imho), organize not to be dependent of it!,
re-cutting and re-pasting to the original area looked to work,
not extensively tested, just tried and looked working, try yourself …
if it works for you you may analyse the adapted formulas to learn how to formulate yourself in the future …
P.S. ‘solved marks’ and ‘likes’ welcome,
click the grey circled hook - ✓ - top left to the answer to turn it green if the problem is solved,
click the “^” above it if you ‘like’ the answer,
“v” if you don’t,
do not! use ‘answer’ to add info to your question, either edit the question or add a comment,
‘answer’ only if you found a solution yourself …
