Hello,
It has been a while since working with Excel so I'm a little rusty. I need some assistance with a VLOOKUP formula. I have a spreadsheet with 7 tabs. 6 of the tabs get their data from reports that I extract the data from. Each of those 6 tabs have 3 columns; Name, AcctNbr, and Balance. The 6 tab names are: SurplusBal, ShortageBal, CurrentBal, TotalNewPmt, ShortageAdj, and DeficiencyAdj. I created a 7th tab and named it FinalData. This tab has cloumns named Name, AcctNbr, ShortageBal, CurrentBal, TotalNewPmt, ShortageAdj and DeficiencyAdj. I copied all the names from the Name column in the CurrentBal tab to the Name Column on the FinalData tab. Now I am trying to write formulas on the FinalData tab that will look at the name in the Name column on the FinalData tab and then look at the other tabs to bring over what I need. For example:
I want to look at the name in the Name column on the FinalData tab and then populate the balance amount from the SurplusBal tab into the SurplusBal column in the FinalData tab. If the name doesn't exist in the Name column on the SurplusBal tab then leave the cell on the FinalData tab empty. If the name exits on both tabs then bring the surplus balance over from the SurplusBal tab into the SurplusBal coilumn on the FinalData tab. I have created named ranges for all columns on on the 6 tabs that get the data from the reports. I have been trying to use VLOOKUP to accomplish this but get either N/A or REF errors. Any assistance with this issue will be greatly appreciated. I apologize for this lenghy post.
Thank you,
Dave
Last edited by NBVC; 10-06-2011 at 11:24 AM.
Hi Dave,
I would suggest a sample workbook illustrating your problem would greatly help.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
You can either hard code the sheet name into a VLOOKUP for each column, or you can use the more volatile INDIRECT function to reference the sheetname in your headers indirectly so you can copy the formula down and across.
E.g.
=IFERROR(VLOOKUP($A2,INDIRECT("'"&B$1&"'!A:C"),2,FALSE),"")
this looks up value in A2 of your current sheet (i.e. the Name) and finds it in the sheet that is named as per string in B1 and extract the corresponding value from the second column. If there is no match a blank is returned.
you can copy formula down and across the columns.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Hi Dom,
I have attached a sample spreadsheet. As you'll see there are 17 customers on the FinalData tab; I need to bring over the account number to the AcctNbr field on the FinalData tab (all customers have an AcctNbr) and then I need to populate the remainder of the columns on the FinalData tab where it finds data on the other 6 tabs. If it finds no data then leave the cell on the FinalData tab empty.
Your help is appreciated.
Thank you,
Dave
Some customers have multiple accounts. How do you want to deal with those?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
For the values entered in C2 and copied down and across:
=IFERROR(VLOOKUP($A2,INDIRECT("'"&C$1&"'!A:C"),3,FALSE),"")
Not sure about the account numbers as you don't have a single list of them to search.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Yes, I do need to deal with them as well. Thank you.
You list in column A of Final data, shows one of each customer... how do you want to deal with the AcctNumbers... pick the first one only? or what?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Hi Dom,
I think I made a mistake when creating the sample. My aplolgies. I have attacheed another sample spreadsheet.
Dave
Re: VLOOKUP Question
Hello all.
Thank you for your replies. I was able to figure it out. I had to create my formulas based on account numbers instead of customer name. The account numbers are unique. I also realized that I was not inculding all the data needed in my named ranges. Once I corrected my named ranges and created the formulas based on account number I had the results I needed.
Once again, I apprciate your replies. These forums are awsome!
Regards,
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks