#1
|
|||
|
|||
Index Function
I am linking a range of one sheet to another.
I highlight the same size and shape of the data I want to link, and I click = then go back to the source data, highlight it, go back to the destination sheet, click CONTROL/SHIFT/ENTER. It works but I get a lot of 0's.....How can I make it show blank cells instead of 0? ='\\177.177.1.14\Sales\Order Status 2018\[Jason''s Orders.xlsx]Jasons Customers'!$A$6:$M$94 thanks |
#2
|
|||
|
|||
Code:
=IF('\\177.177.1.14\Sales\Order Status 2018\[Jason''s Orders.xlsx]Jasons Customers'!$A$6:$M$94="","",'\\177.177.1.14\Sales\Order Status 2018\[Jason''s Orders.xlsx]Jasons Customers'!$A$6:$M$94) |
#3
|
||||
|
||||
Arvi's formula uses the null text string ("") which often has unwanted results if you need to work with those "empty" cells later
There are other solutions without that risk : use your formula as before and - conditionally format cells containing a 0 to background color (which is not the best solution) or - click File - Options - Advanced - Display options for this worksheet ( change to book if needed) - Uncheck " show a zero in cells that have zero value" (the easiest solution) or - custom format cells as 0;-0;;@
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
|||
|
|||
it worked! thanks!.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VLookup and Nested IFs or Index Function | SavGDK | Excel | 10 | 04-08-2016 10:06 PM |
Help with Index Function to Select Range of Cells | bretyuin | Excel | 1 | 02-24-2016 05:11 AM |
Index Match Function across different worksheets | shay_mt | Excel | 2 | 04-27-2015 06:04 AM |
Index Match function | jackzha | Excel | 5 | 12-03-2014 12:43 PM |
Moving a Index/Match function | FraserKitchell | Excel | 2 | 01-19-2010 09:38 AM |