AMOUNT ENTRY

[sa.1985]

Dear Sir,


if i entry in cell A1 , =(100+200+300+400+500) then answer will shows 15,000 in same cell.


my question is that , when i entry in cell A1 same like =(100+200+300+400+500) and i want in sheet 2 same this entry as


A1 IN 100

A2 IN 200

A3 IN 300

A4 IN 400

A5 IN 500


is this possible ?

 

[Luke M]

I think you're doing things backwards. Enter your data in sheet 2 as your describe, and make the formula in Sheet1, cell A1:

=SUM('Sheet2'!A1:A5)

 

[sa.1985]

Dear sir,

i know this formula SUM('Sheet2'!A1:A5)

but i want


In cell A1 i will enter this formula =(100+200+300+400+500)

and its out put should be in column B

B1 100

B2 200

B3 300

B4 400

B5 500

 

[Faseeh]

Hi sa.1985,


Do you want to display the sum in entire column B or in just one cell? In case you want it in just one cell, enter sum formula in that cell, if you want it in entire column, use fixed referencing and drag the formula down like this:


SUM('Sheet2'!$A$1:$A$5)


Regards,

Faseeh

 

[atulrajratna]

Hi Sa.1985,


Why do you want to enter all the numbers in formula?


you can do this-

enter the numbers in SHEET1 as

B1 100

B2 200

B3 300

B4 400

B5 500


A1 =sum(B1:B5) > so you will get the SUM > 1500

so in future, if you want to change the numbers you can change it. no need to modify the formula every time.


in SHEET2

B1 =Sheet1!$B$1 >so you will get > 100

B2 =Sheet1!$B$2 > 200

B3 =Sheet1!$B$3 > 300

B4 =Sheet1!$B$4 > 400

B5 =Sheet1!$B$5 > 500


or do you want particular numbers from the formula?


Regards!

-Atul

 

[NARAYANK991]

Hi ,


The following procedure should do the job :

[pre]

Public Sub separate_formula()
Const operators_list = "=+-/*()"
Dim y, z As Range

Set y = Application.InputBox("Select the cell which has your formula : ", Type:=8)
Set z = Application.InputBox("Select the first cell where the numbers should be placed : ", Type:=8)
x = y.Formula
len_x = Len(x)

If len_x > 0 Then
num_counter = 0
current_string = ""
For i = 1 To len_x
current_char = Mid(x, i, 1)
If InStr(1, operators_list, current_char) Then
If current_string <> "" Then
z.Offset(num_counter, 0).Value = current_string
current_string = ""
num_counter = num_counter + 1
End If
Else
current_string = current_string & current_char
End If
Next
End If
End Sub

[/pre]

Narayan

 

[sa.1985]

Dear Sir,


your answer is very helpful ?


but is this possible as formula ?

 

[NARAYANK991]

Hi ,


I am not sure , but I doubt !


The reason is that there is no worksheet function which allows you to separate the components of an Excel formula. Of course I may be wrong.


Narayan

 

[sa.1985]

ok don't worry i will use as macro


thanks narayank991