I need to auto populate cells in one sheet with data from another sheet
APFT_HTWT_ALPHA ROSTER_ROLL UP.xls
I need help, I am using Excell 2007 on a windows 7 OS. What I want to do is populate cells H and J on the sheet named Roll up Nov13 with data from the sheet named AR 600-9 Table 3-1 & 3-2 based on the data that is in cells D,Eand F on the Roll up Nov 13 sheet. any help would be appreciated. I have attached the woorkbook.
Thanks
Hi and welcome to the forum
Im not sure what the difference would be between H and J, but try this.
1st, put the lower of the age ranges in B28:H28, then in J, copied down...
=INDEX(INDIRECT($E2),MATCH($F2,'AR 600-9 Table 3-1 & 3-2'!$A$4:$A$27,0),MATCH(D2,'AR 600-9 Table 3-1 & 3-2'!$B$28:$E$28,1))
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
I see what you are saying, the difference between H and J is H is max weight from AR 600-9 Table 3-1 & 3-2 based on male or female and there height and age. J is max percentage from AR 600-9 Table 3-1 & 3-2 based on male or female and age.
Doing what you suggested returns a #REF!
any suggestions?
Darn, my bad sorryI forgot to tell you to create range names on AR sheet for Male and Female
Male range ='AR 600-9 Table 3-1 & 3-2'!$B$4:$E$27
Female range ='AR 600-9 Table 3-1 & 3-2'!$F$4:$I$27
That works, but it gets the value one row under the one it should be from the AR table.
I got it to work, Just had to change a A4 to A5, then it pulled the right info. Can you help me to get it to draw the Allowable body fat percentage from the AR sheet to the Roll up sheet column J and have the cell turn red if its greater than the value in column I.
to pull in the data in J, use this, copied down...
=INDEX(IF(E2="Male",'AR 600-9 Table 3-1 & 3-2'!$K$5:$N$5,'AR 600-9 Table 3-1 & 3-2'!$O$5:$R$5),MATCH(D2,'AR 600-9 Table 3-1 & 3-2'!$K$6:$N$6,1))
(In K6:N6, put in the lower ranges, as before)
In column I, you only have 3 values, but to change color, you need to use Conditional Formatting. Take a look and see if you can work it out yourself, if not, give me a shout and I will help you
I got it all to work as I wanted. Thanks for all the help.
Happy to help and thanks for the feedback
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks