# Return value of cell from entire workbook

#### empach

##### New Member
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")

Paul

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### empach

##### New Member
I should also add that each column is the same on each worksheet

Anyone?? :?

#### Boehmer

##### New Member
Are you trying to find a single value?

Have you tried grouping the sheets?

#### empach

##### New Member
Are you trying to find a single value?

Have you tried grouping the sheets?

Hi,

Yes, I am trying to find the first negative value in column F. If its not on the first worksheet, then search column F in the next worksheet, and so on, until it finds a negative number. Once found I then want it to return the value(date) of column A.

I have grouped the sheets - name a cell range of all worksheets and called it "MySheets", but still to no avail.

Thanks

Paul :/

Any ideas??

#### empach

##### New Member
So, this is what I've got so far...

A B C D E F G H I
 date owed pay in month pay in money out bal int added balance 01/05/2015 £12,000.00 £35.00 £33.50 £11,998.50 £1.32 4.00% £11,999.82 02/05/2015 £11,999.82 £0.00 £11,999.82 £1.32 4.00% £12,001.13 03/05/2015 £12,001.13 £12,001.13 £1.32 4.00% £12,002.45 04/05/2015 £12,002.45 £0.00 £12,002.45 £1.32 4.00% £12,003.76 05/05/2015 £12,003.76 £0.00 £12,003.76 £1.32 4.00% £12,005.08 06/05/2015 £12,005.08 £0.00 £12,005.08 £1.32 4.00% £12,006.39 07/05/2015 £12,006.39 £0.00 £12,006.39 £1.32 4.00% £12,007.71 08/05/2015 £12,007.71 £0.00 £12,007.71 £1.32 4.00% £12,009.02

<tbody>
</tbody>

This goes on for about 12 years - each year is on another tab. I am trying to write a formula that finds the first negative number in the balance column (F). If it is not in the first yeay, I want it to search the next year (worksheet) and so on. Each worksheet is named is correspondence to the financial year - A15-M16, A16-M17 etc.

Any help or advice, guidance or otherwise would be gratefully received as it's beginning to do my head in thinking and searching for the answer!

Paul

Last edited:

#### Rick Rothstein

##### MrExcel MVP
I am not a formula expert, so I could be wrong, but my gut tells me you will not be able to find a formula to do what you want. If I am right, that means you will need a VBA solution (which is something I know a little about). Below is a UDF (user defined function) which will find the first negative value in Column F no matter what worksheet it is on and return the value in Column A for the row it is on in the sheet that it was found on (it will return the empty string "" if there are no negative values)...

Code:
``````Function FirstNeg()
Dim X As Long
On Error Resume Next
For X = 1 To Worksheets.Count
FirstNeg = Sheets(X).Columns("F").Find("-", Sheets(X).Cells(Rows. _
Count, "F"), xlValues, xlPart, , xlNext).Offset(, -5).Value
If Err.Number Then
Err.Clear
Else
Exit For
End If
Next
On Error GoTo 0
End Function``````

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use FirstNeg just like it was a built-in Excel function. For example,

=FirstNeg()

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

#### empach

##### New Member
Thank you Rick!! But, this has brought about a date from column A that does not show a negative balance from F??!! Would it help to look for a range of F11:F400? If so, how should I alter the VBA???

Thanks again

Paul

#### Rick Rothstein

##### MrExcel MVP
empach;4181445Would it help to look for a range of F11:F400? [/QUOTE said:
Yes, if that is the range where you want to look for negative numbers is at. See if this modified code works for you instead...
Code:
``````Function FirstNeg()
Dim X As Long
On Error Resume Next
For X = 1 To Worksheets.Count
FirstNeg = Sheets(X).Range("F11:F400").Find("-", Sheets(X).Range("F400"), _
xlValues, xlPart, , xlNext).Offset(, -5).Value
If Err.Number Then
Err.Clear
Else
Exit For
End If
Next
On Error GoTo 0
End Function``````

Replies
7
Views
282
Replies
5
Views
125
Replies
3
Views
178
Replies
2
Views
94
Replies
9
Views
546

1,195,594
Messages
6,010,628
Members
441,558
Latest member
lambierules

### 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?

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