Multiple worksheet lookup
Hello All,
I have two worksheets that I need a formula or a function to merge data from both worksheets into a third.
What I am trying to do is merge data associated with a specific structure (e.g. a bridge); for example in the worksheet called Merged Sheets I have highlighted two examples (also highlighted in other worksheets) of what I am trying to achieve:
• Data sourced from the BARRIER_RCS worksheet will be a straight lookup (so I am OK with this).
• For a unique Plan Number use the specific ROAD NO and specific RRD (Road Running Distance) to find the road number in the CURR_LANE_WIDTHS worksheet then find where the exact structures RRD sits within the START RRD and END RRD and return the various Lane Widths.
BARRIER_RCS
This worksheet contains data relating to a range of structures.
• Column A contains the plan number of a structure on a road (unique ID).
• Column B is the Road Number that the structure is on (there can be multiple structures on a road).
• Column C has the Road Running Distance (RRD) location of that structure (each structure will have a unique RRD).
CURR_LANE_WIDTHS
This worksheet contains physical data related to a variety of roads, i.e.
• Column A has the Road Number (note: a road number can be repeated multiple times).
• Column B has a Start RRD for a variety of road data between two RRD’s.
• Column C has the End RRD for each segment of road.
• Column D has the road Lane Width of the left lane.
• Column E has the road Lane Width of the inner left lane (if the road has two lane travelling in the same direction).
• Column F has the road lane width on the right lane.
• Column G has the road Lane Width of the inner right lane (if the road has two lane travelling in the same direction).
I hope this all makes sense, but if not please ask me to clarify.
Regards,
Dave T
|