Help NEEDED!

g71gx

New Member
Joined
Jan 28, 2014
Messages
3
I'm a first timer here. normally can find what I need by just searching but this one is stumping me.

I have 2 sheets in the same work book but I would like to move them to there own work book.

I'm trying to search column B in the NEW KITS sheet using a Name Manager in a separate work book.

I changed the formula from(Name Manager):

='NEW KITS'!B1953:B10000

To(Name Manager):

='\\AVXWHS2-PC\AVX Share\[Mikes Tracker.xlsx]NEW KITS'!B1953:B10000

but after i made the change I get a #VALUE! error.

any ideas? thanks!
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

jamtay317

Well-known Member
Joined
Mar 6, 2013
Messages
769
try
=INDIRECT('\\AVXWHS2-PC\AVX Share\[Mikes Tracker.xlsx]NEW KITS'!B1953:B10000)
 

g71gx

New Member
Joined
Jan 28, 2014
Messages
3
this did not work
try
=INDIRECT('\\AVXWHS2-PC\AVX Share\[Mikes Tracker.xlsx]NEW KITS'!B1953:B10000)

not understanding what you mean
Try to reference the ranges of interest directly in the formulas you need to invoke.


I also tried this

=C2-(COUNTIF('\\AVXWHS2-PC\AVX Share\[Mikes Tracker.xlsx]NEW KITS'!B1953:B10000,D10)*E10)-(COUNTIF('\\AVXWHS2-PC\AVX Share\[Mikes Tracker.xlsx]NEW KITS'!B1953:B10000,D12)*E12)-(COUNTIF('\\AVXWHS2-PC\AVX Share\[Mikes Tracker.xlsx]NEW KITS'!B1953:B10000,D14)*E14)-B2
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
this did not work


not understanding what you mean



I also tried this

=C2-(COUNTIF('\\AVXWHS2-PC\AVX Share\[Mikes Tracker.xlsx]NEW KITS'!B1953:B10000,D10)*E10)-(COUNTIF('\\AVXWHS2-PC\AVX Share\[Mikes Tracker.xlsx]NEW KITS'!B1953:B10000,D12)*E12)-(COUNTIF('\\AVXWHS2-PC\AVX Share\[Mikes Tracker.xlsx]NEW KITS'!B1953:B10000,D14)*E14)-B2

Don't use named ranges, use rather the ranges themselves in the formulas.

By the way, COUNTIF won't work with a closed workbook. Try to switch an array-processing formula like one with SumProduct...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,914
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top