Monday, May 16, 2011

yogi_Get Information From The Next Row Down Without Having To Change All Tthe Formulas One By One

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
heatherd1015 said:
I have a painting company and I use a google form to gather information about the rooms my clients want painted. Every time I submit a new form the information is put into a google spreadsheet that I use to estimate job costs. I have the room measurement the clients name and address and my calculations all on sheet 1. Every column is labeled the same, the information just varies row to row.
So the first customers information gets put into row 1. The next customer is in row 2 and so on. This is sheet 1.
Sheet 2 I have as a Estimate form that I can print and give to the customer. It has my business name, the estimate # and the total job cost. Lets say I have the formula in the "name" cell on my estimate to be. =Sheet1!G16 which returns "Heather" I complete the rest of the cell formulas to pull various information from row 16. Such as Job description, total cost, etc. I print the form and I am done and now I need to print another estimate for the next client whose information is all on row 17.
Is there a way I can change all the number 16's: A16, G16, L16, to A17, G17, and L17 automatically? I don't want to have to waste my time changing the formulas every time I have to create a new estimate.

Part of Sheet1

Part of Sheet2
as exists now with formulas based on row 16 as a static row

Part of Sheet2
making row number used in the formulas as a variable
so for estimate for next row down 16 if changed to 17 will update results for row 17