"Reference" to a value

[littleps]

Problem: Is there a way to reference to a value itself other than t
cell address?
Summary: I've got 2 sheets. In Sheet1 I use formula, let's say in cel
C1, =Sheet2!A1+Sheet2!B1 where in Sheet2 A1=5, B1=7. Of course cell C
in Sheet1 returns the result of 12 by reflecting formula above mentione
(=Sheet2!A1+Sheet2!B1). When doing reference to cells A1 & B1 I wan
excel to treat those cells as values, i.e. =5+7 (no
Sheet2!A1+Sheet2!B1).
I know that i can copy and then paste values and it will give 12, but
want to save components (numbers 5 & 7) in the final result (12). Afte
getting the result in cell C1 (=5+7) I would like to delete Sheet2, bu
without breaking the formula like =#REF!A1+#REF!B1.

Is there any way to do it by using standard tools of Excel or any extr
tools?
Thanks a lot in advance
Pavel
:cool

 

[Bernard Liengme]

Not a real answer to your question but:
use =Sheet2!A1+Sheet2!B1 in C1
and =Sheet2!A1&"+"&Sheet2!B1 in D1
Do copy and paste special(values) to see 12 and 5+7.
The D1 value is text but will serve as documentation
best wishes

 

[littleps]

Bernard,
Smart decision, but frankly speaking i want a shorter way to do i
since gonna use this exercise often.

anyway thanks, it's alway nice acquiring new knowledge by finding ne
methods/formulas

 

[Bernard Liengme]

Hi,
Here is VBA subrountine that does what you need - hope!
best wishes

Option Explicit
Sub myadder()
Dim arng As Range
Dim brng As Range
Dim outrng As Range
Dim mystring As String
Set arng = ThisWorkbook.Worksheets("Sheet2").Range("A1")
Set outrng = ThisWorkbook.Worksheets("Sheet1").Range("A1")
Do Until IsEmpty(arng)
Set brng = arng.Offset(0, 1)
mystring = "=" & CStr(arng.Value) & "+" & CStr(brng.Value)
outrng.FormulaR1C1 = mystring
Set outrng = outrng.Offset(1, 0)
Set arng = arng.Offset(1, 0)
Loop
Set arng = Nothing
Set brng = Nothing
Set outrng = Nothing

End Sub

 

[littleps]

Hi Bernard,
I should've explained better what i need not to waste other people's
time thinking over the issue.
To tell you the truth i am not VBA adept. Anyway i've tried it and
think it's a bit limited for my needs.

Reason: i am doing a kind of manual breakdown for expenses by projects
in one of the excel sheets by using user id, i.e. a certain user is in
charge of a certain type of expenses. A certain cell in excel can
comprise several numbers (which are milions). I know you're thinking
why not using some lookups or anything else...but sometimes i have to
drilldown to user id so as to identify other kinds of expenses (just
visiually) therefore to allocate them to a different cell. It's not
always 1 user=1 kind of expenses.
This 3000 lines table is arranged by using subtotals agains user id.
Subtotal number is reflected as a formula as well. When i am typing
formula in my Breakdown sheet it returns like
=Sheet2!E17+Sheet2!E10+Sheet1!D7.
i wonder if there is a way to convert the formula saving cosequence of
values no depending on sheet or cell locations.
Or if no as i understand i need to remember the value and type it
manually and no other way. But i think to convert the above formula