Data, lookups, and sums
I have an Excel spreadsheet as follows:
Column A Dates in dd/mm/yy format
Column B Additional data (text)
Column C Group of figures (a)
Column D Additional data (text)
Column E Group of figures (b)
In another spreadsheet I have:
Row 1 Months going horizontally in mmm-yy format from Feb-04 to Jan-05
Row 2 Sum of figures
I would like the sum of figures to be calculated as Group of figures (a)
and (b) depending on what the corresponding date is.
I thought this would be similar to a lookup table. However, I need to be
able to add the sum of the two figures, and I need to ...Have user input converted to uppercase in same cell as input?
Noob Alert! Very new to spreadsheets! I have a simple form that requires
data selected from a text description to be entered in a cell to give us a
special product code.
The code is upper case in the description but ppl are entering it in lower
case, I need to convert the entry to uppercase to match our entry system, but
I need a function to convert and display the converted text in the original
I looked at =UPPER function but that displays converted text elsewhere, if I
use it in the same cell as input I want converted I get the circular error
Can someone perhaps ...Open .wpd format files
How do I open .wpd attachment in Outlook 2002? I do not have Word Perfect,
only Word on Windows XP computer.
"Suinee" <Suinee@discussions.microsoft.com> wrote in message
> How do I open .wpd attachment in Outlook 2002? I do not have Word
> only Word on Windows XP computer.
So where are you getting stuck? Word can open WordPerfect 5 and 6 files....
try saving the attachment, open Word, in Word, do a File/Open and pick Files
of Type - all files, it might open, or there might be ...Nested formular to give a word answer based on 2 cells
I have a s/s which I want to produce a statement based on 2 cells
G170 is a cell that contails a sum (if cells above are filled it it will add
I170 contains a cost
if G170 & i170 are blank then do nothing
If G170 is greater than 0 and I170 is greater than 0 then again do nothing
but if G170 is greater than 0 and I170 is = < 0 then "No Charge Made"
Can anyone help all I get is FALSE
I have tried =if(g170<=0, and (i170=<0,No Charge made",""))
=IF(AND(G170="",I170=""),"",IF(AND(G170>0,I170...Custom Number Format #7
Hi I have a column in Excel that needs to be converted as
follows, I think I can do this using "Format" - "Cells" -
"Custom Number" but am not sure what to type in so it
keeps the first character as text followed by 5 digits
(adding a zero to the end)
The account numbers need to become 6 digit (zero at the
end) but retain the starting letter
If you just want to display the value with an added zero, use
You can convert the numbers using a helper co...some kind of Conditional format?
I've got an issue where I need a formula that when I put a date (or any
value) in cell A1, the text 'Closed' is automatically entered in cell B1
- I'm sure I've seen it somewhere before (probably used it too), but
I've searched in vain - can anyone help?
Not conditional format, but in B1 you can use the formula
"Dave" <email@example.com> wrote in message
> Hi all,
> I've got an iss...problem with conditional formatting and VBA (excel 2007)
i have a problem with conditional formatting and VBA (excel 2007).
at first here is a screenshot of my conditional formula
as you can see, the formula depends on B1 but it is relative. so far so good.
but if i go through the cells with VBA the formula for all cells is the
same, (e.g. =B4="yellow")
if i do the same thing in excel 2003, vba shows the correct formulas:
is this a known bug or do i something w...Auto Formatting Custom Number
I'm creating a form where you can enter a credit card number. I set a
custom number for the cell and described it as ####-####-####-####.
You can enter a 16 digit number just fine, but when you tab away from
the cell and the custom number takes place, it changes the last digit
to a "0". Has anyone else encountered this? How do I fix it?
CSBUG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28846
View this thread: http://www.excelforum.com/showthread.php?threadid...Create a cell with only PART of another cells data
I have a column (D) with the following formatted data
I want to create a cell (column) that ONLY contains the last 4 digits
Can some one please tell me how to accomplish this.
__________ Information from ESET NOD32 Antivirus, version of virus signature database 4443 (20090921) __________
The message was checked by ESET NOD32 Antivirus.
assuming the data starts on row 1. If you want it as a number, then
you can do this:
Copy down as required.
Hope this helps....How do I edit directly in a cell
In earlier versions pressing F2 enabled editing directly in a cell. How do I
do this in 2007
Office Button > Excel Options > Advanced > Allow editing directly in cells
Then F2 will work as desired.
Gary''s Student - gsnu201001
> In earlier versions pressing F2 enabled editing directly in a cell. How do I
> do this in 2007
The shortcut-key remains same for XL2007
> In earlier versions pressing F2 enabled editing directly in a cell. How do I
> do this in 2007
...When entering data into excel spreadsheet cell, the page just jump
I am trying to add data to an existing worksheet this morning. I can click
on the clel, but when I try to enter the data, the worksheet "jumps", am
unable to enter any data. What have I done wrong?
If you are making entries using the numeric keypad, it sounds like you turned
your Num Lock off. Try pressing NUM LOCK (there should be alight that comes
on above it when you do) & try again.
> I am trying to add data to an existing worksheet this morning. I can click
> on the clel, but when I try to enter the data, the worksheet &qu...Help with date format
When I use this function =now()-1 it returns yesterdays date and time less
one day in the format that I choose in the format menu.
This is my problem. I want a formula like this.
"ICSSA Daily Report For "&now()-1
I would like the result to be ICSSA Daily Report For 12-Aug-05
The result that I get is ICSSA Daily Report For 38575.50547
I need to format the date number for the date above but have not been able
to figure out how to accomplish this.
="ICSSA Daily Report For "&TEXT(now()-1,"dd mmm yyyy hh:mm:ss")
adjust the ...cell color does not show
When I fill a cell with color the color does not show on
my worksheet but when I change to print preview I can see
the color. How can I fix this?
If the high contrast setting is turned on you won't see the fill colour.
There's information in the following MSKB article:
OFF: Changes to Fill Colour and Fill Pattern Are Not Displayed
> When I fill a cell with color the color does not show on
> my worksheet but when I change to print preview I can see
> the color. How can I fix this?
Excel FA...Cell formating
How do I format a cell such that I see a number say 40000000.00 as
IndianGuru's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27382
View this thread: http://www.excelforum.com/showthread.php?threadid=469097
Click Format then Cells. Select the Number tab.
Click on Custom in the category box and in the Type box type #,###.00
"IndianGuru" <IndianGuru.firstname.lastname@example.org> wrote
in message news:IndianGuru.1vngya_11272...converting general cells format to text
I'm having a hard time converting already existing general cells into text
format. I'm trying to change it into text format so SAS will be able to
recognize it. Without the "green indicator", SAS, for some reason, will treat
it as a "general" cell even though I've saved it and changed the cells into
text. Any suggestions?
Sometimes when you type a number into a cell and then change the format to
text, it does not "set in" imediately. Hit F2 and then enter on each cell
and it should change it for you. Hope this helps.
"junkgrrl" wrot...Locking Named Cells
I have a lot of named cells in a workbook. Can I lock the name of th
cells but allow users to change the data in them?
I do not want them to be able to change the cell names becuase o
references to them elsewhere in the workbooks.
wjoc1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1528
View this thread: http://www.excelforum.com/showthread.php?threadid=47473
Are you sure that the users are changing the names? Maybe they're just adding
another name for that range...Compare cells (advanced)
I need to find a smart solution for comparing cells in an Excel file.
To make a long story short, we usually receive excel sheets with
thousands of rows. From a certain data there we make our own
extraction from our database and put that in the same sheet, next to
the other data.
Then we need to compare some data, to make sure that it is correct.
Lets say that we are comparing names. The problem is that VERY often
the two cells containing the names are not _exactly_ the same. There
might for example be one letter misspelled in one of the cells e.g.
(Mike Brown/Mike Bruwn) or in one ...Using a VLookup "type" reference but the result is the CELL not th
I'm using a VLOOKUP to help me select a date range from another work sheet.
ie 6/01/04 VLOOKs up a date on another sheet and it's corresponding revenue number. The same is applied to 6/30/04. I'm trying to sum the revenue in the cells (days 6/01-6/30) via the VLOOKUP formula, but the VLOOKUP only returns the value and not the cell location. I need the cell locations to sum the revenue numbers in between. ie. the revenue numbers between B4 and B10.
Any help would be greatly appreciated
try the following
=SUMPRODUCT(--(A1:A100>=DATE(2004,6,1)),--(A1:A100<=DATE(2004,6,1)...Combo box date format
How to format a date in the text property of a combo box.
Setting the format property to dd-mmm-yy or Medium date always returns
xx/xx/xx even though the row sources is formatted to dd-mmm-yy with the
output of a query.
Are you setting the Format property of the combo box itself on the form? Or
of the field to which the combo box is bound?
What is the query that you're using as the combo box's Row Source? Post the
SQL statement. Which field in that query is the date field?
<MS ACCESS MVP>
"RobGMiller&quo...How do I insert a dot/check mark in a cell in Excel?
This is very basic! And I am having a problem... I want to insert a dot or
check mark in a cell. Where can I find a dot/check mark? I thought they can
be easily found, but I can't find them. Can anybody help?
See if this helps, Sue:
"sue" <email@example.com> wrote in message
> This is very basic! And I am having a problem... I want to insert a dot
> ...Naming sheets from a cell value
Thanks for reading my question.
Is it possible to name a sheet from a cell value with in the sheet? i.e. I
have 12 sheets, each sheet represents a month and the month value is in cell
A1. Can I rename a sheet by refering to cell A1 instead of manually renaming
the sheets to January, February etc.
In article <127785E3-01B4-49B1-9966-8D79F795EFA4@microsoft.com>,
"Tony4X4" <Tony4X4@discussions.microsoft.com> wrote:
> Hi there,
> Thanks for reading my question.
>...Different beginning cell each time for same macro
I would like my macro to begin in a different cell that I
select each time and then have the first step of the macro
select the 12 cells immediately to the right of the cell I
select each time.
Currently my macro runs perfectly except that the macro
will only begin in the exact same cell every time the
marco is run. This means that the same 13 cells are used
by the macro every time. Where as I need the marco to
begin in a different cell that I more or less randomly
select and then the macro should select the 12 cells
immediately to the right of that cell.
I know someone out there...Deleting Cells Option doesn't appear
I have a workbook with several tabs. Each sheet has command buttons and
macros -- but the format is identical.
I want to delete a few cells (not rows, just cells)
On some of the tabs, I can select the cells, Edit / Delete / Shift Up
However, on some of the tabs, I can select the cells and I only get the
option to "Delete Row". I have compared the Options and other settings, and
can't find what would be doing this.
Nevermind -- I finally found it -- I had a Filter that was on. Apparently,
even if the filter is not in the area that you want to delete, it still
Im having a litle problem at formating my emails at entourage. I was
used to Microsoft Outlook, where i could just bring a .html file
previously build and start to use.
At entourage i cant find a place to import a .html file and have my
emails formated with images and css.
Is it possible to build a look for an email in a html file and import
On 6/28/07 7:06 AM, in article
> Im having a litle problem at formating my emails at...How to specify a fixed cell in a formula
Perhaps I am explaining myself in the wrong termonology but when I drag a
cell into multiple cells below it
the formula changes cells. How do I make it from changing cells.
Dave, you need to use a absolute, or mixed reference, form excel help
Switch between relative, absolute, and mixed references
Select the cell that contains the formula.
In the formula bar , select the reference you want to change.
Press F4 to toggle through the combinations. The "Changes To" column
reflects how a reference type updates if a formula containing the reference
is copied two cells down an...