Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

convert VLOOKUP to Excel macro


Is it possible to convert this to a Excel macro?

Use a vlookup formula:

=VLOOKUP(B2,'[other workbook.xlsx]Sheet1'!$A$1:$B$1000,2,false)
for example, assuming department ids in column B in the employee workbook, and the source data in columns A and B in the other workbook.
0
barnescs
Asked:
barnescs
  • 10
  • 9
1 Solution
 
Haris DjulicCommented:
0
 
Rob HensonIT & Database AssistantCommented:
Depends what you mean by 'Convert to a macro'!!

Do you mean create a macro that will populate cells with a VLOOKUP formula or do you mean create a macro that will return the same result as a VLOOKUP formula?

Please clarify.

Thanks
Rob H
0
 
barnescsAuthor Commented:
A macro that will return the same result as a VLOOKUP formula.  i have a worksheet with 2 VLOOKUPs on it and it would be easier for the users to have a macro to use instead of the 2 VLOOKUP formulas.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rob HensonIT & Database AssistantCommented:
Seems a bit overkill to have a macro that will replace 2 formulas.

Can you expand a bit as to why the 2 formulas are not suitable? Maybe we can find another way around it.

Thanks
Rob H
0
 
barnescsAuthor Commented:
i have users that run various reports that contain employee's name and department.  The users want to take the data that they have and then by some means match the department number on their report to a list that contains dept. numbers, department descriptions, and supervisors names.  I have written 2 VLOOKUP formulas to do this; one pulls department description, and the other the supervisor.  I was trying to make things easier for the users and thought a macro would be easier than 2 VLOOKUP formulas.
0
 
Rob HensonIT & Database AssistantCommented:
I take it they have a list of Employees' names and departments against which they are populating the 2 formulas.

Assuming the size of the list varies from report to report, the number of formula required for the whole list would be variable.

Do the users already have one copy of the formula which they are currently copying against all rows in their particular report? Is this the bit that needs making easier?

Thanks
Rob H
0
 
barnescsAuthor Commented:
The reports can vary in size from a couple of lines to over 100 lines.  This is a new project that i have been given so no, the users do not already have one copy of the formula.  I am creating this from scratch and with the level of management that will be using this, i need for it to be as simple as possible.
0
 
Rob HensonIT & Database AssistantCommented:
How will the users get the reports they require?

Will they download the data themselves into a new workbook? If so how are you intending to make sure the macro/formula is populated in the new workbook?

By "already have the formula" I meant will there be one copy of the formula in the workbook containing their data which will need copying against all lines.

Thanks
Rob H
0
 
barnescsAuthor Commented:
the users will be downloading to excel reports from various programs.  I intended to set up a workbook for them with them always copying their data into the first sheet and then the formulas/macros would be on the 2nd sheet.  They could then copy them over to the first sheet and have the information that they are needing.
0
 
Rob HensonIT & Database AssistantCommented:
So the plan is:

You will prepare a workbook for the Management User

That workbook will have:
  Sheet1 for their data which they will download and paste into the sheet
  Sheet2 previously prepared VLOOKUP formulas for copying into Sheet1

You want to make sure that the formulas previously prepared on Sheet2 don't get destroyed and are copied against sufficient lines on Sheet1 for the number of rows of data downloaded. Manual copy and paste would be an option but a click of a button to do it for them would be preferred.

Does that sound correct?

Thanks
Rob H
0
 
barnescsAuthor Commented:
yes, this sounds correct  :-)
0
 
Rob HensonIT & Database AssistantCommented:
How about making use of a little Excel trick?

In the header row (assuming row 1) of Sheet1 I assume you will have column 1 "Name", column 2 "Department" or something similar.

In columns 3 & 4, or where the lookup formluas are going, amend the formula to include an IF statement looking at the contents of the header cells in that row:

=IF(A1="Name","Header",VLOOKUP(B1,'[other workbook.xlsx]Sheet1'!$A$1:$B$1000,2,false))

When the user has then populated their data, if they hover the mouse over the bottom right hand corner of the cell it will change to a + sign. Double click at this point and it will copy the contents of that cell down until it reaches what it thinks to be the end of the data, assumes the data in one continuous block.

As you have 2 formulas to copy down, highlight both cells and double click the bottom right of the right hand one, this will copy both down.

Basically, in the header row the IF statement will detect the header "Name" and return the appropriate header for that row otherwise it will do the lookup.

The header row cells can then be protected so that the user cannot overwrite them with the new downloaded data.

Thanks
Rob H
0
 
barnescsAuthor Commented:
With some of the reports having different formats could =IF(A1="Name","Header",VLOOKUP(B1,'[other workbook.xlsx]Sheet1'!$A$1:$B$1000,2,false))
be changed to a range say =IF(A1:A5="Name","Header"...........?
0
 
Rob HensonIT & Database AssistantCommented:
If the headers will always be in row 1 then:

=IF(ROW()=1,"Header",VLOOKUP(B1...

However, will the lookup values be in the same column for each different report format? Column B for the example formula.

Thanks
Rob H
0
 
Rob HensonIT & Database AssistantCommented:
Sorry, think I might have misread that question. For example, the download might have rows of spurious data above the headers eg:

Date
Username
Report title
Name             Department           LOOKUP Result = Header
Data                Data                      Result = Info
Data                Data                      Result = Info

In this case the Headers would be on row 4. The headers are being downloaded with the data so might not fit in with the formulas.

Is this what the scenario could be like?

Thanks
Rob H
0
 
barnescsAuthor Commented:
the headers would always be in row 1 so i'm going to try the =IF(ROW()=1,"Header",VLOOKUP(B1...
to see if that will work.  I have a 12:30 (central time) meeting to go to so i will try this when i get back and let you know how it works.  THANKS very much for all of your help!!!!!!!  :-)
0
 
Rob HensonIT & Database AssistantCommented:
Now 18:30 BST (GMT+1) so will be heading home soon. I will catch up with this in the morning or if I get bored at home this evening, wife and kids away during the week.

Thanks
Rob H
0
 
barnescsAuthor Commented:
sounds good.  i have gotten a little hot project to do so i will have to pull away from this for a little while to pull data together for this.  if nothing else i will be in by 5:30 am tomorrow and will try it out then while it is still nice and quiet.  Thanks for all your help.  You make things so easy to understand.  :-)
0
 
barnescsAuthor Commented:
Everything works GREAT!!!!!  Thanks for all of your help!  :-)  cindy
0
 
Rob HensonIT & Database AssistantCommented:
Glad to be of help!!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now