"If in last week" formula
I have a number of simple two column sheets, one column contains dates.
Basically each sheet is an employee, and the items list the date and
notes for any "one on one meetings" that I have with that employee. I
want to have a summary sheet that lets me know when the most recent
I think it would be ideal to check sheet1-5, if date is older than 1
week, output false or true.
I can't find any formula example on the net that uses dates as a
condition of true or false. Can anyone point me in the right direction?
work for what ...See if cell is in Range
Thanks for taking the time to read my question.
I'm passing a string that is a cell reference to a function. In that
function I want to determine if that cell reference is within a predetermined
range. Not sure how to do that. Right now I have:
Function CheckRange(TheSheet As String, TheCell As Range) As Boolean
If TheSheet = "Sheet1" Then
if TheCell In Range("B4:B30") then 'This line is red as it is incorrect
CheckRange = True
What do I use instead of "In"?
Use something like:
If In...Editing in a cell
I am having a problem with a newly created workbook.
When I go to a cell that has data in it and try to add more data to the
existing string I lose all the previous information. The work sheet that I am
working in is protected and I have "edit directly in cell" checked in the
options section. It is almost like my double click (as well as F2) are being
treated as a single click.
I figured it out. For some reason "Hide" is selected for the unlocked cells.
"John English" wrote:
> I am having a problem with a newly created workbook.
> Wh...Money signs appear in my Cell and I don't want them there
Im trying to enter the numbers 2.9 in cell like AD and it turns it into
$2.90. That is not what I need, how do I stop that?
Format as General instead of currency
"kate" <firstname.lastname@example.org> wrote in message
> Im trying to enter the numbers 2.9 in cell like AD and it turns it into
> $2.90. That is not what I need, how do I stop that?
...How do I get a cell to remember the last entry and add to it?
I am using Excel 2000. I want my cell to remember the number in it and add
an additional number each time I need to retotal.
Sounds like you want that cell to be an accumlator cell.
Simple question with complex results.
You can have a cumulative total in a cell if you have a
separate source cell for adding a new total to the original.
Use at your own risk. I am Posting this just to show you how it can
be done, not as a good solution. You would be much better off to
have another column so you can keep track of past entries.
Goes like this: =IF(CELL("address")="$C$4&...How do I enter the date last modified in the footer of an Excel d.
How do I enter the date last modified in the footer of an Excel document?
I want to be able to distribute a monthly or weekly report in Excel to users
and have it automatically update the date last modified in the footer each
time I update the report.
You need to do that with a Before_Print event macro. If you search the
newsgroups from Google you will find lots of examples.
On Fri, 4 Feb 2005 09:33:04 -0800, "Last modified date in footer" <Last
modified date in email@example.com> wrote:
>How do I enter the date last modified in the footer of an Excel doc...Vlookup only the last 6 characters
I have a spreadsheet starting in cell A4. I want to lookup only the last 6
digits in column A and then lookup the value in column J and return the
appropriate value from column K.
The values in column A are 000322341; 000333456; etc.
The values in column J are 322341; 333456; etc.
Here is my current non working formula...
Any help would be appreciated. Thanks.
The RIGHT function is returning a text value, and I'm guessing that the
values in column J are true numbers. So you'd need to covert the text back
to numbers in order to...Bold report column based on month
I have a report with a column for each month. (Jan, Feb, Mar...)
I'd like to bold the data in the column that the report is run for.
I placed this code in the on Format event of the report to test, the code is
executing ok but the columns end up all in normal weight.
m = DatePart("m", dt)
Select Case m
Me.Jan.FontWeight = vbBold
Me.Feb.FontWeight = vbNormal
Me.Mar.FontWeight = vbNormal
Me.Apr.FontWeight = vbNormal
Me.May.FontWeight = vbNormal
Me.Jun.FontWeight = vbNormal
Me.Jul.FontWeight = vbNormal
Me.Aug.FontWeight = vbNormal
Me...looking for range of text in a single cell
I just started a new job and my company already had a file with abou
16,000 records in it (called Products). This file contains all of th
parts numbers that they carry. In this file there is one column calle
"description". This cell contains the dimensions, color name and som
other information such as if the piece is flat or rounded. I hav
another file that has about 400 records and this file tells me "colo
name" as well as the "type" of stone that it is (called Stone_Type).
What I am trying to do is add a field to the Products file that wil
say stone type. ...To have a 'fixed cell' be equal to the last data entered cell in a column
Good Evening All,
I have a worksheet, example below.
I have frozen the panes to always show rows 1-3.
I wish the cell A3 to be the same as the last 'non-blank' cell in Column
A.(See explanation below).
2 Header Header
So in this case, at present, A3 would be M1001,(A9), but when I enter a new
value in A10, (eg M1008), I would like A3 to automatically update to A10 ie
I think that maybe INDIRECT or OFFSET maybe involved but am fairly
unfamiliar with ...Comparing columns and extracting data
I've got two columns with lots of data in each. Some unique, some the
same. Short example:
I would like to create additional columns with the following
Col C (what's common in both A & B)
Col D (what's in A but not B)
Col E (what's in B but not A)
Any help is appreciated.
These are all array formulas. **Array formulas need to be entered using the
key combination of CTRL,SHIFT,ENTER (not just ENTER)
> Col C (what's common in both A & B)
Ass...sales transaction batch entry stays after saving
After you save a sales transaction assigned to a batch, the batch previously
entered stays there and if you try to pull up another transaction you have to
delete the blank sales transaction screen before it will pull up the
transaction you are trying to pull up. It seems to me to reduce key strokes
when you save a sales transaction the batch field should then be blank and
the system should allow you to pull up another transaction from the magnifing
glass and not have to delete something that is not even there.
This post is a suggestion for Microsoft, and Microsoft respo...How do I have a cell update itself only if it's currently blank?
I need to track to retain the first value of a date field. Each time
I load the worksheet the date field has the potential to change, I
need to keep track of the initial date in addition to the current date
that is in the field.
I have date columns called ASSIGNED and 1st ASSIGNED. The initial
value of both will be blank. At some point the ASSIGNED will be
populated with a date. When it is populated for the first time I want
to save this value in the 1st ASSIGNED column.
I need something like:
IF ( ISBLANK(ASSIGNED), "", ASSIGNED )
in my 1st Assigned cell, however, I do not ...Changing Font Colour if cell is certain word?
Can I make the font of a certain cell change it's font colour to Red if
the word in the cell is, example, "Fail"? I want the change to apply
wuming79's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36037
View this thread: http://www.excelforum.com/showthread.php?threadid=560169
Use "Conditional Formatting". It's on the Format menu
Software Tester & Musicia
-----------------------------------------------------...Can I link *.jpg files to Excel cells?
I would like to connect pictures to cells on a spreadsheet. Ideally, I'd
like to connect more than one to a cell. Or to its row. That would work OK
Can I do it?
You can hyperlink a cell to an image stored elsewhere or to an image stored
within the workbook.
How you do it depends upon the purpose of the linking and the ultimate results
you are looking for.
Gord Dibben MS Excel MVP
On Fri, 25 Jun 2010 17:26:13 -0400, "LAS" <FakeMail@Hotmail.com> wrote:
>I would like to connect pictures to cells on a spreadsheet. Ideally, I'd
>lik...GP10 Transaction Lists don't include a BatchID column
GP10 has alot of new functionality within the 'Lists' area via the use of
ribbons etc. to perform changes to documents outside of the batch
environment. However the logical way to enter and group Transactions is still
via the use of Batches. Within the list area I can't find a way to filter or
sort my transactions by BatchID or at least see which batch the transaction
belongs to without drilling into the document.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the ...Adding random cells togethr
I want to be able to add
y2,aa2,ac2,ae2,ag2,a12,ak2,am2,ao2,aq2,as2,au2,av2,ax2,az2 together and
return the result in bb2
I am using =sum(y2,aa2,ac2,ae2,ag2,a12,ak2,am2,ao2,aq2,as2,au2,av2,ax2,az2)
however I only see the actual formula returned in the cell not the result. I
have been able to do this in the past. Can anyone help with this problem?
Perhaps BB2 was formatted as text before you entered the formula. Select it
and hit F2 and Enter.
"Kaylean" <Kaylean@discussions.microsoft.com> wrote in message
news:0DF3C2DC-8918-...cell ranges in formulas
When referencing a cell range in a formula, is there a way
to represent the cell range by referencing another cell.
What I want to do is this, for example:
say cells a1 thru a100 contain a number of values.
I want to sum a certain subset of these today, but
tomorrow I want to change that subset, so I'd like to
enter the starting and ending point for my sum range into
another cell, say b1 & b2
So if I enter the text "a6" in cell b1, and "a25" in cell
b2, my sum formula should look like =sum(a6:a25). This
give me the flexibility to change my sumrange very e...Rename a sheet tab when cell contents change
I would like to rename a sheet tab when a specific cell's contents
change...is this possible to code? I am using Excel 2003 (but need some
"Michelle" <miki@spam_me.net> wrote in message
> Hi all,
> I would like to rename a sheet tab when a specific cell's contents
> change...is this possible to code? I am using Excel 2003 (but need some
> backward compatibilty...)
>...Diagonal Cells format
How do I format the Label Row of a spreadsheet to be 45 diagonally instead of
the standard horizontal format?
select the cells, choose format / cells - go to the alignment tab and play
with the little dial on the right hand side of the dialog box (or type
numbers in the box underneath).
"Ross" <Ross@discussions.microsoft.com> wrote in message
> How do I format the Label Row of a spreadsheet to be 45 diagonally instead
> the standard horizontal format?
&g...Separatting Data in Columns
Thank you to all in advance for your clever assistance. Problem: I've data separated by a comma in a column which needs to be in two columns. Is there a quick to achieve this?
Data>Text to columns>Separated>Commas
Microsoft MVP - Excel
"G. Allen" <firstname.lastname@example.org> wrote in message
> Thank you to all in advance for your clever assistance. Problem: I've
data separated by a comma in a column which needs to be in two columns. Is
there a quick to ...Is there a quick reference guide for excel 2003 -2007
I think I have seen one somewhere but can't find it. Is there is a reference
guide showing how to find find things in excel 2007 that are not in the same
place as they are in 2003?
you can give this a try
of download this xls workbook
"Noella" <Noella@discussions.microsoft.com> wrote in message
>I think I have seen one somewhere but can't fi...Formatting and Grouping Columns
In Office 2000 in Excel when you select two columns and right click it
included the option to "Unhide" or "Hide".
In Office XP in Excel I can not select two columns it automatically expands
the selection to include the Merged Row above it which includes all the
columns below - not just the two I selected. When I select just two cells
the right click does not include the option to "Unhide" or "Hide". I have
to go to the format menu, select column and select "Unhide" or "Hide".
Is there a way to select columns without including...Command button on data-entry form
Is there a way to add a command button to a form that displays data for
reference? I'd like the user of the form I'm creating to choose a chemical
abbreviation from a dropdown but be able to click a button and display a
table containing the full chemical name and unit. Or else have a dropdown
with three columns? It's easy to do in Access, but I can't seem to figure out
how to do either of these things in Excel.)
It might be easiest to use a range of 2 columns (why 3???).
Then you can make your combobox have multiple columns--including both the
abbreviation and long name....How to separate numbers within a cells into column
I have search all the web but still cannot find a solution to this problem.
I have a single cell containing the number -> 12345
I wanted to either separate the number 1 into column A, 2 into Column B, 3
into Column C and so on.
Otherwise, if inserting a space between 1 and 2 and 3 and 4 and 5 is also
How to go about it?
Any EXCEL experts here can please advise me?
Thank you very much.
If your value is in B1, then in A1 enter:
=MID($A2,COLUMN(),1) and copy across
Gary''s Student - gsnu200787
I should have said the value nust be in A2, not B1