Get Actual Number From 2 Cells?
Mar 25, 2014
I need to find a number (1, 2 or 3) and I am looking in two different cells for it. Now, Cell 1 might have the information; Cell 2 might have the information or BOTH Cells A AND B might have the information, or either cell (or both) might have different information. I can't do an IF/OR because I don't know which cell will have it, nor can I do an IF/AND for the same reason. I can't assume that they will both have the information -- so I can't just use a solution that includes a "divided by."
for example:
A
1 2
2 0
or maybe
A
1 3
2 3
or
A
1 4
2 1
If A1 or A2 is > 0, but <4, then that number
View 7 Replies
ADVERTISEMENT
Jan 23, 2014
I need to count the actual number of days between 2 dates but excluding Sunday...
For example,
Start Date-----End Date-------# of Days
01/Jan/14-----23/Jan/14-------20 days (excluding Sunday)
In some cases, Holiday also to be removed i.e. 01-Jan-2014 is holiday, then the # of days should be 19 days.
If I use Networkdays formula, then system results excluding both Saturday and Sunday. But I want to remove only Sunday.
View 5 Replies
View Related
Jun 3, 2013
Down loading data I receive dates as a number that indicates YYMMDD (ex. 130603 is 03 JUN 13). Is there an easy way to convert these number strings to the actual date indicated other than typing it in?
View 5 Replies
View Related
Oct 13, 2011
I am doing a vlookup on a cell range where the value I'm trying to lookup (a date) exists, but it's not an actual number in the cell...it's a reference to another cell with that value (somewhere completely different).
So, I'm trying to vlookup(date(1/1/2011),A1:A12,2,false) to get the B column value.
A B
1/1/2011 #
2/1/2011 #
...
12/1/2011 #
However, the A column is not the actual date. It is a reference to another cell somewhere completely different that has the actual date 1/1/2011.
When I do a vlookup trying to find 1/1/2011, it can't see it there unless I overwrite the reference in A1 (for instance) with the actual date.
Can I do a vlookup and keep my cell references?
View 6 Replies
View Related
Aug 13, 2013
I am trying to track inventory over 160 days in a spreadsheet. Every week I update a table that shows the number of skus and dollar amount that are over 160 days in a table. I have a ton of formulas that will automatically populate once I put in the new weekly data on a different spreadsheet. I want to track the weekly data so what I would really like to be able to do is paste the actual numbers and not the formulas into another excel spreadsheet so I can track the progress over time. Is there a way to just paste the actual numerical number instead of the formula itself?
View 2 Replies
View Related
Jul 10, 2006
On my worksheet, cell A1 and A2 both contain the value:
0.1655555
I've changed the number formatting to three decimal places so it appears on the sheet as 0.166.
In cell A3 I have a forumla =A1+A2 which currently comes back with the value 0.331 (0.1655555+0.1655555=0.331).
Instead of adding together 0.1655555 I want it to add the displayed value of 0.166 so it should then come back with the value 0.332.
View 3 Replies
View Related
Feb 19, 2009
i have this file with macro. But it doesnt works how i want. I need all data exctly in forms as are. So it can not will be changed. I hope it will be works, if i define my macro other.
I find actual day in column C where are dates. But now it find only if is there value like now(). In this file it found cell D24, but i need, that it will find cell C24. I need search only actual day in column C.
View 5 Replies
View Related
Apr 5, 2009
The spreadsheet i am working on requires mileage between last trip and previous trip.
Unfortunately there will be blank cells between as shown below ...
View 9 Replies
View Related
Nov 30, 2007
I have a macro which copies the values from one cell to another. But, while copying the macro changes the date format to a serial number and this doesnot happen all the times. Below is the code I have. I want the macro to automatically convert the date from from serial number to dd/mm/yyyy. I tried quite a different ways, but none of them seems to work. Below is the macro I run to copy the contents from one cell to another.
Sub History()
'
' Macro: Backup results from prior rounds. Please run this macro after selecting the cell where the backup is to be done.
'
cur_row = ActiveCell.Row
If ActiveCell.Offset(-(cur_row - 10), 0).Value <> "History" Then
MsgBox ("Check whether the active cell is the designated history cell. History operation failed")
Exit Sub
Else
ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & ActiveCell.Offset(0, -5).Value & " "..........................
View 5 Replies
View Related
Jul 10, 2013
I am compiling a master spreadsheet to analyse progress of students. I have their potential grades in one column and their latest progress grade in another.
I would like an automatic look up of the potential to the progress grade so that if their current grade is BELOW their potential that gets highlighted one colour, if it is above, another colour.
One issue is that the grades are all letters (S, A, B, C, etc.) and that some potential grades are 'dual' (i.e. A/B, S/A) which complicates the issue somewhat.
View 7 Replies
View Related
Mar 31, 2008
how to merge cells AFTER THE CODE BELOW.
Can anyone tell me how I merge cells without naming the cells?
Say that I want to go to cell A13 and then to the first empty cell in that column, where I want todays date.
Then I want to go 1 cell to the right, from that cell I want to merge 14 cells into one big cell in which I want some text written.
I come to the start cell by the following
View 10 Replies
View Related
Jan 13, 2009
how to get a date to return what the actual day? ie 13/01/09 is tuesday?
View 3 Replies
View Related
Feb 18, 2014
I constantly use the following to determine the last row in a worksheet for loop control purposes.
[Code] ......
In my current problem i have a worksheet that has been formatted such that the code above returns a value of ,say 150 where as the actual data stops at say row 34.
If I "watch" the value in Col A I see A34 = "1513(a)" which is valid data and A35 = ""
I can easily cycle through the worksheet and calculate the last used row myself but I am always looking for ways to let "VBA do the heavy lifting" when I can
View 3 Replies
View Related
Dec 21, 2012
I have a list of 5,000 urls and I'd like to see what the actual url that is returned for each of them. For example, if I run the url www.example.com/runningshoes, that url may redirect to www.example.com/shoes. I'd like to see what each url returns. Is there a formula/macro that I can use to see this? I'm not very experienced in using macros, but I'm willing to give it a shot if it'll work.
View 2 Replies
View Related
Aug 23, 2007
once bTest is declared as a Boolean, it becomes equal to FALSE (I checked this after much head scratching). How does one assign a value to a boolean variable only after you make your test.
Sub foo()
Dim bTest As Boolean
If Range("a20").Value = False Then bTest = False '
View 9 Replies
View Related
Jan 17, 2008
I have a spreadsheet with a large amount of conditional formatting and formulas within it. In order to make the file smaller and more manageable for management, I need to copy the sheet into a new book and then paste special values it.
However, upon doing this, the conditional formatting remains active and so my formats all change.
How can I copy the formats, but not the conditions?
View 9 Replies
View Related
Feb 11, 2009
I got a list of data in many row. I need to erase some rows, With a macro, how do i select the actual row position.
I want to place a macro button on each row created. By pressing it, i want to activate a macro to erase the entire selected row.
All i need is to know how to select the active row of the selected cell activated.
View 9 Replies
View Related
Nov 29, 2009
I am looking to get a Live Clock changing every minute..
Lets say I put the Total Time in the Cell A2 = 15 minutes and I have a Command Button next to it "Start"..
On the Click Event I want the Actual Time time-stamped at that moment in the Cell A3 and in Cell A4 the Time should keep on Ticking...
Can someone attach a file in Windows 2003 i.e .xls format and also explain the code if possible.
View 9 Replies
View Related
Apr 26, 2006
a colleague recently used my pc and "messed it up". when i enter a value in any cells, it is divided by 100, eg, when i enter 1, it shows as 0.01.
View 2 Replies
View Related
Jun 10, 2013
i have been working on 2 sheets receiving and selling item and QTY, or in other words i have a Stock IN sheet and a Stock Out sheet
is there some sort of a formula which links 2 cells, columns, together in a way that, if one cell's number increased the other would decrease automatically ?
say iv set 50 in cell a1, i want to set 10 elsewhere and the 50 in cell a1 will automatically decrease from to 40
here's my excel worksheets i uploaded it,
View 7 Replies
View Related
Feb 4, 2009
See xls file. I have this chart and the data labels are percentages instead of the actual value. How did these get that way? Under Chart Options there is a autotext box that is there but if I uncheck it, the data labels go back to the actual dollar value and that autotext box goes away.
I am totally baffeled by this despite serach the help, the internet and this forum. I am sure it is something easy I am missing.
Can some one provide some color on this?
I cut this from a larger book with many tabs because I am not able to send out the entire thing (proprietary).
View 6 Replies
View Related
Feb 14, 2014
I am trying to edit the data in the actual pivots rather than editing in the data source. i know there is vba code to enable pivot table editing.
How to enable this.
View 2 Replies
View Related
Oct 22, 2007
Place the actual date in a cell. Here is my code
View 2 Replies
View Related
Apr 3, 2009
I discovered the following Macro (on another site). What I'm attempting to do is make data entry simple by entering a date such as 012209 and have a macro convert the entry to 01/22/09 as a legitimate date field. On entering 012209, the macro displays a value of 09/01/2021 with an actual cell value of 1/9/2021. I expected a value of 01/22/09.
Entering 010109 displays a value of 01/01/2009 with an actual cell value of 1/1/2009. I expected a value of 01/01/09 (I can live with this, just didn't expect a 4 digit year).
View 6 Replies
View Related
Oct 15, 2009
i am creating an add in that will allow me to display all pivot tables in a workbook, their name, sheet location and source data in al listbox (called LBPivot) on a form (called FrmPivot)
My problem is that i am not sure how to make sure it points the focus to the activeworkbook and not the actual add in workbook?
View 6 Replies
View Related
Sep 19, 2012
I have a column A with date formatted as dd/mm/yyyy. I then have a column B which is =A, and I reformat the date formats to a custom format DDDD which gives me the day of the week that the date relates to.
I then try and write an if statement
e.g. =If(b1="Friday",1,0)
B1 always references the date which is stored as 40118, when I need it to reference the actual formatted value that I see in the cell e.g. Friday?
View 4 Replies
View Related
Nov 8, 2007
how would one change the actual code words - instead of using if statements i would like to have when statements that do exactly the same thing, i just want to write "when blah then blah"
View 9 Replies
View Related
Mar 11, 2008
I have this formula in E19: ...
View 9 Replies
View Related
Feb 25, 2009
I have created the following code by recording a macro. The ActiveCells (“A1.A5203”) were based upon the actual number of records at that time. I wish for this macro to be used in a template and the actual number of records will vary from time to time. What do I need to add to this code to allow the AutoFill function to operate and fill only the actual number of records that exist.
ActiveCell.FormulaR1C1 = "=LEN(RC[-2])"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A5203")
ActiveCell.Range("A1:A5203").Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=8,""0""&RC[-3],RC[-3])"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A5203")
ActiveCell.Range("A1:A5203").Select
View 9 Replies
View Related
Mar 23, 2007
I've attached a simplified worksheet which tracks the calls of a team of salespeople along with a pivot table showing their results for Jan-Mar. I've also included a summary of their planned calls for the period. Ideally what I'd like to do is have the pivot table show actual calls, planned calls and variance for each salesperson and each month as seen on A37-J42.
View 6 Replies
View Related