IF formula with multiple data range in cell
I'm trying to create an IF formula that returns data after searching a cell
that contains more than one data range. For ex:
Cell B3 contains GCVW-00001, GCVW-00002
I need the formula in C3 to return Yes if B3 contains GCVW-00001 and I need
the formula in D3 to return Yes if B3 contains GCVW-00002
I'm able to get the formula to return yes or no correctly if cell B3
contains either GCVW-00001 or GCVW-00002, but the formula won't work if it
contains both at the same time.
right now my basic formula looks like:
I want to transfer information from one spreadsheet to another with
different titles on the columns. How do I do this?
I want to direct the values on the column which has a different title
column to the sheet that I want to move it to.
> I want to transfer information from one spreadsheet to another with
> different titles on the columns. How do I do this?
> I want to direct the values on the column which has a different title
> column to the sheet that I want to move it to.
This is more of moving data to another sheet which has di...tricky problem with 14800 rows (collating figures)
I'm trying to sort through a very large amount of data, and to get averages
from it. (14800 ows in the full version !!). What I need to do is
1) Find out how many times each DELAY (column B1) appears for each AIRLINE
(A1) and give a total for each airlines (This is to show which delay affects
individual airlines the most)
2) Add up the CPU time spent on delays for each airline
3) Collate the number of ABENDs-periairline, and CPU time-per-airlines in to
a grand total
I need hedlp with this one as it is totally out of my depth.
A1 B1 C1
AIRLINE DEL...Transposing list of numbers
I'm using Excel 2003. I have a list of 400 numbers in Column A, I would like
to transpose it across 7 columns and 58 rows. Is there an easy way to do
this? I understand I can do the copy/paste special/transpose for 7 at a
time. Thank you.
Dim rng As Range
Dim i As Long
Dim J As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
J = 1
On Error Resume Next
nocols = 7 'InputBox("Enter Number of Columns Desired")
For i = 1 To rng.Row Step nocols
Cells(J, "A").Resize(1, nocols).Value = _
I have a MASTER summary worksheet with this layout
07-Sep 14-Sep 21-Sep 28-Sep 05 - Oct Total
No of late deliveries 36 40 20 10 9 115
No of Drivers 18 32 11 11 4 76
getting its info from WEEKLY worksheets eg
03-Sep 04-Sep 05-Sep 06-Sep 07-Sep Total
No of late deliveries 7 8 7 7 7 36
No of Drivers 4 3 1 6 4 18
I would like the master layout to be like :
No of late deliveries No of Drivers
07-Sep 36 18
14-Sep 40 32
21-Sep 20 11
28-Sep 10 11
05-Oct 9 4
Total 115 76
But as the values in the MASTER worksheet ...Creating a macro commad to shift selected rows to the right next c
The command for cutting and pasting a single row looks likes this
There again for another row i need to do the same thing as what i had did
for the pervious.
And i had more than hundred thousand of selected rows to be shift to the
right, cause is a data collected from vendor and the complie data is some
thing...pasting column and row width and heights
When I copy data where the column width and the row heights have been changed why doesn't it keep the column width and row heights?
can't answer the "why" question but
if after pasting the data and with the paste range still highlighted you
choose edit / paste special and tick column widths and then ok you'll be
half way there.
(this works in excel 2000 and above)
"Pasting column width and row heights" <Pasting column width and row
firstname.lastname@example.org> wrote in message
news:53DC4CC1-D4A3-4238-877B-3A937E09E9DF@microsoft...Put a value of a cell in text box
Please need help.
Could i put a value of a calculated cell in a text box. In another sheet of
the same workbook.
Thanks for your help.
I put a textbox from the drawing toolbar on sheet2.
With that textbox selected, I put this in the formula bar:
And when sheet1!a1 changes, so does the text in the textbox.
> Please need help.
> Could i put a value of a calculated cell in a text box. In another sheet of
> the same workbook.
> Thanks for your help.
Click on the Text...How to set Upper case in a column
I have a spread sheet set up in a MS Excel 2002. I know there is a formula
=UPPER. I can use it for any individual cell. But I'd like to use this
formula in a few columns, so the text in each cell of these columns will be
in UPPER case.
May someone please tell me how to use formula within a column? Step-by-step
instructions will be appreciated.
Fort Myers, Florida
I do NOT recommend you do this but if you insist,
Right click sheet tab>view code>copy/paste this>change column K to suit>SAVE
Now anything you type into that column will be change...TSQL Transpose
I'm trying to create a query that does a real transpose similar to Excel
function - it is slightly different from Pivot/Unpivot functionality of SQL
Basically, I have a date dimension table and I need to list corresponding
dates, separated by coma for each month. So, for January 2010, my results
would be something like this:
2010-01 01/01/2010, 01/02/2010, 01/03/2010...01/31/2010
What would be a way to achieve this?
Seems you just need to concatenate the date. Here is method using FOR XML PATH:
SELECT DISTI...Go to the last-new row in a datasheet view
What is the code to go to the last row where is added a new record
when you type something?
I tryed adding a record to the form recordset but I don't want to
leave that record empty, I just want it to be ready to load data into.
Greetings from Paraguay.
Claudio Bogado Pompa.
Use the GotoRecord method. It is explained in VBA Help. If you are using a
macro, then it is the GotoRecord action, also in VBA Help.
Dave Hargis, Microsoft Access MVP
"Claudio Bogado Pompa" wrote:
> What is the code to go to the last row where is added a new record
> when you type something...Cell formatting
I cannot get my cells to format correctly. When I type text in a cell it
automatically truncates instead of overrunning into the next cell. I've
already looked at the format cell settings and all the boxes are unchecked.
Any suggestions? Thanks!
Perhaps the adjacent cell isn't empty. Even a space .Try Delete.
mvpearl omitthisword at verizon period net
"fnht451" <email@example.com> wrote in message
>I cannot get my cells to...Rows and Columns Backwards
When I open excel, the rows are on the right side of excel instead of the
left. Also, the columns (Column A) starts on the right and goes to the left.
This only happens with a new workbook or sheet.
Right to Left
Regards Ron de Bruin
"jm" <firstname.lastname@example.org> wrote in message news:4D7EFB96-F932-4B6A-B656-13BB9F2335F2@microsoft.com...
> When I open excel, the rows are on the right side of excel instead of the
> left. Also, the columns (Column A) starts on the right and goes to the left...cell contents revert to 0 when i click on the next cell
I put a number into a cell click on the next cell and the first cell reverts
to 0. If I format to number with 2 decimal places it will be ok but when I
try to take out decimal places it goes back to zero,
You haven't said what number you are trying to put into the cell, but I
suspect that the number is less than 0.5.
A quick test shows that if you set the cell to no decimal places then enter
a number less than 0.5 it is displayed 'rounded down' so it will show as
zero, if it's 0.5 or above it displays as 1.
If you need to put numbers less than 0.5 into youe c...Row height
anyway to copy row heights from one sheet to another, like you can with
colums widths on paste special options?
The only manual way is to copy the whole rows
Regards Ron de Bruin
"Steven" <email@example.com> wrote in message news:3mXfc.2374$UG1.firstname.lastname@example.org...
> anyway to copy row heights from one sheet to another, like you can with
> colums widths on paste special options?
> Many thanks,
Steve, I just used the format copier to copy row heights from one ...Summing and Averages with non-numeric cells
I have a 7 question survery with the following possible responses
We then take ther responses and average them. I'd like to eliminate the N/A
responses from the denominator and from the total sum. I currently have 1
cell for this operation - which doesn't work when the response is N/A since
it's a simple (sum)/7 equation.
Thanks for any help!
Try it this way
> I have a 7 question survery with the following possible responses
>...2007 pivot table display data in row label
I have data in a row label that I want displayed as currency ALL the
time....BUT...when I refresh, the data changes back to the default. Any
Format the cells as Currency and save.
Please hit Yes if my comments have helped.
> I have data in a row label that I want displayed as currency ALL the
> time....BUT...when I refresh, the data changes back to the default. Any
Also, right click on the PT>Table Options>uncheck Autoformat table
"...apply parameter query to multiple rows
I have created a parameter query that takes a single cell as a
parameter and fills in the adjoining cell with data from an external
ODBC connection. What I need is to be able to fill down the query so
that it performs the same functionality for all of the cells below the
original source cell. I have tried many variations but have had no
success. Any help would be greatly appreciated.
...Create three columns of data from the same field
I have a report that lists the names of people who have been to our store in
the past six months. I need to send this out to someone. It take about
three pages and only has one column (Customer Name). I would like to somehow
create a one page report that lists this one column three or four times. So
there would be three rows. The first row would be customers 1-40, the second
would be clients 41-80 and the third would be clients 81 + the remaining
clients. Is there a way to do this?
You can easily create multi-column reports in Access using File->Page ...Changing cell contents so that Excel recognises it as a date
Is there a simple way to convert data in to recognisable date format? What I
want to do is calculate the difference between start and finish dates, but
the data has already been entered and excel is not recoginsing it as a date
even when it has been re-formatted as a date.
I would be so grateful for any help! :-)
Changing the format the cell won't change the value of the cell.
After you reformat the cell, try reentering the data. Depending on what you
have in the cell, hitting F2, then enter may be enough.
> Hi there,
> Is there ...How do I keep rows a1-a2 always at top of screen when on row 50
How do I keep rows a1 and a2 always at top of screen even when I'm on row 50
or 75, etc.
I know it's a simple command but can't figure it out. I have older version
Place the cursor in A3 cell and press Alt+W+F (Window>Freeze Panes).
Remember to Click Yes, if this post helps!
> How do I keep rows a1 and a2 always at top of screen even when I'm on row 50
> or 75, etc.
> I know it's a simple command but can't figure it out. I have o...excel cell text input
I use excell to build a batebase up, and I wish in certain columns to have
the first character to be in uppercase, how do I do that?
I am a newbie.
ASAP Utilities, a free Add-in available at www.asap-utilities.com has a
feature that will do this nicely.
Vaya con Dios,
> I use excell to build a batebase up, and I wish in certain columns to have
> the first character to be in uppercase, how do I do that?
> I am a newbie.
> Thank you
...FRx Open Rows error
FRx works on some workstations and user combinations but not others. Report
viewing is fine. We get an error when some users try to look at the columns
or rows by clicking Open Rows or Open Columns.
It is a network share. We are not using Frx security.
What is the error message? What version of FRx are you using? Have you
tried serching for the error message in kb?
"Richard Slauenwhite" wrote:
> FRx works on some workstations and user combinations but not others. Report
> viewing is fine. We get an error when some users try to look at the columns
> or rows by clic...a quick way to hyperlink 2000 of cells ?
I have an excel file listing more then two thousand paintings and
I now want to hyperlink all cells from column A to jpeg pictures of
these paintings. I know how to do this, but it takes such a long time
every cell individually.
Is there no way to get this done in a kind of batch like operation, or
is there another program where I can copy my excel database and get
the job done more
Any reaction is very much appreciated.
you could use the HYPERLINK formula in a helper column. how are your
Fr...Speciefie table and not cells to sum
I'm trying to make a summary in a specified tabell without
have to specifie what start and end cell. So far I have done like this
but I want something like this
so that I can specifie column and tablename.
I hope someone knows howe to do this.
I do not re ognize which language version you using.
Thank you so much for your help, it wo...