Populating a cell from another worksheet

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:50 PM GMT

Question

Example of Work Order Form and Database

QUESTION: , I am not by any means anexpert, so please bear with me. I am usually able to Google an answer for what I am working on, but this one has me stumped. Here is what I'm trying to do: I need to take specific information from cells in tab named "2010 WO 3891" and populate a work order form in a tab named "Work Order". If I enter the date in the date cell on "2010 WO 3891", then I would like for that date to be populated in the date cell on the "Work Order" tab, and so on. I've tried linking the cells, which does populate the info, but I need it to advance to the next work order row when I enter a new work order. My rows on "2010 WO 3891" are set up as: A2-work order# ; C2-Date ; D2-Tenant Name; E2-Property Address; G2-Submitted By; H2-Telephone#; I2-Description of Request; L2-Date work completed; M2-Labor Rate; N2-Material Cost. The other tab, "Work Order" is set up to look like a form for the tenant to sign. The cells are to all be populated with the info from the "2010 WO 3198" worksheet. Work Order# is cell E4; Date is cell B9; Tenant Name is B12; Property Address is B15; Submitted By is B18; Telephone# is B21;Description is B24; Date Completed is B33; Labor Rate is B36; and Material Cost is B37. If you can tell me what the formula is for one of the cells, I can figure out the rest. I just need to know where to start. Thank You!!

ANSWER: Andrea,

In the sheet Work Order

E4: ='2010 WO 3891'!A2 B9: ='2010 WO 3891'!C2 B12: ='2010 WO 3891'!D2 B15: ='2010 WO 3891'!E2 B18: ='2010 WO 3891'!G2 B21: ='2010 WO 3891'!H2 B24: ='2010 WO 3891'!I2 B33: ='2010 WO 3891'!L2 B36: ='2010 WO 3891'!M2 B37: ='2010 WO 3891'!N2


> I need it to advance to the next work order row when I enter a new work order. What does that mean? You have given fixed locations on both sheets. Also, a lot of the information you cite as needing to be linked would be unique to a workorder and not duplicated in another row. For example, submitted by would only appear on the Work Order form once I would think. If you have multiple tasks and the key information from each tesks need to be on a separate row of the Work Order - then I can understand that, but that doesn't fit what you describe.

If you wanted to have all the information in a database format and create a separate Work Order form for each row, I could understand that. But I can't see what you are trying to do.

If you can explain, perhaps with an example, what you want to do, then I can probably give you some explanation.



---------- FOLLOW-UP ----------

QUESTION: I apologize...let me try to be more clear. I need to be able to go to the 2010 WO 3198 worksheet and log in the information for each call that comes in from my tenants (wo#, date, tenant name and address, phone #, date complete, labor $ and material$). I need this info to stay in a database and I would also like for that information to automatically poplulate on to a work order "form" in the Work Order spreadsheet that I can send to the tenant for signature. If it's easier to create a separate work order form for each new work order that comes in, that is ok with me. > I need it to advance to the next work order row when I enter a new work order. This comment means that I get a call from a tenant, say it is work order #3400. The next tenant that calls would have work order #3401 and so on. I have attached a side by side image of what I'm talking about. Hopefully I am being more clear. Thank You!!!

Answer

andrea,

Basically you want this: If you wanted to have all the information in a database format and create a separate Work Order form for each row, I could understand that.

the modification of the above is that you want a work order template, you enter a workorder number at the top. The workorder template populates automatically and you print it out. then you can type in another work order number and it populates and so forth.

That is the way I see your request.

so you would type your work order number corresponding to a value in column A of the sheet 2010 WO 3198 in cell E4 of sheet Work Order

E4: WO-4055 B9: =if(E4="","",vlookup(E4,'2010 WO 3198'!A:N,3,False)) B12: =if E4="","",vlookup(E4,'2010 WO 3198'!A:N,4,False))

in the B9 formula, the "3" in vlookup corresponds to returning the value from the 3rd column (column C) of the lookup range (A:N) in the same row as the value in E4 is found (WO-4055)

for B12, the value is returned from column 4.

All the other cells would have similar formulas with the adjustment made for what column is returned.

I assume the sheet name is 2010 WO 3198 from your first post as I can't read the tab name in your picture.

I believe that is what you want.

change the work order number in E4 and the work order template should update with the new data.

Advertisement

©2021 eLuminary LLC. All rights reserved.