formula for computing multiple links

[jv]

col. A B C D E F G
Budget AFR AFC HAK AKC Bal. %Left
33,300 7,500 950 3,750 2,500

From the above example;
Link sheet are: ( expenses columns )
col.B = AFR!F6
col.C = AFH!F6
col.D = HAK!F6
col.E = AKC!F6

Question:
1 - I need to have a formula for column H, (B+C+D+E)-A
2 - In column G, balance percentage ( remaining amount in
the budget )

Can someone help me solve this problem?

jv

 

[Roger Govier]

Hi

Try entering in F1
=INDIRECT(B1)+INDIRECT(C1)+INDIRECT(D1)+INDIRECT(E1)-A1

in G1
=F1/A1 and format as Percentage

 

[jv]

Roger,

Following your tips, i get this result #REF!

Is there anything i have to adjust or rectify in my work
to achieve a better result?

jv

 

[Roger Govier]

Hi

Either you need to change the references to B2, C2 etc as row 1 contains
your column headings
or, I must have misunderstood your question.
I assumed that in the cells you had the text reference to the sheet location
for the data.

Assuming your column headings are in row 1 and that in row 2 you have the
formulae as you have wrtiiten them
i.e. in B2 you have
= AFR!F6 and what shows up is the numeric value from that sheet
reference,
then your formula for F2 is simply
=SUM(B2:E2)-A2
and for G2
=F2/A2 formatted as percentage

 

[jv]

Roger,

=SUM(B2:E2)-A2

will give me a negative value. How can i make a formula to
change it to:
A2 - B2:E2

Thanks for your assistance.

Same question was raised by Mr.JC today subj: "links
formula". Its relative to my work and an added knowledge
to me, i can incorporate his idea in my work. Can you
share your idea on his problem.

jv

 

[Roger Govier]

Try A2-SUM(B2:E2)

--
Regards
Roger Govier

Roger,

=SUM(B2:E2)-A2

will give me a negative value. How can i make a formula to
change it to:
A2 - B2:E2

Thanks for your assistance.

Same question was raised by Mr.JC today subj: "links
formula". Its relative to my work and an added knowledge
to me, i can incorporate his idea in my work. Can you
share your idea on his problem.

jv

 

[jv]

Roger,

Now its working! The tips you've shared are clearly
digestible and can't be found easily in the book.

Thank you so much and my regards.

jv