Hi, I want to extract the cell reference from within a formula and can't for the life of me think how to do it ! Let's say in A1 i've entered a date. Cells A2 to A10 all point at cell A1 (so the formula on all those cells reads =A1). What I want to do is this: In the corresponding cell in column B I want to show what cell is referenced in the cells in column A. So, in B1 it would probably return something odd (because there's no formula in A1), but in cells B2:B10 it would return "A1". I won't bore you with the reason for needing this. Suffice to say i've got a complex spreadsheet that i've managed to mess up by virtue of applying some sort criteria and i'm trying to restore the data to it's original state. Thanks Mick

What I would do is use find and replace. First right click on the worksheet tab, and choose "move or copy", shove it at the end and create a copy. On the copy, highlight the cells you want to get the formula of, CTRL-H and replace = with ' (a single quote). Then copy this whole column and paste special (values) on the sheet that is current. Should give you your desired result. If you have any IF formulae, this will make the cell look a bit odd, but I can't think of a way around this. Failing this, you could try looking at VBA Range.Dependents property.

Hi, Thanks ! That did do exactly what I wanted to do. Unfortunately it didn't solve my problem ! I don't think i've much option but to start again. Thanks for the tip though - annoyed I didn't think of it myself !! Cheers Mick

Would using Tools...Options...View Tab...Check the Formulas box work for you? That would show the formula in each cell, so instead of them appearing in column B, they would show up in the actual cell in which they reside.

You could try the CELL function. Something like: =CELL("address",A3) There are a whole host of attributes that the CELL function returns, like the "contents", "Col", "Row", etc. etc. MIKE

Thanks for all the replies. It looks like the 'problem' occurred at some other stage of me working in the sheet. I thought the sort i'd done had maybe screwed around with the formulas (although I know it shouldn't) or maybe a result of switching between different versions of Excel (2007 vs earlier versions). It looks like it was probably something more basic than that - ie user error ! Anyway, sorted now by me starting the piece of work again. Thanks again for the replies. Cheers Mick