Amendment in formula

[Libra0099]

Dear All,

Good day!

I am working on budget model and facing a problem in amending a formula. Please note that I have to do the same amendment mentioned below in many cells which are reading from different sources.

I want to amend formula mentioned in row 8. In row 13 I have also mentioned the desired formula….

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N	O
6	Actual	8	0	0	0	0	0	0	0	0	0	0	0
7	*	*	*	*	*	*	*	*	*	*	*	*	*
8	Budget	5	11	16	22	28	36	44	53	64	74	80	185
9	*	*	*	*	*	*	*	*	*	*	*	*	*
10	Var	* * * * * * *(3)	* * * * * * *11	* * * * * * *16	* * * * * * *22	* * * * * * *28	* * * * * * *36	* * * * * * *44	* * * * * * *53	* * * * * * *64	* * * * * * *74	* * * * * * *80	* * * * * 185
11	*	*	*	*	*	*	*	*	*	*	*	*	*
12	*	*	*	*	*	*	*	*	*	*	*	*	*
13	Desired formula	5	0	0	*	*	*	*	*	*	*	*	*
Sheet1

Waiting for your reply. Thanks

 

[Ganjin]

okay, this is what I can think of doing:

1) Select The Row
2) CTRL + H (now comes the fun part)

put a SPACE in front of the "="
Find What:=
Replace with: =
Replace All
append the formula (change was needed to allow this to work)
Find What:=
Replace with:=IF(OFFSET($A$1,ROW()-2,COLUMN())=0,0,
Replace All
since 5, always seems to be at the end, (ex, ='[For link test.xlsx]Sheet1'!$H$5) use it to close the formula
Find What:5
Replace with:5)
Replace All
remove the space in front of the formula
Find What: =
Replace with:=
Replace All

 

[Libra0099]

Dear Ganjin,

Thanks you very much for your reply & efforts.

I will this try this now. But just for your information that I have to do this for so many cells reading from diffrent sourcing (Workbooks & sheets).

Can you advise any other way..... like maro for slected cells etc

 

[Ganjin]

ah, afraid that I am very much a newbie when it comes to VBA
but, fear not, there is definitely quite a few people here that can help you out in that regard

 

[Libra0099]

Dear All,

Let me sumup again in better description.

I am working on budget model and reviewing these with different deparments on monthly basis. Budget figures are linked from source files which are reading from different sources (Workbooks, sheets and cells). Is there any possibility that if Acutal figures appear then Budget figure should be visible... other wise no need to show.

For example... In Jan no need to show Feb to Dec figures..... I have sorted a formula with if condition but I have to amend each and every formula in my working... Can any one help/guide me to please amend the current formula..... Just as example I have shown in screen shot.

P.S: I know the other way of doing so by offset thing.... but due to my current circumstance I have to amend the current linked formula....

PLEASE HELP............. HELP................ HELP

 

[Libra0099]

dear all,

let me sumup again in better description.

I am working on budget model and reviewing these with different deparments on monthly basis. Budget figures are linked from source files which are reading from different sources (workbooks, sheets and cells). Is there any possibility that if acutal figures appear then budget figure should be visible... Other wise no need to show.

For example... In jan no need to show feb to dec figures..... I have sorted a formula with if condition but i have to amend each and every formula in my working... Can any one help/guide me to please amend the current formula..... Just as example i have shown in screen shot.

P.s: I know the other way of doing so by offset thing.... But due to my current circumstance i have to amend the current linked formula....

Please help............. Help................ Help

please hlep me............... I am still waiting

 

[Libra0099]

Please help!

 

[Libra0099]

Re: Amendment in formula - Any LUCK???

Any LUCK for me????????

If my description of problem is not clear then let me know... I will try to comeup with better description of my problem.... but please help me.

Thanks