I have a calendar where I want to bring over the CODE (Points) for a specified employee. See Attached: Employee is selected on the Calendar and the CODE from the Data Tab are brought over. Right now I am getting just the number, I need it to be the CODE. I was using a sumif but now need another option.
Dynamic Range names:
ActCode =Data!$E$2:INDEX(Data!$E:$E,LastRec)
DATA =Data!$E$2:INDEX(Data!$A:$A,LastRec)
Employee_Names =Data!$A$2:$A$8
FileNum =Data!$B$2:INDEX(Data!$B:$B,LastRec)
LastRec =COUNTA(Data!$A:$A)
Occur_Date =Data!$C$2:INDEX(Data!$C:$C,LastRec)
Points =Data!$D$2:INDEX(Data!$D:$D,LastRec)
Array formula:
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Please Login or Register to view this content.
Last edited by protonLeah; 03-11-2022 at 04:06 PM.
Ben Van Johnson
Thank you for your help. In the list of formulas is this one correct? DATA =Data!$E$2:INDEX(Data!$A:$A,LastRec) The first part looking at E and the Index at A??
I am trying to input the formula for Cell E9 and here is what I have (see screenshot), is it correct?
WRT #3:I just defined the range from top right to bottom left. It's the same asWRT #4:
=INDEX(Data!$A:$A,LastRec):Data!$E$2, i.e. bottom left to top rightThe formula is correct, but I should have wrapped it in IFERROR() to handle cases were there is no match in the database:A9:
Please Login or Register to view this content.
Last edited by protonLeah; 03-14-2022 at 06:11 PM.
On the WRT #3:
I just defined the range from top right to bottom left. It's the same as
=INDEX(Data!$A:$A,LastRec):Data!$E$2, i.e. bottom left to top right
I want to make sure I understand, Data!$E$2 is right for the first part looking at column A??
My formula is picking up the wrong data. In the attached Employee 1 should not have anything for 1/29/22, that date is only for Employee 2. But when I change the Employee to Employee 2, the entry for 1/29/22 goes away.
If the data were static and unchanging I would have defined the name "DATA" as referring to A2:E9. However, I assumed that the number of rows is variable, but that the number of columns is fixed at five.
One way to define the variable-row range is with the OFFSET function as:
=OFFSET(A2,0,0,COUNTA(A:A)-1, 5)
Another option, using INDEX/MATCH and specifying the opposite corners of the range. The top corners are fixed, in this case, but the bottom ones are variable by row. There are several options:
Top left to bottom right:
=Data!$A$2:INDEX(Data!$1:$1048576,COUNTA(Data!$A:$A), 5)
Top right (E2) to bottom left (INDEX(Data!$A:$A,LastRec):
=Data!$E$2:INDEX(Data!$A:$A,LastRec)
etc...
Last edited by protonLeah; 03-15-2022 at 05:25 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks