Excel Search Help
I have an excel spreadsheet and was wondering what the best way to do a certain task.
Sheet 1 is a payment request template.
Sheet 2 is a SQL query return
I would like to be able to enter a reference number in Sheet 1, which will then bring back any entries (usually 1 but occasionally could have more entries associated with a reference number) from Sheet 2.
Then, the entries that is returned from Sheet 2, will be listed with 1 entry per row in the same column.
Thank you in advance.
Sheet 1 is a payment request template.
Sheet 2 is a SQL query return
I would like to be able to enter a reference number in Sheet 1, which will then bring back any entries (usually 1 but occasionally could have more entries associated with a reference number) from Sheet 2.
Then, the entries that is returned from Sheet 2, will be listed with 1 entry per row in the same column.
Thank you in advance.
Sorry. I didn't think it was so difficult. I was trying with ISNA and trying to figure out if gathering data into one cell delimited by commas, then split into columns for each value, then pivot table or something. Was starting to look like this...
and Sheet2
But take a look at this fairly comprehensive help about doing this various ways. Please click on the links in table of contents also.
http://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/
and Sheet2
But take a look at this fairly comprehensive help about doing this various ways. Please click on the links in table of contents also.
http://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/
It depends on what you have on Sheet2 i.e. how many columns of data corresponding to a reference number you want to fetch from Sheet2 into Sheet1.
There are several ways to get this done and the best way depends upon how many rows of data you have on Sheet2. If the data on Sheet2 is a small set of data, you may go for a formula based solution otherwise you may go for something known as Advanced Data Filter associated with a VBA code to return the data corresponding to a specific reference number and I assume this would be an easiest approach to achieve this.
Would be better if you can upload a sample workbook.
There are several ways to get this done and the best way depends upon how many rows of data you have on Sheet2. If the data on Sheet2 is a small set of data, you may go for a formula based solution otherwise you may go for something known as Advanced Data Filter associated with a VBA code to return the data corresponding to a specific reference number and I assume this would be an easiest approach to achieve this.
Would be better if you can upload a sample workbook.
Hi sktneer and all.
Did you not like the methods in that last link at get-digital-help.com?
Did you not like the methods in that last link at get-digital-help.com?
@Christopher
I actually didn't check that link.
I am not still sure about the asker's requirement completely, that's why I requested to provide some more information. Asker has asked a very general question about which method will best suit to perform a task and that's difficult to answer unless you have all the required details. If you read my reply, you will know what I am trying to know from the asker.
I actually didn't check that link.
I am not still sure about the asker's requirement completely, that's why I requested to provide some more information. Asker has asked a very general question about which method will best suit to perform a task and that's difficult to answer unless you have all the required details. If you read my reply, you will know what I am trying to know from the asker.
ASKER
Sorry for the delay in reply.
I have a check request form where I usually input the reference number manually in AI1 to AI5. It will then bring me back information on rows 7 - 12. However, the way I do now, I do a search on the invoice number manually and then input AI1, etc. Since I have the search data in tab 2, I was wondering if I enter instead the invoice number in F8, it will bring me the reference number(s) back into AI1 to AI5. Of course, in tab 2, I will have to move column G to the front to do a vlookup.
I attach a sample of the file.
Thank you in advance.
--orbitonline.com-Private-NSL-HomeD.xlsx
I have a check request form where I usually input the reference number manually in AI1 to AI5. It will then bring me back information on rows 7 - 12. However, the way I do now, I do a search on the invoice number manually and then input AI1, etc. Since I have the search data in tab 2, I was wondering if I enter instead the invoice number in F8, it will bring me the reference number(s) back into AI1 to AI5. Of course, in tab 2, I will have to move column G to the front to do a vlookup.
I attach a sample of the file.
Thank you in advance.
--orbitonline.com-Private-NSL-HomeD.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I apologize for the delay in reply, was off work sick.
That is almost close to what I am looking for, Chris. I am sorry, if I was a little confusing in my explanation.
Normally, I will enter the supplier invoice number in AI9, and it brings me back the bill of lading in F8. I was wondering if I did it the other way round. If I entered the bill of lading in F8, it will bring me back the supplier invoice no. If it is only one bill of lading to one supplier invoice number, that would be easy. But sometimes I have one bill of lading to multiple supplier invoice number. How do I bring back multiple invoice numbers in AI9 - AI13?
Thank you in advance.
That is almost close to what I am looking for, Chris. I am sorry, if I was a little confusing in my explanation.
Normally, I will enter the supplier invoice number in AI9, and it brings me back the bill of lading in F8. I was wondering if I did it the other way round. If I entered the bill of lading in F8, it will bring me back the supplier invoice no. If it is only one bill of lading to one supplier invoice number, that would be easy. But sometimes I have one bill of lading to multiple supplier invoice number. How do I bring back multiple invoice numbers in AI9 - AI13?
Thank you in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Appreciate all the help. You have all pointed me in the right direction.
Thank you.
Thank you.
Hi again people.
For the sake of the question I thought I would mention this could be done with Pivot Tables. They're useful and sometimes simple to use for table data.
Attached is a PDF I made of what I did to the original file step by step. Also attached is the modified workbook.
Hope it's fun!!
EEExcel2013Pivot.pdf
ChrisNewVersionCopy-of---orbitonlin.xlsx
For the sake of the question I thought I would mention this could be done with Pivot Tables. They're useful and sometimes simple to use for table data.
Attached is a PDF I made of what I did to the original file step by step. Also attached is the modified workbook.
Hope it's fun!!
EEExcel2013Pivot.pdf
ChrisNewVersionCopy-of---orbitonlin.xlsx
This example is from Excel 2013 help file which is attached as pdf. The video you see in the beginning of the PDF is pretty good and from this link below.
https://support.office.com/en-US/Article/VLOOKUP-When-and-how-to-use-it-9a86157a-5542-4148-a536-724823014785
ExcelVlookup.pdf