Pulling data from other sheets
I have two sheets. On sheet one I have a table for data. When these numbers are less than a certain value I use <X.
I now need the raw data so for example if X=2 and my result is 0.03 I want the second sheet to contain the raw number of 0.03, then display on sheet 1 <2.
Also if the result is >X I have the cell turn yellow to highlight the result. That part I have figured out but don’t want to lose.
Can anyone help me?
Posted Nov 8, 2017, 4:22 PM UTC
38
=IF(Sheet2rawdata!A1<2,"<2",Sheet2rawdata!A1) ?
I apologize for my delayed reply. That code was able to pull the data successfully, but in the process my conditional formatting has gone away and now highlights any cell. I’m not sure how to proceed.
it probably fails when you put in the text "<2" which is not a number it can evaluate. It will work for 0 or blank.
could you use -1 instead of "<2"?
Also if the cell is empty, I don’t want anything to transfer over, right now it’s filling in 0. Thank you for your help!
just nest another if into your if =IF(Sheet2rawdata!A1="","",IF(Sheet2rawdata!A1<2,"<2",Sheet2rawdata!A1))
or just format the cell to hide zeros but you will have to keep the nested if to include if zero. you could do
=IF(Sheet2rawdata!A1=0,0,IF(Sheet2rawdata!A1<2,"<2",Sheet2rawdata!A1))
https://support.office.com/en-us/article/Display-or-hide-zero-values-3ec7a433-46b8-4516-8085-a00e9e476b03
I’ll give it a shot, thanks again.