Find data and autofill

[Niceguy60]

Looking for some help on a formula if the formula is even possible. I have attached an example for reference.

I am in need of a formula that will auto fill data based on two spreadsheets having the same job number. Basically, once I add a job number to my billing spreadsheet, I want it to recognize the same number on the status document (if there is one), then auto fill a number of fields (Item, Agency Lead), so I don’t have to manually go back and do it. I know each field would need to have a formula to find that specific information but not sure if this is possible or not.

My spreadsheets are on separate tabs too but for the example I posted the items on the same page.

Any help would be appreciated.

[DannyJ]

Hi Niceguy,

A VLOOKUP function works fine. The formula for that spreadsheet is,

=VLOOKUP(A11,A3:I5,4)

VLOOKUP works as, First number is the lookup value (A11), The second cell references are where to check (A3:I5) which is your table and the number 4 is the cell reference.

Hope that helps.

Regards

Danny

[Niceguy60]

Danny,
Thanks for your help on this. This definitely puts me in the right direction but it's still not 100%. What I did was add a tab just like it is in on my spreadsheet. When I do the same formula, It gives me the value at the bottom of column . If you wouldn't mind looking at this, not sure why it's not associating with the job number.

Here is the formula on the billing tab.

=VLOOKUP(A4,'STATUS '!A3:I5,6)

Thanks for your help.

[Niceguy60]

Just realized I attached the wrong spreadsheet. I'm still working through this and will repost spreadsheet if I can't figure it out.

Thanks

[Niceguy60]

I think VLOOKUP is what I'm looking for but my problem is the Job # on the "Status" spreadsheet. The job # will not remain in the same cell. It's constantly changing when new items are added or shifted. If I am to add a job number to the "billing" spreadsheet, I need it to find the corresponding number on the "Status" spreadsheet then fill in the data requested. Is this possible? It seems like the VLOOKUP is almost doing it but there should be another step to the formula.


Any thoughts are appreciated, I have attached a better example for reference.

Thanks

[DannyJ]

Hi Niceguy,

Sorry for the delay. The formula works fine for me. Can you please replicate the problem and then post it.

I have added an extra 14 items in and it still says carpet.

EDIT: One suggestion I would make is to use this formula instead,

=VLOOKUP(A4,Status!A:I,2)

This will check all of column A so that it doesn't ever drop out of range.

Thanks

Danny

[Niceguy60]

Danny,
Hate to keep bothering you with this but the data is still not pulling accurately. I added another sample spreadsheet. Pleareference the "Billing" worksheet job numbers 8940 and 4500. The data for these two formulas is incorrect. It's pulling from different cells or not at all.

Thoughts

[DannyJ]

Sorry I was being lazy and hadn't put the FALSE in at the end. It should now work.

Regards

Danny

[Niceguy60]

This is awesome!!! I don't understand the "False" but it worked. Also one last question, if I was to remove the job# from the billing worksheet I get the "#N/A" symbol in the formula field. Is there something I can add to the formula so this will show as blank cell?

Thanks again... Big help

[DannyJ]

The False tells it that it has to be an exact match. I have changed the code to an If(ISERROR. I am not familiar with your version of excel so don't know if you can use IFERROR. If you can then let me know and I will edit the code for you.

=IF(ISERROR(VLOOKUP(A4,Status!A:I,2,FALSE))," ",VLOOKUP(A4,Status!A:I,2,FALSE))

Attached is the spreadsheet with the codes.

[Niceguy60]

Excel Mac 2011
Version 14.1.4

Thanks again