Hello everyone. This is my first time on this site. Before you all scold me, I did try searching the forum for information before starting this thread. I found similar posts but not exactly what I am trying to do. That being said, I am about as green as it gets when it comes to excel, lol. I am able to do minor commands/formulas in single workbooks. Hopefully, you guys can give me some input and save me some time from staring blankly at the screen scratching my head. I will try to explain this as clearly as possible:
We are trying to better organize our Proposal Costs vs. Actual Costs. We have SpreadSheet 'A', which contains our complete hours (proposed and actual) for each project. This sheet is laid out as such: COLUMN "A": Job Numbers; ROW "1": Column Titles; ROW "2": Project #1 Proposal Hours; ROW "3": Project #1 Actual Hours; ROW "4": Project #2 Proposal Hours; ROW "5": Project #2 Actual Hours; ROW "6": Project #3 Proposal Hours; ROW "7": Project #3 Actual Hours; etc., etc.... At the end of each of these rows, we have columns 'AI' (shows the total amount of proposal hours; formula format) & 'AJ' (shows total amount of actual hours; formula format). This spreadsheet is setup good and we do not have problems with this.
Now, on SpreadSheet 'B', there are 3 cells on this spreadsheet that I want to reference to SpreadSheet 'A'. I want to be able to reference Column "A" from SpreadSheet 'A' above by typing in a job number into a certain cell. When I type the job number into the cell I want to use, I want it to pull in the information from the associated job number and put 1.) the contents of column 'AI' (total amount of proposed hours) into 1 cell (the actual contents, NOT THE FORMULA SHOWN IN THIS CELL) & 2.) the contents of column 'AJ' (total amount of actual hours) into another cell (the actual contents, NOT THE FORMULA SHOWN IN THIS CELL). Please note that the job number I am trying to reference in Column "A" may be in a different row than the ACTUAL HOURS cell. IE: Project #1 Job Number will be in cell 'A-2', Project #1 Total Proposal Hours will be in 'AI-2' & Project #1 Total Actual Hours will be in cell 'AJ-3'. Does it make a difference that these are in different columns and/or rows? (Project #2 info would be: Job No. cell 'A-4'; Prop. Hours cell 'AI-4'; Actual Hours cell 'AJ-5', etc., etc...)
So, one, is this possible? And, two, how would someone like me go about getting this to work, lol? Again, I hope this makes sense to some of you. If you need additional information or anything please let me know. I will try to get answers back to you ASAP. Thanks in advance for your help.
P.S., I was able to successfully reference the file I am trying to get to by using "=[worksheet.xlsx]sheet1!a2-a1001". When I type that in though, it automatically inserts the contents of cell A2 of the referenced file, it does not give me the option of choosing or anything.
Just by reading your post, i feel you need vlookup. However, i will be able to give a better solution if you attach a sample workbook.
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Arlu, I tried using Vlookup and I could not figure it out. I may be typing the code wrong... Attached are 2 files. Sample Sheet 'A' would be the master sheet I guess. Sample Sheet 'B' is the file I want to pull information into from Sheet 'A'. Sample Sheet 'B' explains what I am trying to do also.
SAMPLE SHEET A.xlsx
SAMPLE SHEET B.xlsx
(***NOTE*** Cell A1 of Sample Sheet 'A' should contain "JOB NUMBER". Not "fc" as shown... Typo.)
Anyone have any clue how to do this or if it is possible?
It is possible, but i am not that well-versed with formulae to give you an answer. I will put this forward to our experts.
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
For a VLOOKUP formula to work, the strings need to match. So I made the strings on the two sheets match and now it works fine.
D6: =VLOOKUP($B$4&B6, '[SAMPLE SHEET A.xlsx]Sheet1'!$A:$H, 8, 0)
D7: =VLOOKUP($B$4&B7, '[SAMPLE SHEET A.xlsx]Sheet1'!$A:$I, 9, 0)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Assuming that the start of the cell always matches the job number and that you have the proposed hours in the row above the actual hours, then
D6: =INDEX('[SAMPLE SHEET A.xlsx]Sheet1'!$H:$H,MATCH($B$4&"*",'[SAMPLE SHEET A.xlsx]Sheet1'!$A:$A,0))
D7: =INDEX('[SAMPLE SHEET A.xlsx]Sheet1'!$I:$I,MATCH($B$4&"*",'[SAMPLE SHEET A.xlsx]Sheet1'!$A:$A,0)+1)
If the layout is not that fixed, we'll need a better idea of what we can match on. For example, if the text is always 'job_no' followed by ' (Prop Hrs.)' or '(Actual Hours)' then we could match on that.
Good luck.
D6: =INDEX([SampleA.xlsx]Sheet1!$A$1:$I$3,MATCH($B$4&"*",[SampleA.xlsx]Sheet1!$A:$A,0)+ROW($B6)-6,ROW($B6)+2)
or
D6: =INDEX([SampleA.xlsx]Sheet1!$A$1:$I$3,MATCH($B$4&B6,[SampleA.xlsx]Sheet1!$A:$A,0),ROW($B6)+2)
Edit (having seen JB's post): or:
D6: =VLOOKUP($B$4&B6,[SampleA.xlsx]Sheet1!$A:$I,ROW($B6)+2,0)
Either formula copied down to cell D7.
Regards, TMS
Last edited by TMShucks; 01-11-2012 at 05:05 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks