Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-02-2018, 07:38 AM
jennyyoung jennyyoung is offline Index Function Windows 7 32bit Index Function Office 2010 32bit
Novice
Index Function
 
Join Date: Apr 2018
Posts: 15
jennyyoung is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 05-02-2018, 08:22 AM
ArviLaanemets ArviLaanemets is offline Index Function Windows 8 Index Function Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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)
Reply With Quote
  #3  
Old 05-02-2018, 08:57 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Index Function Windows 7 64bit Index Function Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #4  
Old 05-02-2018, 09:59 AM
jennyyoung jennyyoung is offline Index Function Windows 7 32bit Index Function Office 2010 32bit
Novice
Index Function
 
Join Date: Apr 2018
Posts: 15
jennyyoung is on a distinguished road
Default

it worked! thanks!.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Index Function VLookup and Nested IFs or Index Function SavGDK Excel 10 04-08-2016 10:06 PM
Index Function 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 Function 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:42 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft