automatic updates vlookup and

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Automatic updates with Vlookup and incorrect information coming

Automatic updates with Vlookup and incorrect information coming

resolvedResolved · High Priority · Version 2016

Dawn has attended:
Excel Intermediate course
Project Introduction course

Automatic updates with Vlookup and incorrect information coming

Hi All

Hope you can help.

I have used the vlookup with the iferror in my work book. The vlookup is looking for dates in one column and text in another.

I am getting dates coming across when there are no dates in the source work sheet? I have formatted the column to date via format settings, but this doesn't seem to work. The iferror has removed all n/a's, but left incorrect dates. I have no idea where these dates are coming from as they do not appear on the source sheet.

In the second column which is text and has a drop down menu in the source sheet, only one word is found the other words come across as 0, I tried formatting the column for text and then general, but it didn't work. Again the iferror has removed the n/a's.

How can I have updates on the source sheet automatically update on the other sheet, eg if I put a new date in a new cell keeping in range of the vlookup can it update the other sheet automatically?

Thanks for your time.

Stay safe
Dawn



RE: Automatic updates with Vlookup and incorrect information com

Hi Dawn,

Thank you for the forum question.

Can I please ask you to copy your Iferror and Vlookup and paste it here thanks. I will need to see your functions to see if they are right.


To automatically update when new records are added use the table tool. I have found a good example (see link below)

https://www.contextures.com/xlExcelTable01.html

If you want me to look at your file you can send it to:

info@stl-training.co.uk


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Automatic updates with Vlookup and incorrect information com

Hi Jen

Thanks for your response, here is my function =IFERROR(VLOOKUP([@[resource_id]],Enquiries!A:O,9,FALSE)," "), what I'm find is it brings across the wrong data in column 9, there is a drop down menu in the source sheet, should this affect the outcome? other cells are correct just random ones and its the same for the information across those particular rows.

Many thanks
Dawn

RE: Automatic updates with Vlookup and incorrect information com


Hi Dawn,

I cannot see any errors in your functions and I can see that you already use tables. One thing I could see was that you reference column A to O all way down to row 1048576 A:O. If your workbook takes time to open and is slow, this can be the reason, but it is not the reason why the Vlookup doesn't work.

Is it possible to send me a sample file. If it is confidential data delete the information but just keep the columns you use for the lookup.

There is nothing in your explanation which should make the Vlookup behave strange.


info@stl-training.co.uk




Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector


 

Excel tip:

Hide data in Excel Worksheets

Let's say you have some data in cell 'C5' you would like to hide from the casual viewer.

Click cell 'C5' to select it.

Click the 'Format' menu, select 'Cells'. When the 'Format Cells' dialogue box opens, click the 'Numbers' tab (if necessary), then select 'Custom' from the 'Category' list.

Double-click the 'Type' entry box and type three semi-colons: ";;;"

Click 'OK' to close the dialogue box and accept the new formatting.

The data in cell 'C5' disappears. It's still there and will work in calculations, but it isn't visible.

If you need to check the data, just click the blank cell and the contents appear in the 'Formula bar'.

View all Excel hints and tips


Server loaded in 0.13 secs.