I use a formula to copy certain cells.
Please Login or Register to view this content.
When I copy and PASTE VALUES to another cell, the cells with no data are not blank. This is causing me problems later when I want to test for ISBLANK.
I assume because it is putting "" in the cell but I can't see it.
Is there a better way to do this?
Thanks.
In H4 copied down:
=F4=""
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.
you might try after pasting the results where you want, highlight the whole column and then go to and open text to columns, if delimited is highlighted then just hit finish. That worked for me in your example.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Sam Capricci
That works but in real life I have multiple columns and would have to do them one at a time.
Thanks.
Last edited by Len Silva; 02-23-2021 at 09:52 AM.
AliGW
That works to change COL H to TRUE, but I still need to test COL F for ISBLANK and that doesn't work.
Thanks
It doesn't work because F isn't blank. Just use the test I gave you instead.
If you want more specific advice, then demonstrate how it really makes a difference to you - from what you've given us, it's just hair-splitting, so give us a bit more to go on, please.
What I have found to work is to change my formula from "" to "x", then in the pasted results I can replace x with nothing. Kind of clunky, this is a big file and this operation will happen frequently.
The other problem is that I can't use VBA for security reasons so I am limited to formulas.
Very. I have to wonder why so much copying and pasting is necessary. Maybe a root and branch consolidation exercise would improve your work flows?
AliGW
I want to use GO TO> SPECIAL> CONSTANTS to copy only the non blank rows.
The whole process is more than I could simplify for this post.Originally Posted by AliGW
Basically, I have a table with Board Member meeting attendance. Since there are 25 members I want to enter them all at once if possible for a particular meeting.
So I use a separate sheet that lists all the members and a check box if they attended, along with date, time etc. I then copy and paste values into the table.
The problem comes when some do not attend, then I am placing empty rows into the table which I must then delete.
I also have to make it as simple as possible for the user without using macros (not permitted by IT).
I appreciate your help.
I don't really understand what you are doing.
Just be aware that ISBLANK will not detect a cell containing the null text string ""
You need to use COUNTBLANK instead
ISBLANK should really be named ISEMPTY, as there are many situations when a cell might appear to be blank but isn't.
You can use formulae to bring the data from your other sheets to avoid those manual Copy/Pastes and thus improve your workflow (and accuracy).
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks