Hi Folks -
I have a workbook that I"m trying to determine the last row of another sheet (Results!). Right now, I have the formula using 30000 to buy me some time but I'd really love if I could make the formula dynamic to be able to tell me the last row.
Here is my formula:
I tried to use CountA but just returns the column on the current sheet. Is this possible?
Please Login or Register to view this content.
There are instructions at the top of the page explaining how to attach your sample workbook.
A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.
Administrative Note:
Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
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.
@Aligw -
thank you!
Attached is my workbook, please see Tab P12 that have my notes and explanation.
And your Office version is ... ?
@AliGW, 2013. Thank you!
Thanks - please update your forum profile as requested. It says 16 at the moment.
For the formulae in C to G... you can safely use WHOLE COLUMN references, instead of exact ranges, with no adverse effects on performance.
However, the formula in B6 looks hugely complicated. Can you explain what you WANT IT to do.... don't explain the mechanics of the formula, I can see that myself... but it looks like you want a formula that returns one instance of each name from column H of results, with the (possible) added complication that it should also have some values in column I. That's what I mean by explain what you want it to do.
I'm sure it can be hugely simplified!!
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
HI Glenn!
So, basically.
The formula in B3 uses the value in C6 of the Cover Page as a reference to the Results! tab. Using C6 from the Cover Page, it populates that cell with the Name of the person that it pulls from the Results! tab with that same value in Column M for the applicable Week Ending Date (Column F).
THe Week Ending Date in Results are also populated in C5:G5 of the applicable Period Tab (i.e. P1)
All of the following 3 sections do the same thing, the only difference is they use a different value on the Cover Page.
Let me know if that makes sense, thank you!
Please will you update your profile with your current Office version? It needs to show Excel 2013.
Hi Ali, it's now actually 0365. I got a new machine on Friday.
I'll update now.
Oh, fab. Thanks!
I think I 'll have to bow out. The VBA won't work on a 64 bit system... I know nothing about VBA and while the formula in B6 is easily simplified, it knocks out the values in the headers of the columns to the right.
So I can't fiddle with Named Ranges to auto adjust formula ranges to your data... or do anything... without the whole thing falling over.
Glenn -
Not sure what you mean about VBA? FOr now, we don't need to run VBA> WOuld you mind sharing the simplied formula so I can play around with it?
Let me go back again. I had about 15 files open at once!! Who knows what happened!!
As soon as I turn calculations from manual to automatic, everything on P12 errors out...
Let me attach a new version. On Friday I got a new machine and upgrade to 0365. Give me a few minutes and I'll attached the 0365 version. WOuld that help?
Who can tell?? This is the Excel equivalent of the Dark Side of the Farce...
HI Glenn -
Attached is the new template. I also updated the formulas in C:G with whole column references per your suggestion. Let me know is this is better.
Thanks!
This is what I see when I set calcs to automatic!
That's odd. My works fine when toggling between Automatic and Manual.
Can you share the formula you tried? I'll poke around with it and see where it may be breaking.
OK. So, back to basics. This looks like your original, but stripped of all the bat's wings, cat's entrails, etc, etc, that go to make up VBA.
I set up 2 named ranges (CTRL-F3 to view edit) called Employee and Project... that look like this:
=Results!$H$2:INDEX(Results!$H:$H,MATCH("Zzzz",Results!$H:$H))
They will auto-adjust to the correct range to suit your data.
Then I used this formula to return 1 instance of each name in Results that matches the date criterion and the Project name criterion:
=IF(AND($A$1<>"",TODAY()>$A$2),IFERROR(INDEX(Results!H:H,AGGREGATE(15,6,ROW(Employee)/((Project=Sheet2!$A$1)*(COUNTIF(Sheet2!B$5:B5,Employee)=0)),1)),""),"")
Hello Glenn -
Thank you very much! I took a peek and that certainly works, but there seems to be a flaw.
If I take your workbook and add another row with a new person (for Majestic Wines for instance) and then put a Week Ending Date that is not on Sheet2, it still includes that person in the list which shouldn't be the case. Is there a way to correct? I"ve attached your workbook with the example I'm talking about. thank you!
Hi Glenn -
Additionally, is there a way to use a formula that doesn't use INDEX? The only reason I ask is what happens when someone opens the file on a lower excel version? I would anticipate they wouldn't be able to refresh infact it would error out.
What is best practice around that?
Will be back tomorrow. In Spain (currently) and it's dinner and wine o'clock.
INDEX has been around for ever. No issues there.
Define, in words, date criteria for inclusion in sheet 2.
G'nite.
HI Glenn -
Enjoy!
So, another "criteria" needs to be added to your logic when deriving the Employee Name. For instance, we also need to factor in the Date in C5:G5 on Sheet2 when checking the Results tab.
The values in C5:G5 are also values found in the Week Ending Data column of the Results tab. The Employee returned must have a "Week Ending Data" values that matches 1 or more values in C5:G5 of Sheet 2.
Let me know if you need additional detail.
Another Named Range (Date):
=$F$2:INDEX($F:$F,MATCH(10^100,$F:$F))
=IF(AND($A$1<>"",TODAY()>$A$2),IFERROR(INDEX(Results!H:H,AGGREGATE(15,6,ROW(Employee)/((Project=Sheet2!$A$1)*(ISNUMBER(MATCH(Date,$C$5:$G$5,0)))*(COUNTIF(Sheet2!B$5:B5,Employee)=0)),1)),""),"")
HI Glenn -
That worked like a charm!! Thank you!
Back to my comment above about INDEX. I just ran a test and when I open the workbook on a non-0365 version of excel, I see the following associated to the INDEX function:
And when I refresh, I get the !NAME error. I suppose there is no work around to that other than ensuring everyone in the organization is updated to 0365?Please Login or Register to view this content.
What version of Excel is on that machine? It must be Excel 2007 or earlier??? INDEX was introduced a LONG LONG time ago. AGGREGATE came along in 2010.
=_xlfn.SINGLE appears at the start of the formula if ANY function called is not available... so I can assure you, it's not iNDEX. Are any users still on Excel 2007 or earlier?
I'm getting the error with excel 2016.
There's nothing in either formula that is not available in Excel 2016, as far as I can see ...
??? I'd say that that's impossible!! But clearly it is possible.
1. Post a screenshot.
2. Does this work in Excel 2016 (it will not give the correct result..., but that is not the point)
=IF(AND($A$1<>"",TODAY()>$A$2),IFERROR(INDEX(Results!H:H,AGGREGATE(15,6,ROW(Employee)/((Project=Sheet2!$A$1)*(COUNTIF(Sheet2!B$5:B5,Employee)=0)),1)),""),"")
Are all 3 named ranges present on the file you're opening in Excel 2016???
HI Glenn and ALi -
Just to be clear, the issue is not with Glenn's formulas. They are working fine.
If you open up my attachment, the issue is coming from the following cells:
J8,J9,I10,I11
If you look at other formulas using the INDEX throughout the workbook, I see it enclosed in {} which is working fine. But for some reason, the formulas in J8,J9, I10 and I11 have the _xlfn.SINGLE single in them. Why would this be?
*Note, my attachment does not have Glenn's updates, using this one for example purposes.
I think I know what it is:
=@INDEX(Contract!$C:$C,AGGREGATE(15,6,(ROW($A3:$A1000)-ROW($A$2))/(Contract!$A:$A=YEAR($C$2))/(Contract!$B:$B=TEXT($C$2,"mmmm")),1))
This sometimes appears when workning between newer and older versions. Delete it.
@Ali - that worked!!! Thank you so much! DO you know what causes the A to show up? I'm nervous if I open an excel file via 0365 it will add it automatically until I remove it again? I did some testing opening and closing and I don't see @ show up anymore so I'm just trying to understand how it happens so I can prevent in the future.
Google implicit intersection operator
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
Thank you again to you both for your help, much appreciated it!
Glenn, taking a looking but don't see how to prevent it from showing up infortunately. I know why it's happening but unsure how to prevent @ from showing up again when opening the files with older excel versions. I'll keep digging. Thanks!
I'm not sure that it is preventable.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks