Referencing a range in a different worksheet, NOT vlookup

[cmboulter]

Hi,

I have made a nested IF statement, that includes a MATCH function. This match function needs to reference a range (e.g A2:B10) in another worksheet (e.g. "Contracts" sheet in the Rigs.xlsx file on my desktop).

How can I reference this range, so that it looks at this range as it would any range in its own sheet? Here is my attempt.

=MATCH($R19,[RIGS.XLSX]Contracts!$A$2:$T$1000,0)

So I need the A2:T1000 range in the Contracts sheet, in the rigs.xlsx file.

I'd be grateful for any help. I think this is the problem that is preventing my nested IF formula from working. If it isn't, I'll follow up with the full formula.


Thanks!!

Chris

[teylyn]

Match only uses a one-column or one-row range as the second argument and with the 0 as the last parameter returns the number of the first cell that matches the search value.

You probably want something like


=Index([RIGS.XLSX]Contracts!$A$2:$T$1000,MATCH($R19,[RIGS.XLSX]Contracts!$A$2:$A$1000,0),<column number>)

where <column number> is the number of the column where the value to be returned is located, e.g. 2 for column B, 3 for column D, etc.

If you need more help, we would need more information from you about the spreadsheet. Post a small data sample with dummy data to illustrate your data layout and expected results.

cheers,

[cmboulter]

Thank you very much for your help.

I have attached my example worksheet with instructions on what I'm aiming to do with the formula. I'd be grateful if you could take a look and let me know if you have an easier solution that what I'm doing at the moment.

Thanks again

Chris

[Cheeky Charlie]

Two additions:
To get the syntax, open the referenced workbook and select the range. When you close the referenced workbook the formula will update with the full path.

To find out where an equation is failing use tools->formula auditing->evalute formula (not a beautiful looking utility, but incredibly useful)

[cmboulter]

Thanks Charlie,

Has anyone managed to look at my example sheet?

Thanks

[Cheeky Charlie]

Table 2 is a comic red-herring?
Is rig contract ID a key? (all entries unqiue)

edit:
What if contracts on the same rig overlap?

[Cheeky Charlie]

In anticipation:
=SUMPRODUCT(--(D17=B2:B4),--(B17>=D2:D4),--(B17<=E2:E4),C2:C4)

I set the number format of the cell to: #;;"No contract" in order to avoid using an if and duplicated formula.

HTH

[cmboulter]

Hi,

Yes, contract ID is a unique field.

If contracts overlap, then an error should be displayed. N/A is fine as I will then check it manually in my database.

What next?


Thanks

Chris

[Cheeky Charlie]

=IF(SUMPRODUCT(--(D17=B2:B4),--(B17>=D2:D4),--(B17<=E2:E4))>1,"Contracts overlap!",SUMPRODUCT(--(D17=B2:B4),--(B17>=D2:D4),--(B17<=E2:E4),C2:C4))

You will need to make the references absolute by row (B2:B4 -> B$2:B$4) but left relative for ease of reading.

[cmboulter]

Thanks Charlie.

Could you explain what your formula does?

I have inserted it into my example spreadsheet, but it doesn't seem to work.

What I'm aiming for is for the formula to compare the date given, to the dates of each contract, and then when its found one where the date is inclusive of start and end, it extracts the contract ID.

Cheers

Chris

[Cheeky Charlie]

Works for me...

Looking at this:
SUMPRODUCT(--(D17=B2:B4),--(B17>=D2:D4),--(B17<=E2:E4),C2:C4)

Sumproduct multiplies the relative elements of multiple arrays then adds them together - so:
(D17=B2:B4)=(60898={60889,60898,60898})={FALSE,TRUE,TRUE}
-- has the effect of multiply this by -1 twice - which coerces true/false to 1 and 0, so
--{FALSE,TRUE,TRUE}={0,1,1}

The second block tests whether the well date is greater than the contract start date - {0,0,0}
The third block tests whether the well date is less than the contract end date - {1,1,1}
The fourth block is the contract IDs - {1,2,3}
Multiplying: {0,1,1}*{0,0,0}*{1,1,1}*{1,2,3} = {0,0,0}
Summing:{0+0+0} = 0

With the number format: [#;;"No contract"] (remove brackets) this displays as 'no contract'

If there were more than one contract matching the contract and the time frame the first phrase would trigger, creating the 'more than one contract' warning.

What are you getting?

[cmboulter]

I get 0 like you, but then adjust the dates, for example so that the date fits into one of the ranges, but then I get 5.


In the meantime, I think I've found a way of getting the information I need with a long IF formula, which I can tell you if you're interested.

But another, seemingly more simple problem has come up.

I use this in the formula:

=COUNTIF('C:\Users\chris\Desktop\[Rigs.xlsx]Contracts'!$A$2:$A$1000,R26)

Which displays the #VALUE error. It doesn't matter what R26 is (e.g. 60004, or 60012), the value error still appears. All the data in that range are numbers, in a number format.

But when I have the Rigs sheet open, the formula works fine. Is there some problem with the reference? I don't want to have to have the rigs worksheet open all the time.



Thanks a lot for your help

[Cheeky Charlie]

I get 0 like you, but then adjust the dates, for example so that the date fits into one of the ranges, but then I get 5.

Are you changing the number format as instructed?
Are you inserting the whole formula I gave:
=IF(SUMPRODUCT(--(D17=B2:B4),--(B17>=D2:D4),--(B17<=E2:E4))>1,"Contracts overlap!",SUMPRODUCT(--(D17=B2:B4),--(B17>=D2:D4),--(B17<=E2:E4),C2:C4))
?
I would be very surprised if this returned 5 on the example you provided.

RE: long if, no thanks

COUNTIF, SUMIF and INDIRECT don't work on closed workbooks, there are various workarounds for each but I would suggest the formula I've provided comprehensively addresses your needs. Have you updated your example?

[cmboulter]

Wow, thank you! I changed the format of the cell and it worked like a charm.

I have transferred the formula to my main sheet and it works great so far!

Thanks a lot! Much easier than the long IF formula!


Cheers

Chris