please correct me with this formula.....
IF(AND(Sheet1!L2=B1,Sheet1!G2=A2),(Sheet1!E2),""),IFERROR(INDEX(Sheet1!$E2:$E$1000,MATCH('2016'!$A2,Sheet1!$G$2:$G$1000,0)),"")
t1.PNG
please correct me with this formula.....
IF(AND(Sheet1!L2=B1,Sheet1!G2=A2),(Sheet1!E2),""),IFERROR(INDEX(Sheet1!$E2:$E$1000,MATCH('2016'!$A2,Sheet1!$G$2:$G$1000,0)),"")
t1.PNG
Last edited by mikehk; 02-24-2018 at 11:50 AM.
Try:
=IFERROR(LOOKUP(2,1/(Sheet1!$G$2:$G$1000=$A2)/(Sheet1!$L$2:$L$1000=B$1),Sheet1!$E2:$E$1000),"")
It works on the first cell but doesn't on the other cells...please see the screenshot.
Thanks.
T2.PNG
Try this
Remember to ctrl shift enter after inserting the formula as this is array.
=IFERROR(INDEX(Sheet1!$E$2:$E$1000,MATCH('2016'!$A2&'2016'!$B$1,Sheet1!$G$2:$G$1000&Sheet1!$L$2:$L$1000,0)),"")
Nice..this works,thanks ....but when I copy the formula to other cells it doesn't work
=IFERROR(INDEX(Sheet1!$E$2:$E$1000,MATCH('2016'!$A2&'2016'!$B$1,Sheet1!$H$2:$H$1000&Sheet1!$L$2:$L$1000,0)),"")
(the only change made is bold above)
=IFERROR(INDEX(Sheet1!$E$2:$E$1000,MATCH('2016'!$A2&'2016'!$D$1,Sheet1!$G$2:$G$1000&Sheet1!$L$2:$L$1000,0)),"")
this works...though I made some changes (in bold)
=IFERROR(INDEX(Sheet1!$E$2:$E$1000,MATCH('2016'!$A2&'2016'!$F$1,Sheet1!$G$2:$G$1000&Sheet1!$L$2:$L$1000,0)),"")
doesn't work
The cells where your formula works....I see { } , apparently they are not in the cells where the formula is not working, could
be because of this?
tr.PNG
hmm, could be your the issue of the name A1 , A2 , A3 etc as they are merging cell. can you attach a sample.
oh just a guess, when you edited the bold part, did you ctrl shift enter?
yup...I did ctrl shift enter in all
I changed names from A1, A2 ...to XL12, XL13.....
sample sheet attached herewith, please check.
thanks.
try this in sheet 2016 cell C2, easier way for you without editing anything.
=IFERROR(INDEX(Sheet1!$E$2:$E$1000,MATCH('2016'!$A2&'2016'!B$1,Sheet1!$H$2:$H$1000&Sheet1!$L$2:$L$1000,0)),"")
Ctrl shift enter
then just drag the formula horizontally to the column you wan to stop, and then drag vertically down all. see if it works.
you can just clear the formulas in those column which is not needed after that.
ok i open up your attached and realize your cell G2 is not in array. you might have miss the ctrl shift enter.
Last edited by finalazy; 02-24-2018 at 03:09 AM.
Got it working....selected cell and tapped F2, then ctrl shift enter
Thank you so much for your help
you are welcome =)
a request if this is possible to do .....I would like to fill up the name in the cells of start date, not sure if I am able to explain well...please see the screenshot, thanks.
example
start date 1/1/16
end date 4/1/16....
so I would like the name in cells C2:C4 (1/1/16 to 3/1/16)
d1.PNG
you will need another formula for that which i feel will be kind of inconsistent. It will be better if you have another 2 more column for each row 1 to show the start and end date.
Last edited by AliGW; 02-24-2018 at 03:58 AM. Reason: Unnecessary quotation removed.
Hmm....let me think ....thank you once again for your help. Highly appreciate.
Have a great weekend.
ok I think i get what you want to do, you can try this.
=IF(AND(IFERROR(INDEX(Sheet1!$E$2:$E$1000,MATCH('2016'!$A2&'2016'!$B$1,Sheet1!$G$2:$G$1000&Sheet1!$L$2:$L$1000,0)),"")="",B2=""),A2,IFERROR(INDEX(Sheet1!$E$2:$E$1000,MATCH('2016'!$A2&'2016'!$B$1,Sheet1!$G$2:$G$1000&Sheet1!$L$2:$L$1000,0)),""))
CTRL SHIFT ENTER
After seeing your image that you attached, I think the column B is your end date formula and column C is your start date formula. Use the formula i provided about and paste on column C2 and drag down. remember to ctrl shift enter
Last edited by finalazy; 02-24-2018 at 04:17 AM.
That's correct....start date is in column C and end date in column B.
I copy pasted your formula ....it works but get numbers instead of name, please see the
attached screenshot.
Format of all cells are General
S1.PNG
You need to format the cell to custom date which is same as column A.
Got it....
what about the dates that show up? can't those be hidden?
s2.PNG
Hmm, I'm not sure how to remove those dates within 1 formula , it will be doable if there's additional column added.
Maybe others are able to help with this 1.
And actually Phuocam provided you the formula is also working. Missing out on the $ for it to work.
=IFERROR(LOOKUP(2,1/(Sheet1!$G$2:$G$1000=$A2)/(Sheet1!$L$2:$L$1000=B$1),Sheet1!$E$2:$E$1000),"")
His is better since it's ordinary formula(not array, don't need the use of ctrl shift enter)
Last edited by finalazy; 02-24-2018 at 07:43 AM.
ok come to think of it, it should show the dates that is within the range of end date. so maybe you can do it this way to remove those dates. The formula are quite messy now so i amended abit.
Paste this in cell D2
CTRL SHIFT ENTERPlease Login or Register to view this content.
Paste this in cell E2
CTRL SHIFT ENTERPlease Login or Register to view this content.
Just copy both the formula and paste to your needs.
See if this helps. And yes please change your thread titles just as AliGW stated.
Last edited by finalazy; 02-24-2018 at 10:09 AM.
Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!
- Use concise, accurate thread titles.
- Your post title should describe your problem, not your anticipated solution.
- Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
- Responding to a request to change your thread title by doing so is mandatory.
To change a title go to your first post, click EDIT then Go Advanced and change your title.
No help to be offered, please, until the OP complies with this request.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I have changed the thread title, hope that's okay, if not, please tell me what
should I rename it to...thank you.
No, it is not OK, sorry. You need to explain the PROBLEM in your title.
Hope the title is okay now, if not please suggest me what should I rename it to...thank you for your help.
Hi mikehk,
It should be fine now. Have you tested my above method?
This formula nearly works....I copy pasted in cell E2 as you said....but the first name now show up as a date
Please see the screenshot
=IF(AND(IFERROR(INDEX(Sheet1!$E$2:$E$1000,MATCH('2016'!$A3&'2016'!D$1,Sheet1!$G$2:$G$1000&Sheet1!$L$2:$L$1000,0)),"")="",D3="",MAX(INDEX((Sheet1!$L$2:$L$1000=D$1)*Sheet1!$H$2:$H$1000,0))>=$A3,AGGREGATE(15,6,Sheet1!$G$2:$G$1000/(Sheet1!$L$2:$L$1000=D$1),1)<=$A3),$A3,IFERROR(INDEX(Sheet1!$E$2:$E$1000,MATCH('2016'!$A3&'2016'!D$1,Sheet1!$G$2:$G$1000&Sheet1!$L$2:$L$1000,0)),""))
a1.PNG
My bad. The formula i provided for you is for cell E3,
try this again at cell E2
CTRL SHIFT ENTERPlease Login or Register to view this content.
please see the screenshot, the dates in between show up
s4.PNG
It shows blank cells in the beginning, which is exactly how it should be ,
hope same can be done with dates in between, please see the screenshot.
thanks.
S5.PNG
yea I noticed that as well. This is really tough. I tried a few ways but to no avail. Even adding helper column I also can't get it to work the way you wanted. Hopefully others can help you out. =(
No worries....thank you soooo much, highly appreciate your efforts.
Another alternative to make it work is by apply conditional formatting to fill color
in those cells.
I will open a separate thread for that.
Thank you once again.
Have a great weekend.
Best regards
No worries....thank you soooo much, highly appreciate your efforts.
Another alternative to make it work is by apply conditional formatting to fill color
in those cells.
I will open a separate thread for that.
Thank you once again.
Have a great weekend.
Best regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks