Return value of cell from entire workbook

empach

New Member
Joined
Sep 11, 2012
Messages
22
Hi,

I would like a little help on the formula below please.

I am trying to search for a negative number across all worksheets (I have named a range of their names as MySheets - each worksheet is a year)

The column I am searching is F, and I want to return the cell value of A when it finds the first negative number.

IF(COUNTIF("'"&INDEX(MySheets&(F11:F499,"<0"))),"'"&INDEX(MySheets&(A11:A499,MATCH(TRUE,"'"&INDEX(MySheets&(F11:F499<0,0),0)))),"No negative numbers")

Thanks in advance :)

Paul
 
Hi.

I imagine this can be shortened somewhat using some combination of FREQUENCY and other functions, but this array formula** should work:

=LOOKUP(1,0/(INDIRECT("'"&INDEX(MySheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&MySheets&"'!F11:F499"),"<0")>0,0))&"'!F11:F499")<0),INDIRECT("'"&INDEX(MySheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&MySheets&"'!F11:F499"),"<0")>0,0))&"'!A11:A499"))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
 
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Oh my days!!! THANK YOU RICK!!!!!!! I would never have been able to do this without your assistance. Works like a dream!! Thank you SO MUCH!! :)

Cheeky, I know but, Is there a way of making it self update when I alter the balance column??

Paul (BIG HAPPY SMILEY FACE!!!)
 
Upvote 0
Thank you XOR, I have input this formula, but this recalls a date that is 9 rows down from the first negative number???????

Paul
 
Upvote 0
Do the ranges I used match yours? Did you remember to follow the instructions re array formulas?

Regards
 
Upvote 0
XOR, I do have the curly brackets around the formula, and the ranges F11:F400 are what I am searching in. I have messed about with the figures to try to show a different balance and what I have found is that it finds the right worksheet but brings the last date on that worksheet - not the first instance of a negative number.

With respect - and thanks, but there is no need to carry on trying with this formula as Rick has given me a working solution.

I thank you for your assistance in my hour of need - I'm sure I will be asking again in the future :)

Paul
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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
Back
Top