I'm trying to pull data from another excel sheet - my problem is that when I use my formula < =VALUE('Sheet 1'!AH5) > and AH5 is blank, it is filling in a zero in the new cell. This is a problem because I am using the referenced data in an AVERAGE formula and it is throwing off my numbers (because the zero is counted in the average instead of being left blank). Is there any way I can pull the information from the other sheet and have it fill in blanks when the original cells are blanks?
Last edited by williamc; 03-31-2016 at 04:19 PM.
You can do this
=IFERROR(1/(1/VALUE('sheet 1'AH5)), "")
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
This may be obvious (I'm still new to Excel), but would I add that to my existing formula or make it the new formula? If I add/combine it with the one I have, how do I do that?
That would replace your formula. Look closely; your formula is embedded.
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
Thank you. So that worked with the blanks. The problem I'm having now is that the zero's are also showing up as blanks, and I need the zero's to show up as zero's. How would I alter that to show zero as zero and blank as blank?
If you have to differentiate between blank and 0, try
IF('sheet 1'AH5="","",VALUE('sheet 1'AH5))
As an aside, why are you using VALUE? Are some (or all) of the numbers on sheet 1 set as text? Do you have some text values that you want to show up as errors?
If all the data is numbers (or blanks)
=IF('sheet 1'AH5="","",'sheet 1'AH5)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks