formula for computing multiple links

  • Thread starter Thread starter jv
  • Start date Start date
J

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
 
Hi

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

in G1
=F1/A1 and format as Percentage
 
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
 
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
 
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
 
Try A2-SUM(B2:E2)

--
Regards
Roger Govier
jv said:
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
Click to expand...
 
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
 
Back
Top