Excel formula having reference to other sheet and existing sheet

[shreeram.maroo]

Hi,

I have a basic doubt while using any formulas in excel. for. e.g. sumif, index, match, vlookup etc. where let's say the sum range is in another sheet, criteria range is in another sheet and criteria is in the current sheet where formula is being used.

Consider the attached sheet where i have added a formula =SUMIFS(Results!C:C,Results!B:B,Currency!B3)

My basic doubt is while writing the formula and giving references to relevant columns / cell (while i am trying to reference cell "B3" in criteria), excel by default writes it as "Currency!B3" and not only "B3". Is there any way wherein excel by default considers references in current sheet as only "cell address" and not "sheet name + cell address".

I hope I am able to explain the issue.

[rory]

Just remove the sheet name afterwards. It happens if you are switching between sheets when writing formulas. I often just leave those arguments blank and fill them in afterwards, or type any cell reference without switching back to the sheet with the formula.

[HansV]

To add to Rory's reply: if you want to remove the sheet name afterwards, you can do so in one go:
Activate the Replace dialog (Ctrl+H).
Enter Currency! in the 'Find what' box.
Leave the 'Replace with' box empty.
Click 'Replace All'.

[shreeram.maroo]

Thanks Rory and Hans, these tricks even I use quite often.

But in general i was trying to check if anyone is aware of any permanent solution to this.

[HansV]

As far as I know, it is not possible to avoid this behavior.

[shreeram.maroo]

Hmm, got it. Even i tried to research a bit around this but didn't find any solution.