is there a way for a udf to return the cell's original value instead of an error.
ie. the UDF in the cell returned a value from a database. now if i move the database and reopen this sheet, excel will try to recalculate and if it doesn't find the database it will return an error. instead of that i just want excel to return whatever was in the cell, before it had to be recalculated.
I am familiar with the codes suggested here: http://www.eggheadcafe.com/software/...-previous.aspx
I move the cursor with Hyper-Links or just by clicking another cell.
I wonder - is there something I can add/do in order to enable returning from ANOTHER sheet !?
I am trying to take a column of data located on one sheet and move into two different locations on another sheet. My range is from A1:A10. The below code works, but as it loops, it replaces G1 and H1 with the other values from the original range. Once the first pos and neg values are placed in G1 and H1 respectively, I want the next cells to be evaluated and placed in G2 and H2 etc. What am I missing here? How can I get it to stop pasting over itself?
Sub test()
Dim bcell As Range
For Each bcell In Range("a1:a10")
If bcell > 0 Then
bcell.Copy Destination:=Worksheets("Sheet2").Range("G1")
Else: If bcell < 0 Then bcell.Copy Destination:=Worksheets("sheet2").Range("H1")
End If
Next bcell
End Sub
We have a product plan, which we are trying to automate. To break it down we have a product number which will consist of several other components parts. In the product plan we are tying to automate showing which components are in constraint to the side of the complete part number. We've tried using VLOOKUP but it only ever returns the first value it finds, whereas we need to show all constrained parts. Have tried to break it down into ranges as per attached, but this is unreliable as the data is drawn from another source, and corrupts the ranges if re-imported or sorted.
View 4 Replies View RelatedNeed a T/F formula that will return T if any 2 of the 3 values from previous row are double digits and the third digit is also from previous row. The 3 digits in the same row must have a zero.
Examples
668 T
806
995 T
509
995 F
519
******** ******************** ************************************************************************>Microsoft Excel - CASH 3 EVE COMBO GROUPS.xlsx___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=ABCDE302/06/09006T402/05/09016F502/04/09110T602/03/09610F702/02/09661T802/01/09160FSheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
What is the keyboard shortcut to return to your previous location in the worksheet?
View 2 Replies View RelatedHow to write a VBA code so that it would calculate the date of the previous Friday and from there Return the date exactly 1 month before; on any given day.
For example...
today is may 8th
So if I run the code the dates returned for last friday should be 05/02/2014
and 04/04/2014 should be returned AS the DATE exactly a month from 05/02/2014
how do i get the previous days working date in VBA, how can i get VBA to determine wether we are in the week, and how will i get it to know its a monday to retrieve fridays date?
View 3 Replies View RelatedI am applying data validation in my workbook. The data in ColS consist of:
ColS
(Row2) X
(Row3) Y
(Row4) Y
(Row5) Y
(Row6) X
In ColT, I am to apply data validation: i.e. If in concerned row of ColS is having "X" value, then I am to show an error message. Else, values from A2:A5 should be shown in the as list and these should be accepted in ColT.
Windows 7, Excel 2007. a formula to find the next lowest sequential number within a group.
Here's an example data set, which represents transactions by date and category:
Col A is a user entered Date which will not be in chronological order (unfortunately a sort is not an option)Col B is a user entered CategoryCol C is a formula that gives the chronological sort order based on the date.
The formula in C2:C9 is =COUNT($A$2:$A$9)-(RANK(A2,$A$2:$A$9)+COUNTIF($A$2:A2,A2)-1)+1Col D is a formula to determine if the row is the first chronological transaction within the Category
The array formula in D2:D9 is =IF(MIN(IF($B$2:$B$9=B2,$C$2:$C$9))=C2,"Y","")
This might not be a necessary column, but can be used as a helperCol E is where I would like a formula to give the next lowest value in Col C for the Category in Col B (the order number in Col C for the previous transaction within the same category).
I have entered in the values that the formula should return in Col E in the table below
A
B
C
D
E
[Code]...
If the current line is the first transaction of the category, the formula should return the value in Col C for that line (so E2 would equal the value in C2, which is 2).
So the first part of the formula could be straightforward, using the helper column:
=IF(D2="Y",C2,
Here's the tricky part: If it's not the first occurrence of the category, then it should return the value of Col C for previous transaction (or the next lowest number in Col C that is within the same category).
E3 would return 4, since the previous transaction for Category A was line 9
C3 = 6, and of all the values of Col C for Category A (2, 4, 6, 8), 4 is the next lowestE4 would return 6, since the previous transaction for Category A was line 3 C4 = 8, and of all the values of Col C for Category A (2, 4, 6, 8), 6 is the next lowestE5 would return 1, since it is the first transaction in Category BE6 would return 1, since the previous transaction for Category B was line 5 C6 = 5, and of all the values of Col C for Catebory B (1, 5, 7), 1 is the next lowestEtc.
I'm thinking I need an array formula to find the minimum value within the category, where that value is less than the value of Col C in the current line. Just not sure how to write that...
see attached file. Need to find latest non blank value - in attached file it is highlighted in yellow. From there, want to summarise 26 weeks back so, in the attached file:
Row 2 would be finding 750 and summarised back 26 weeks from 30 sep 2012
Row 3 would be finding 2250 and summarised back 26 weeks from 2 dec 2012
Row 4 would be finding 5000 and summarised back 26 weeks from 4 nov 2012
i use a worksheet.change() sub in the vba of the worksheet itself, not a new module.
I want to check that when a certain value is deleted, the user really wants to do this (and attach a routine to this to delete the entire row, shift etc). so far piece of cake, but where the .... can i find the previous value of the cell in case the user decides he made a mistake. the standard VBA sub creates a target as range, but there doesn't seem to be a target.value.previous, or something to that effect, or for that matter something like a worksheet.beforechange() sub
how to find and restore a value which has been deleted in excel in the worksheet using VBA?
I have an excel document which I am trying to automate using VBA. I have two headings which have the same text every month but the month value must change.
Basically I’m looking for the last day of the previous month. So if it was 05th April 2007 I want to return 28 March 2007. I just need to obtain the last day of the previous month in the following format ’28 March 2007’.
I need to paste Entire row in one sheet( Sheet1) to the previous row in other sheet(Sheet2) ex: If i copy 3rd row from one sheet(sheet1) than it must be copied to 2nd row in other sheet(sheet2)
Sub try()
Sheets("Sheet1").Select
Range("b2").EntireRow.Copy
Sheets("Sheet2").Select
Range("b2").EntireRow.Previous.Select
ActiveSheet.Paste
End Sub
I have built a compiled a workbook which figures out the daily business in my club, Staff Rota, business done, wage slips, and everything else I need. Well, everything except the stock element.
I need the current week to look up the previous weeks stock levels on each item, when the previous weeks workbook changes it's name every week. At present I name the sheets by the week ending date, i.e. 12-04-14.
I have a data table with dates and marks by 0 and 1.
I need a formula, if a date selected from data table, to find first next or previous matched date.
At attached table you can see all detail and explanation.
match dates.xlsx‎
I've got a workbook that increments in sheets for each new week. I have a cell showing the current week number for that sheet ( lets say cell C1) and any formulas that need to reference data from the previous sheet do in 'indirect' function which basically looks at the current week number ( lets say week 20)in cell C1 and minus's 1 to navigate to the previous week (week 19).
I was wondering if there was a way to reference the previous sheet purely by the order they're in.
So say i have 5 sheets named "1,4,5,8,9" and these represent week numbers so the sheet named '8' in cell C1 would have '8' standanding for the week number but cells that wanted to reference the previous sheet couldn't do the 'indirect' C1-1 as there is no 'week 7'.
The weeks used could vary alot so a formula to reference the directly previous sheet is needed.
ok, thought best if i show you the actual formula
=IF(ISBLANK(C12),"",IF(ISERROR(VLOOKUP(C12,INDIRECT("'"&($AG$2-1)&"'!$C:$AG"),28,FALSE)),G12,SUM(G12,VLOOKUP(C12,INDIRECT("'"&($AG$2-1)&"'!$C:$AG"),28,FALSE))))
the '$A$G2-1' part is the bit that redirects to the previous sheet based on it's name, what i think i really need is to replace this part with a 'PrevSheet' function but i'm unsure how.
I want to move to the next row but i don't want to make another format just when i begin to write in the next row the format appear automatically like previous row ?
Note : format include formulas ,borders and colors every thing in previous row ...
If I erroneously saved over an excel document that is stored on an external drive, is there any way to restore the previous saved document, or am I screwed.
The thumb drive is plugged into a computer that gets backed up onto a network server, but I assume that the external thumb drive is not backed up with the c drive and r drive on the server.
Sheet summary2 has the table. And I am looking to compare and add the data (from all sheets Date 1 to Date31) . I am looking to add those values (yes as 1) in my summary 2 sheet for the range E3 to H14 ...if it mathces as MF8330 or x543 or c5045 or x940.
View 4 Replies View RelatedIn Excel VBA, is there a way to get the beginning date and the last date for the previous month from TODAY()?
i.e. If TODAY is Jan 3, 2014, then I would like to have the beginning date as Dec 1, 2013 and the end date as Dec 31, 2013.
i.e. If TODAY is Mar 18, 2012, then I would like to have the beginning date as Feb 1, 2012 and the end date as Feb 29, 2012 (yes 29, not 28).
So basically, I need to have the AI to know the beginning of the year (January 2014 - takes Dec 2013) and if it happens to be February, the 'end date' is either 28th or 29th depending on the year.
I have a report that is run everyday showing a list of orders, on separate tabs based on facility code, that has all kinds of information. I want to be able to take today's file, and have it lookup any comments I posted on the previous day's file.
I've been messing with code all morning and can't get it right. I think my problem is the vlookup part, and I don't think my loop is right either. Here's a little bit of the code i'm trying:
I've found on this newsgroup a function to get the previous sheet
name
Function PrevSheet()
On Error GoTo EndFunction
Application.Volatile True
PrevSheet = Application.Caller.Worksheet.Previous.Name
Exit Function
EndFunction:
With Application.Caller.Parent.Parent.Worksheets
PrevSheet = .Item(.Count).Name
End With
End Function
when i put =PrevSheet() i get the previous sheets name
What i want to be able to do is use this in place of directly
referencing the sheet name in my code as follows...
=IF(G4-J4<0,G4-J4,G4-J4)+'Week 1'!H4
So i want to be able to replace Week 1 with PrevSheet but i've tried
and falied on this. eg...
=IF(G4-J4<0,G4-J4,G4-J4)+PrevSheet()!H4
Could someone show me the correct syntax for this.
I would like to have a "PREVIOUS" navigation button in a workbook that goes back to the previously activated worksheet, which may or may not be physically the previous worksheet in the workbook. In other words, if I go from Sheet #1, to Sheet #3, to Sheet #5, I'd like to return to Sheet #3 from Sheet #5 (rather then navigating back to Sheet #4).
"Open VB editor and double click 'Thisworkbook' and paste this code in on the right:
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
lastsheet = Sh.Name
End Sub
Then right click 'ThisWorkbook' and insert module and paste this code in
Public lastsheet As String
Sub Select_Last()
Sheets(lastsheet).Select
End Sub
Whenever a user changes sheets, the name of the last sheet is now held in the variable 'Lastsheet' so if you assign a shortcut or button to the code then the last used sheet will be selected."
My problem is that when I want to assign the macro, I can't find macro name that looks anything like the above in the drop down list of Macro Names. how to assign the macro to a button?
Yesterday (13/03/2007) was the 9th working day of the month (basing a week on Mon – Fri) – today is the 10th working day
Is there any formula, or mix of formula’s I can use to automatically calculate this?
I have a month number in H2 (1-12). I want a formula that will give me the previous month number. So, if h2 = 1. I need my formula cell to equal 12.
With "edit directly in cell" turned off, if you double click through in a cell containing a formula to the cell being refferenced, is there a quick way of getting back to the original cell you clicked on?
View 9 Replies View RelatedI have a DDE-based worksheet, that is obtaining data from Townsend Analytics' RealTick Server software. One of my cells is a DDE formula that retrieves the current BID ask price, for a given stock.
When a new BID price comes in to my designated cell, I need to somehow capture the existing BID price, before this new one updates in the requisite cell. I want to be able to store this "old" value in another cell.
Unfortunately, there is no "pre-cell" event trapping. I've tried to work with the worksheet_change event, which passes an argument of the effected cells, but the cell value has already changed, by the time this event has been called.
This problem sounds simple, but I am at a loss for a solution. Does anybody out there in EXCEL-land have a solution ?
I have a folder containing excel files with dynamic suffix names. I have files for all days starting from the 1st of the month – for example : Performance_01012010.xls, Performance 02012010.xls, Performance_03012010.xls till Performance_31012010.xls.
I want a VBA code to retrieve the date out and change it automatically (means it should add 1 automatically to the previous date). For example, if I open Performance_05012010.xls, my macro should SAVE AS this file to Performance_06012010.xls.
I am writing a macro that goes from one sheet to another sheet to copy data but then must return to the 1st sheet to paste it.
Is there a command for returning to a previous sheet.
Note that the sheets may not necessarily be in consecutive order ie. the 2nd sht may not be immediately after the 1st sheet in the sheet tab order.
The spreadsheet will be made up of a number of sheets that can use the same macro to reference a master sheet to copy and return data.