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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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 :/
 
Upvote 0
So, this is what I've got so far...

A B C D E F G H I
dateowedpay inmonth pay inmoney outbalint addedbalance
01/05/2015£12,000.00£35.00£33.50£11,998.50£1.324.00%£11,999.82
02/05/2015£11,999.82£0.00£11,999.82£1.324.00%£12,001.13
03/05/2015£12,001.13£12,001.13£1.324.00%£12,002.45
04/05/2015£12,002.45£0.00£12,002.45£1.324.00%£12,003.76
05/05/2015£12,003.76£0.00£12,003.76£1.324.00%£12,005.08
06/05/2015£12,005.08£0.00£12,005.08£1.324.00%£12,006.39
07/05/2015£12,006.39£0.00£12,006.39£1.324.00%£12,007.71
08/05/2015£12,007.71£0.00£12,007.71£1.324.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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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