Cell Referencing - Recognizing Data from Row

[carrieebacon]

Okay A) I would like to apologize for constantly posting on here, but I am learning. B) I have one sheet in a workbook that contains asbuilt data (ASBUILT), and one sheet that will be used to format the text that I will place into Trimble Business Center (TBC TEXT). I would like to pull (or reference) from column J in ASBUILT to place into either column D, F, I, or L. The thing is, it needs to align with the correct ID number in column B in ASBUILT. If there is a better way to do what I am trying to do, feel free to tell me. I have been working on putting this together for a while and I think I have lost my sanity.

Here is what I am working with.

This is the ASBUILT sheet

And this is the TBC TEXT sheet

 

[mumps]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). Also, you have multiple occurrences of each ID in ASBUILT but only one occurrence of each ID in TBC TEXT. How do you decide which of the multiple values in column J in ASBUILT to pull and how do you decide into which column (D, F, I, or L) to place it? It would be helpful if you could post what your end result should look like based on the data you posted.

 

[carrieebacon]

Here is the link:
TRIAL Storm Asbuilt - Copy.xlsm | Powered by Box

Okay SO. The ultimate goal is for it to look like the DESIGN sheet, but containing ASBUILT data. So I referenced Columns A, B, C, E, G, H, J, K, AND M from DESIGN to TBC TEXT for the correct text, ID number, and direction. Essentially, only the elevation (ELV.) needs to be put into the TBC TEXT sheet. I am going to use CI 11 as an example. In the design, CI 11 has three inverts (INV.). Those elevation points were recorded on the jobsite and I import them into the spreadsheet as a csv file and then run a macro to organize and clean up. So in the ASBUILT sheet, there are three rows with the same ID number of 11 because there are three inverts. I guess one way to ensure they are placed correctly into TBC TEXT is by the direction (Dir.).

 

[carrieebacon]

Let me correct myself... I meant to say "Essentially, only the Invert Elevation (Inv. Elv.) needs to be put into the TBC TEXT sheet."

 

[mumps]

I'm a little confused. In the DESIGN sheet, for ID 11 you have 6.08 for W, 9.08 for N and 6.08 for E. Looking at ID 11 in the ASBUILT sheet, you have -19.5 for W, -19.3 for N and -18.2 for E. The values for each direction don't match. Please clarify in detail.

 

[carrieebacon]

Design elevation is going to be different from asbuilt. Design is what the engineers say the elevation should be, asbuilt is what the elevation actually is after utilities have been installed. The design details are only in there for when I eventually compare data in the TOLERANCE sheet.

 

[mumps]

The ultimate goal is for it to look like the DESIGN sheet, but containing ASBUILT data.

I guess that this is what confused me. Are you saying that you want the values -19.5 for W, -19.3 for N and -18.2 for E. copied to the TBC TEXT sheet?

 

[carrieebacon]

That is correct. I would like to find a way to do this without having to copy and paste each one.

 

[mumps]

In the ASBUILT sheet, ID 17 has the same direction (S) for both point names. Should they not be different? Also, you have "????" in cell I18 and a blank in cell I26. How do you want to handle this issue?

 

[carrieebacon]

My boss created a csv file with fake asbuilt data, thats what I have been using for this. I am trying to come up with a new process for collecting asbuilt data. So yes you are correct, they should be different. That's just what he put I guess.