can't save HLOOKUP Formula

[jrtaylor]

I have a multi-tabbed workbook in which many worksheets refer to other worksheets.

I've successfully used the HLOOKUP function on one of the worksheets, but when I copy it to another worksheet and attempt to change the references in the formula, I can't get it to "save" correctly.

The working formula looks like this:

Please Login or Register  to view this content.

when I attempt to change the formula by referring to another worksheet, the single ' marks don't save. So when I write this:

Please Login or Register  to view this content.

the saved formula looks like this.

Please Login or Register  to view this content.

which results in a #NA cell.

I've tried saving the formula as an array (with the {}) but this doesn't help.

Any advice?

Thanks

[Cutter]

The apostrophes aren't needed with a sheet name that doesn't have spaces in the name. The #N/A is telling you there's no match. You'd get a #REF error if it didn't like the sheet name.

[martindwilson]

just out of interest what's in c2?

[jrtaylor]

Cutter, thanks! Stupid me. The formula works on all of the other cells (first row was blank).

martindwilson, C2 is a date. The formula looks for any timestamp within a 24 hour period, so that a match can be found whether the "date" is 6/24/12 1:17 or 6/24/12 14:34.


Thanks again

[martindwilson]

Wise man, ahh now I am.

[jrtaylor]

PS,To give credit where credit is due, the "fuzzy logic" lookup formula was provided to me by protonLeah in another thread on this site!