Question description:
This user has given permission to use the problem statement for this
blog.
Hi,
I use google sheets to record jobs completed and what is due for payment. Each job will have a different schedule of rate for what ever work is required (SOR). These SORs are loaded manually from construction plans (different row for each SOR) however there may be some variations e.g. column G is the proposed quantity and column H is the actual. The back sheet named SORs details the amount each SOR is worth. I would like to know if it is possible to automate it so it calculates what the total amount is? e.g. if column G contains something then multiply column H by the correct SOR amount for the SOR sheet?
Solved by V. A. in 17 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
16/10/2018 - 12:55
Hi
Excelchat Expert
16/10/2018 - 12:56
Welcome to Excelchat, I see that your question is about fixing formulas
User
16/10/2018 - 12:56
https://drive.google.com/open?id=1TBD04SYRK1jOkVlgRfNbLPVcp1iLzhM9CadcAY2V-JA
Excelchat Expert
16/10/2018 - 12:56
I can help you with that problem.
Excelchat Expert
16/10/2018 - 12:56
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
User
16/10/2018 - 12:56
Hi, This is a link to a good a
User
16/10/2018 - 12:56
Hi,
Excelchat Expert
16/10/2018 - 12:56
Thanks for the link
User
16/10/2018 - 12:56
Sorry! That is a link to the google sheets that I talking about
Excelchat Expert
16/10/2018 - 12:57
Ok
Excelchat Expert
16/10/2018 - 12:57
Not a problem. Let me have a detailed look.
User
16/10/2018 - 12:57
Thank you
Excelchat Expert
16/10/2018 - 01:00
Ok
Excelchat Expert
16/10/2018 - 01:00
So I went through your problem statement
Excelchat Expert
16/10/2018 - 01:00
and it looks like you trying to calculate the cost in each row
Excelchat Expert
16/10/2018 - 01:00
using the actual value
User
16/10/2018 - 01:00
Yes that is correct
Excelchat Expert
16/10/2018 - 01:01
That should be doable
Excelchat Expert
16/10/2018 - 01:01
May I know, how are you filling in the "Submitted sheet"
Excelchat Expert
16/10/2018 - 01:01
Is it being entered manually?
User
16/10/2018 - 01:02
Yes, everything up until column G is manually entered from construction plans
Excelchat Expert
16/10/2018 - 01:02
ok
Excelchat Expert
16/10/2018 - 01:03
So I see that you were trying to do cost calculation in column Q
Excelchat Expert
16/10/2018 - 01:03
Am I right?
User
16/10/2018 - 01:03
Yes
Excelchat Expert
16/10/2018 - 01:03
Ok
Excelchat Expert
16/10/2018 - 01:04
Let me change that for you
User
16/10/2018 - 01:04
Thanks
Excelchat Expert
16/10/2018 - 01:04
No worries, I am here for you!
Excelchat Expert
16/10/2018 - 01:07
So, I have pulled the rate in Column Q for you
Excelchat Expert
16/10/2018 - 01:08
and In column R, I have calculated the Cost for you.
Excelchat Expert
16/10/2018 - 01:08
Does this solution solve your problem?
User
16/10/2018 - 01:08
Yes perfect!
User
16/10/2018 - 01:08
One more quick question
Excelchat Expert
16/10/2018 - 01:08
Sure
User
16/10/2018 - 01:09
Is there a way that I can type the name of the SOR (column F) and it automatically populates the SOR number (column E) instead of me typing both in manually?
Excelchat Expert
16/10/2018 - 01:10
:)
Excelchat Expert
16/10/2018 - 01:10
Yeah
Excelchat Expert
16/10/2018 - 01:10
Let me fix that for you
User
16/10/2018 - 01:10
Amazing! Thank you
Excelchat Expert
16/10/2018 - 01:11
Does that help?
Excelchat Expert
16/10/2018 - 01:11
I have updated it for you.
User
16/10/2018 - 01:12
Amazing! Thank you kindly!
Excelchat Expert
16/10/2018 - 01:12
I am glad that I was able to help you!
Excelchat Expert
16/10/2018 - 01:12
Thanks for coming to Excelchat. Have a nice day.
Excelchat Expert
16/10/2018 - 01:12
I would appreciate any comments or feedback.
User
16/10/2018 - 01:12
No problems
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
