I have 2 workbooks that I am working on. One workbook has a list of instructions based on an order number. It may have up to 30 rows and 6 columns of instructions. The 2nd workbook I am trying to create, I would like to type in the order number and have the rows and columns fill out below. I have used VLOOKUP, however, this is my first attempt at INDEX. I would appreciate any help. Thanks.
please attach sample of workbook
1. Thank those who have helped you by clicking the Star * below the post.
2. Please mark your post [SOLVED] if it has been answered satisfactorily.
Sincerely,
Farid
Take a look at this example which uses an Array Formula to index name address etc details from the larger table by putting in a partial key of the persons name. This is similar to what your looking for.
Hi, welcome to the forum
perhaps something like this?
header1 header2 header3 header4 header5 header1 header2 header3 header4 header5 aa aa b cc dd ee
G2= your search criteria
H2=IF($G2="","",INDEX($B:$E,MATCH($G2,$A:$A,0),MATCH(H$1,$B$1:$E$1,0)))
copied down and across as needed
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thanks for everyone's response. I still seem to be having trouble so I have uploaded my two workbooks. Please remember that the my formula must have the following path
'F:\INVENTORY\FEB 2016\[ROUTING STEP TEST.XLS]ROUTING STEP
Once again thanks for all your help.
Ive modified your April formulae as attached. It will now replicate any records found in 'routing step' that match the WO# entered in D1. Col A contains an Array formula that calculates the row numbers for the entered WO#, so that this does not need to be calculated separately for each row entry. This, and the counter in E1 helps to handle a large amount of data quickly.Originally Posted by 34_CHEVY
Looks great. Having a little trouble copying it to the location and changing the formulas to see the actual sheet. I believe I can get it from here. I'm going to leave the thread open till I can get it figured out. But thanks so much.
Yes - I had problems when I tested what you would need to do. The problem is it remembers the temporary location that it was stored in when I uploaded it, and this isn't valid. I tried to stick with your two files to get close to what you need.
The way I fixed the problems was by saving both downloaded files to my hard drive followed by a restart. Then I opened the destination file (Aprils Traveler) from my hard drive, and clicked Data Tab\Edit links. I picked the option to Change Source (Routing Step Test) and browsed to Routing Step Test on my drive. Initially this creates errors, as we haven't opened the Routing Step Test file yet. After you have changed the links, open RST.xls and the errors will disappear.
Try that and if it doesn't work, I'll have to do an upload with both sheets in one workbook and you will need to work out how to move the formulae into the other workbook. I should be able to help you here if needed.
Thanks for the tips. I tried something similar and got partial success. I'll use your steps 1st and if not I'll create a new "April sheet" using your formulas. Thanks again.
Tried recreating new sheet with your formulas. I got "#Value" error referring to a circular reference pointing from Column A to each of the formulas using the Error Tracer. I have uploaded a Word document where I recreated your formulas with my location.
34_CHEVY
Your formulae are all correct when compared with mine, assuming that the ROUTING STEP TEST.xls file exists at the location F:\INVENTORY\FEB 2016\.
Looking at your list of formulae in the word document, I noticed that the (Col A) formula that goes in A3, (listed second in the Word Document) does not have the curly braces { } around it to signify its array
formula status. If this is missing, then this will normally cause the #VALUE error.
If we can concentrate on getting the correct results returned in Cells A3 and B3 only to start with, we can then copy B3 across C3:G3 and then copy A3:G3 down for as many returned records as necessary.
Check the formula in A3 for curly braces. If they are missing, Select Cell A3, and then click in the Formula Bar somewhere within the formula shown. This puts the cell in Edit mode. Then:
Press Control + Shift + Enter to insert the braces. Double check to make sure the braces are in place. The result should now display the Row number of the first record to be returned. Note that any time you edit
an array formula Excel removes the braces, so you always need to end with C+S+E not just ENTER. You can however copy array formulae to other cells as normal, complete with braces.
The formula in B3 is correct per the Word Doc, and is not an array type so no braces needed. It is dependent on A3 in order to return a value. When A3 and B3 are OK you can copy/paste as above to complete the
extract table.
Last edited by Hercules1946; 03-16-2016 at 04:21 PM.
Did you resolve this?
Unfortunately no. I placed the { per your instructions, but still gave a tracer error linked to column A and a circular reference. I've not had much time the last day or so to work on it. I still appreciate any input you might have. I believe we are so close. Thanks
Im sorry to hear that, but I hope that we can resolve it soon for you.
Can you post a sample of your latest workbooks, illustrating the errors you are getting? Also, were you able to get the workbooks to work that I attached in #6 ?
Sorry for the delay. We had several people out last week for "Spring Break". Please see the attached email after modifying the formula to look at the destination spreadsheet.
Thank you once again for all your help. Never did get it to work in 2 workbooks, however, I created a second sheet and it worked great. That should satisfy my needs.
It is difficult (but not impossible) with two workbooks. The best way is to start with both workbooks open, so that you can build the formula without worrying about the paths to where the saved books are. If you then save the books to their location, Excel will automatically adjust the formulae to show the correct path. Provided the books remain at these locations, you have a set of permanent links that can be updated to cater for changes made to the book(s).
Im glad you found a workable solution. Do come back if you need further help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks