I am trying to pull data from a cell based on another cells value - beginner help

Hi all,

I am a logistics planner who is trying to set up a spreadsheet to aid the processes of what our team does to complete our planning.

I have multiple worksheets with different formulas which I will try and explain.

My first sheet allows us to manually input data such as customer and site location and requires no formulas. We have setup a formula which works out what time a load needs to be despatched and will return based on a site time which we input or import from a sheet. Next to that information we are able to insert a registration in a seperate column. This information then copies through to a seperate worksheet where it converts and joins the text together, which we can then copy and paste into a booking sheet which we send out by email. This is using the textjoin function.

We now are trying to populate our vehicle booking sheet with it pulling the information based on the registration value and copying the text from the cell alongside it into our vehicle booking worksheet. I am guessing perhaps a vlookup function?

Just not sure how to approach this. I will insert and example below

OUTLET (A1), TEXT (B2), START TIME (C2), TOWN (D2), TIPPING (E2), TEXT2 (F2), TEXT3 (G2), RETURN TIME (H2), REGISTRATION (I2), WORK (J2)

Work box pulls from this formula =TEXTJOIN(" ",FALSE,A2,B2,TEXT(C2,"HH:MM"),D2, E2, F2,G2,TEXT(H2,"HH:MM"))

registration column contains REGISTRATION TEST and works contains MHC - 07:00 BIRMINGHAM 0 MINS DW - 09:00

I want this data to copy into a booking sheet which contains all of our registrations and finds the work based on their registration. Some registrations will be found multiple times as they do multiple trips. We currently have to manually copy and paste this data.

Thanks in advance for any help with this

Mike

Welcome to the forum.

It would help if you could attach a sample Excel workbook, by following the guidelines given in the yellow banner near the top of the screen.

Hope this helps.

Pete

Thanks for the response, could you advise how to do this, I can't see an upload function

Thankyou

The yellow banner explains the procedure, but I reproduce it here:

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Hope this helps.

Pete

Hopefully this has pulled through

Thanks again

I've read through your first (lengthy) post a few times, and I'm still a bit unclear what you want to achieve as there are some inconsistencies. Referring to your file, you have this in cell J2 of the MHC - Paste_Sheet sheet:

MHC - 07:30 BIRMINGHAM 30 MINS DW - 09:00

and this refers to a Reg (Column I) of Test 1. There are two other entries for Test 1 on rows 5 and 8. So, would you like the data from J2 to appear in cell C2 of the Vehicle Booking Sheet, and for J5 (9:00-10:30) to appear in D2 and J8 (10:30-12:00) to show in E2 ? And, indeed, for J3 (with a Reg of Test 2) to appear in C3 of the Vehicle sheet ??

If so, then that is now a bit clearer for me.

Pete

Apologies for my lengthy post, that is exactly what I am trying to achieve. I am guessing it is possible but i am not sure where to start with it. The registrations will vary day to day so it's not likely to be a consistent formla also.

Thanks

Mike

First of all, I would suggest that you change the formula in cell I2 of the Paste sheet to this:

=MHC!K2&""

This should copy down automatically, and it will show as a blank rather than as zero if the source cell is empty (just looks better). Then I have used this formula in K2:

=IF(I2="","",I2&"_"&COUNTIF(I$2:I2,I2))

Again, it should copy down automatically, and it will generate a unique reference for each load, based on the registration and load number. In the Vehicle Booking Sheet you can use this formula in cell C2:

=IFERROR(INDEX('MHC - Paste Sheet'!$J:$J,MATCH($A2&"_"&COLUMNS($C:C),'MHC - Paste Sheet'!$K:$K,0)),"")

and this can be copied across and down as far as required.

Hope this helps.

Pete

I'm going out shortly, so I thought you might like to see this in action - I've attached the workbook that I worked on.

Hope this helps.

Pete

I can't think of an easy way of sorting the load by time order at the moment, but it is a bit late so I might see things differently in the morning.

As for the second question, you should just try things out - if it doesn't work for you then don't save the file (or save it with a different name), so that you don't mess up the live version.

Hope this helps.

Pete

Thankyou very much for the help, it’s been a real benefit. Let me know if you think of anything regarding the timing order.

Thanks again

Mike

Hi Mike,

I'm going out shortly, and will be out for much of the day. Could you attach a newer workbook which shows some of the "misplaced" loads, so I can take a look at it when I get back in.

Cheers,

Pete

By the way, I don't see any date information in the file, so does that mean that you use a new file every day?

Pete

Morning,

Thanks again for your help with this, this is an example of what I am talking about. We do indeed use a new sheet every day. There is no date input on the sheet, only time functions.

Thanks

Mike

Hi Pete,

I tried to use the formulas against the actual sheet we are working with and its proving to be more difficult than I anticipated. I have attached our actual working sheet minus the data protection information. If you could have a look at this version instead it would be greatly appreciated.

Regards and thanks

Mike