From One Sheet To The Next
Work with MS Excel a lot? Do you use multiple sheets inside the same workbook?
Have you ever found yourself trying to reference a value from a worksheet other than the one you’re currently working with?
I’d bet that you noticed a problem when you tried the same old formula… if you need the value from cell B2 from sheet 1 in a formula on sheet 3 and used =B2 as your formula then you were in for a surprise. You got the value from cell B2 all right – too bad it was from worksheet 3 not worksheet 1.
Looking for the “magic” you must add to your formulas to get Excel to behave as you need?
Was that a “Yes!” I heard – I just knew you were looking for this info.
Here’s the trick – unless you tell Excel to look on a different worksheet it’s going to pull numbers from the sheet that contains the formula – so, we need to tell Excel exactly where to go (that is… tell it where to find our data). We’ll need to add the sheet name to our formula so Excel knows on which sheet to look for our value.
Returning to our example – if you want to pull a value located in cell B2 on sheet 1 you’d enter this formula
Yep – that’s it! You simply have to add the sheet name (with the !) before the cell address and you’ve instantly got the value you really wanted.
And yet, more good news to this tip – the value will automatically update as you change the original data in cell B2 on worksheet 1.
And there you have it – time to use your formula workbook wide.