I have three columns, N, O, and P. The cells are pulling in values from another worksheet. Because of the way this particular spreadsheet is (it's one that is meant to be filled by users in my department, and some may fill it with less data than others), some of the cells are blank. I am trying to create a list of the cells that only contain values from N, O, and P into V, W, and X, but I am stumped. I've spent far too long on this without asking a question. I am hoping this makes sense!
Hello and Welcome to the Forum,
It somewhat makes sense, but a sample file of what you have and what you expect always helps.
If you do really have O365, then try the Filter formula.
=FILTER(N2:N100,N2:N100<>"")
HTH
Regards, Jeff
Thanks Jeff! I did try the filter formula, but I kept getting a spill error. Attaching here, a quick glance of the spreadsheet. Typically, the info in columns A-L is being pulled in from a different tab, but I've just pasted the values in those columns for now.
Ok, well those cells are not really blank, they appear to be zero. I can't download your sheet for some reason, but how about try...
=FILTER(A2:A37,LEN(A2:A37)>1)
Yeah I should have mentioned that they were returning zero values but holding formulas. I have tried to resave the file in a different way so maybe it's possible to open it? I tried that formula as well and got a spill.
Before we try anything else, I suspect you haven't listed everything in column AA from column N. Hopefully this was just a sample. What about Semi Reporting and Quarterly? These are listed twice in column N, but they also have different dates in column P. What are you intending to do with those duplicate values?
Have you considered just using a pivot table?
So.... this whole thing is a part of a much larger project. The intent is to have the admin input the dates on the "Input Data" tab - on the "formula central" tab - which is the sample I sent over before, it calculates dates based off of the original date and so forth.
Once that is done, it would fill in the "fact sheet" and the calendar is changed based off of that. Then, the button will print to pdf to their desktop (I have disabled the macro on this file, but I did get that to work!)
I will tell you that I KNOW this is not the bet way to do this, but - I am not the most proficient in excel, and I've just started with VBA, so I hesitated to send any other info - but here it is!
Not sure I have a good solution. Let me post the thread in our help area to see it anybody has something to offer.
Re: Looking for formula to return array of data excluding blank cells.
is that what you want?
Yes! how did you do that?
Re: Looking for formula to return array of data excluding blank cells.
Use Power, Luke!
this is Power Query with a simple M
(M is a Power Query language)
You've Power Query build-in
Please Login or Register to view this content.
Oh! I have not really stepped into Power Query yet…. No time like the present? *I am one with the Force. The Force is with me.*
Thank you for this! I’ll give it a go.
Is this what you are after:
Formula:
Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Look at first line (link) in my SIGOriginally Posted by ecubugg
Or were you looking for something like this:
Formula:Please Login or Register to view this content.
Is this resolved now?
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
I went back today to try this, and for whatever reason, it's not giving me a spill today!!!! So, this is awesome and worked perfectly. Thank you so much
Happy to know to have your solution and thank you for the rep and marking the thread as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks