Having info in a certain cell EXACTLY same as in cell on anotherpa
SIMPLY... I want in my summary tab to have the data from A1-P30 to ALWAYS be
the data showing on my first page tab that is in position A1-P30.. Once a
week I move the data in my first page by moving rows up and down depending on
the performance of a certain golfer and what place he now is in overall. This
is driving me nuts!
I know all about
=page1$C$8 but that doesn't seem to work...
> In your summary tab,
> place this in any starting cell, say in B2:
> =IF(ISBLANK(OFFSET(INDIRECT("'page1'!A1"),ROWS($1:1)-1,C...Sorting Area with Merged Cells
In the area A23:G30, each row contains merged cells of column B, C, D, E
and F apart from each other row in the area. I get a "This operation
requires that merged cells to be identically sized" error when running
this macro. Even using the Data/Sort from the File menu gives me this error.
How would I sort an area containing rows with merged cells? Also, the
cells must remain (or return to being) merged after this operation.
I appreciate the previous help concerning clearing the contents from
merged cells using ActiveCell relative references. Unfortunately, my
email/newsgroup ...Linking two cells in two books
I have two workbooks. In Book1, I want A2 to be exactly
the same as B1 in Book2.
The problem is that every night, I run a program which
updates Book2, inserting a new row 1, so the value in B1
is now pushed down to be the value in B2 and a new value
is inserted into in B1. I want this new value in B1 to be
reflected in A2 (Book1). That is, for A2 (Book1) to
always be the same value as B1 (Book2), even when B1
becomes a new row with a new value.
I thought I would be okay if I used dollar signs, so for
A2 in Book1, I have:
But it doesn'...Copying the column heading into all non-empty cells #3
Gord you're a genius!
Thanks so much - that works a treat! Sorry I didn't get back to yo
last night - I'm in London and it was past my bedtime.
The reason I'm copying the headings into the rows by the way, is that
have a database which I want users to be able to search, and it's easie
if I just have have one column listing all the Amenities for each hotel
rather than 120.
Incidentally, my next move is to now merge all the cells in each ro
into one, with the amenities separated by spaces. I've just downloade
a "Merge Cell Wizard", which has accomplished th...Sum of cell which use if array function
I have many cells using if function which result is number.
I'd like to sum all of those cells, unfortunatelly i cant do that, the
result is none.
How can i solve this one?
If your IF formulas contain quotes around numbers, REMOVE THEM.
Microsoft Excel MVP
"Pran" <Pran@discussions.microsoft.com> wrote in message
> I have many cells using if funct...Can you automatically add a comment to a cell
Is it possible to automatically add a comment to a cell
once it has been selected.
The cells currently have data validation to only allow
you to enter certain letters and conditional formating to
change the colours once these letters have been selected.
I would like it to add the current date and time when any
of the letters have been selected using the drop down box
through data validation.
Private Sub Worksheet_Change(ByVal Target As Range)
Target.AddComment ("Date and Time Last modified " & Now())
"Mall...How to return a blank cell if sum = 0
How would I show a blank cell, instead of the number 0 in the cell, if the
formula in that cell results in an answer of 0?
=If(myformula=0,"",myformula), say, +if(sum(A3:g3)=0,"",Sum(a3:g3))
> How would I show a blank cell, instead of the number 0 in the cell, if the
> formula in that cell results in an answer of 0?
> How would I show a blank cell, instead of the number 0 in the cell,
> if the formula in that cell results in an answer of 0?
Another option... if you want zero values to not show **anywhere** on your
worksh...Running a mcro based on value of a cell
I have a spreadsheet that performs End Of Day calculations on Financia
I have a large macro that runs at the end of every day,part way throug
the macro i need to collect data from a column in a worksheet based o
the text that appears in cell T1.
In the YEARLY sheet i have columns of data that have the end of yea
prices of various Financial instruments.
In row 1 across the worksheet are the names,e.g Vodafone,Do
Jones,Nasdaq,GBP/USD etc.Below each name is a column of 20 year
data,i.e A2:A21,B2:B21,C2:C21 etc.
The macro needs to look at the text in T1 and then find the a...Cell update delay
I have a spreadsheet with two cells that are linked to an external value.
However I want one of these cells to only update every second. This is
basically so I can see the difference between what the external value is now,
and what it was a second ago.
Take a look at this:
If you do it once per second, it will (probably) take over control of your
If this information was helpful, please indicate this by clicking ''Yes''.
...Conditional copy cells from one excel file into current
Does a macro (or function or ??) exist to conditionally copy value
from a stored spreadsheet into the current (open) spreadsheet?
Here's the scenario:
Stored spreadsheet is two columnsof text (~200 rows) : Think explici
question in column one, then the correct answer in column two of th
Then my current (open) spreadsheet has SOME of the same questions i
I would like to invoke a [macro?] that, in pseudo code, does somethin
similar to the following:
for each row (question in column 1) of the current spreadsheet
Search in column 1 of *stored* spreadshee...extract covert dollar value in text cell to just dollar value
cell value/contents: 10 for $1,700.00 to just $1,700.00
for Cell A1 use
this will extract everything to the right of (and including) the $ sign
Alex Delamain's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11273
View this thread: http://www.excelforum.com/showthread.php?threadid=275359
On Thu, 4 Nov 2004 10:08:06 -0800, "Kelly" <Kelly@discussions.microsoft.com>
>cell value/contents: 10 for $1,700.00 to ...how to activate a cell if it contains data
I've created a Pvt Table which contains 6 fixed columns and it will show data
depends on how many columns I wanted to see. In addition, I've created
another set of columns to copy data shown on the pvt table. So here is my
question, is there a way to activate cells with data on the Pvt Table?
Here is an example:
If I wanted to see 4 columns of data on the Pvt Table:
Column A Column B Column C Column D Column E
Row1 $27 $30 $49 Total
My other Set of columns should appear like this:
Colum...How do I automatically fill a cell based on another cell in Excel
I am trying to fill an adjacent cell based on the input in another cell.
Here are the particulars. I have created a pull down list in one cell. The
list contains all of the parts that our mechanics order. Now when one of the
parts are chosen I would like for Excel to populate the next cell over with
the price of the part automatically. Can this be done?
I assume you already have a price for every part in the list somewhere. So
what you need is a VLOOKUP formula.
=VLOOKUP(A1,your range, 2,0)
A1 is the cell with the drop down list
your range - range where you have your part list...Preserving formatting of merged cells when hiding columns
I have a spreadsheet that contains columns that my supervisor wants to
keep hidden when sending out to contacts. The problem is, there are
some merged cells further down the sheet that spread across those
columns, and hiding the columns messes up their formatting. Is there a
way to avoid this?
merged cells are the bane of Excel, figure out how to do without them.
Nick Xylas wrote:
> I have a spreadsheet that contains columns that my supervisor wants to
> keep hidden when sending out to contacts. The problem is, there are
> some merged cells further down the sheet that spread across ...Format cells #7
I have Excel 2000, Win ME. Recently the pulldown command to format cells
does not always respond - ie, no box appears, so I can't apply borders or
control number format. The shortcut approach doesn't work either. I've
tried Help..Detect and Repair but the fault remains. Any suggestions
...formula in cells
I'm going to try and explain this a little better and give you the actual
cells that I am trying to calculate:
I have net pay in cell B8,
withdraws in cell C8 and
the Balance in D8.
I would like row D to add B, subtract C and display the balance.
So when I put in the next paycheck on B9, D9 will reflect the amount from
D8, add to it B9, subtract C9 if a value is there and display the balance in
D9. I would like the cells in Column D for the last value entered to be
blank until data is entered into them and not display the last balance the
whole way down the spread sheet. Does this...how to make cells automatically format to entered text size?
i am new to excel and i would like to have it resize the cells automatically
to the size of the text i entered. i know if i click on a cell and do format -
line break it does it for one cell. but how can i have excel do this
automatically for every cell in every workbook?
Hit CRTL + A to select all cells.
To make this the default for all new workbooks and sheets....
Open a new workbook. Customize as you wish. File>Save As Type: scroll down
to Excel Template(*.XLT) and...Automatically Hyperlink Text in certain cells?
Anyone know if it is possible to automatically hyperlink. i looked
under format cells but no such option.
for example if i type: testfile.zip into Cell A1 it will
automatically make the text a hyper link to testfile.zip
You have a couple of options:
#1. You could type in a longer string:
(include the "File:////" and the full path to the file.)
#2. You could use an adjactent cell that uses a worksheet function:
=if(a1="","",hyperlink("file:////c:\my documents\zipfiles\" & a1, "Click m...Cell Echoing Help in VBA
Is there a VBA routine that will echo back the cell
location based on the value in another cell ? For
example, consider the following
A B C D
1 101 110.5
I'm looking for the cell location for the value (110.5)
contained in cell C1 and place the returned location (A5)
in cell D1.
To use a formula on the worksheet....
In cell D1 enter
If Application.CountIf(Range("A:A"), Range(&q...Adding fill to cells only when value is "No" in another cell
I would like to apply a conditional format to a selection of cells that will
change the fill to gray when the value in another cell = "No". So, let's say
"No" was input into cell A1, if so then cells B1 through E1 would
automatically fill with gray. If the value in A1 is blank or "Yes" or
anything else, then no formatting would occur in those cells because data
would need to be input in them. Is it possible to do this?
In B1, create a conditional format of:
and set the fill to gray.
Now copy the format of B1 down to E1 ...formating multiple cells
I have an spreadsheet froma data collector that has roughly 42,000 rows and
15 columns of data. Unfortunetly the data collector formats the cells to be
"text" instead of "number". When i go to make this change by selecting all
the cells and changing there format this process can take up to 30-45
minutes. Now this is on a p4 with 2gb of ram. However i do this same
change on my laptop (much slower) and it happens in a matter of seconds.
What gives....why would my work PC be slll slow. I am running Office 2003
at work and Office XP on my laptop. Hopefully someone ...Protect Cells
I have Psion Series 3MX that has spreadsheet software called Sheet. In
Sheet, I can Protect individual cells, so that another user can't alter its
content, but leave specific cells unprotected for the user to enter their
How can I do this with Excel. I have tried, but seem to only be able to
Protect the whole sheet and not leave individual cells unprotected?
If referring to MS Excel workbooks/worksheets..........
By default, all cells are locked when you protect the sheet.
Select the cells you wish to leave unlocked for entry.
Format>...Display contents of cell in another cell as part of text string?
How can I display the contents of a cell in another as part of a text
Such as... "The answer is "B4""
where B4 is the contents of cell B4?
="The answer is "&B4
Note <space> after "is".
Please keep all correspondence within the Group, so all may benefit!
<firstname.lastname@example.org> wrote in message
How can I display the content...How do I put a blank value in a cell not using "" in a formula
Thanks in advance
I have a row that that is sometimes blank or contains a number followed by a
space and two letters eg. In cell A1 I have the value 456 CR
In cell B1 I am using the formula =IF( ISBLANK(A1),0,(LEFT(A1,
LEN(A1)-3)*1)) to strip the space and two letters from the cell. I multiply
(LEFT(A1, LEN(A1)-3) by 1 to convert to a number from text.
I am almost happy with the formula. I want the first part of the IF
statement to evaluate to 0 if the field is blank but do not want to see the
0. If I use "" instead of 0 it is a text value again which causes nu...Locating a specific cell
This is a simplified example of what I am trying to do
I want to display the result for the max and min and also the year and month.
In this case the min is 2 in Feb 2009 and the max is 9 in Mar 2010
A B C D E
1 Jan Feb Mar
2 2009 3 2 7
3 2010 5 4 9
Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can not...