Build value from cell content & sheet name !
Interesting query (I hope)....
I'd like to create a value in a cell that represents a concatenation of a
value in another cell and the sheet name (tab name).
Example, sheet name is 'A' and value in cell 'M15' is '1162'.
I'd like cell 'M18' to indicate a value of '1162A'.
Any ideas ??
to get sheet_name see http://www.xldynamic.com/source/xld.xlFAQ0002.html
(remove nothere from the email address if mailing direct)
"Neil" <firstname.lastname@example.org> wrote in message
news:dekai3$1p...How to use cell data in formula after splittling cells
I pasted a text document into excel which i wanted in three columns
date, price and volume. I successfully split these up using "conver
text to table".
What I want to do now is use the price data in a formula, but it keep
coming up with "VALUE" error.
Can anyone tell me what I have done wrong and how I can fix it?
Message posted from http://www.ExcelForum.com
sounds like your values are actually stored as text. Try the following:
- select an empty cell and copy this cell
- select your price and volumn data
- goto 'Edit - Paste Special...Formula to say Yes or No based on variance in time cells
I have 2 time cells, F5 and I5, I want a forumla that will return the
result Yes or No if there is a specified difference, 30mins for
example. We have a spreadsheet with the same information coming from 2
sources and need to compare the information to say wether it is similar
or not. Thanks in advance for any help
"KingCreole" <email@example.com> wrote in message
> Hi all,
> I have 2 ti...Code places sum at bottom, require it on top of range
I have a =sum that needs to be inserted one cell above the range (number of
rows will vary but always in column "E"). The code below inserts the code
one cell below the range but I'm not having any luck figuring out how to
change it so that it inserts it one cell above. Any ideas?
but I require
x = Range(ActiveCell, ActiveCell.End(xlDown)).Count
ActiveCell.End(xlDown).Offset(1, 0).FormulaR1C1 = "=sum(R[-" & x &
Dim iLa...Country Prefix in same cell
I'm trying to pull country prefixes for matching countries from the second
sheet that has recurring countries with respective prefix to the first sheet
that is not recurring, prefix for same country in one cell separated by comma.
Please see attached link for a visual description.
Will appreciate your input.
try using a Pivot Tablej
or a macro solution
of course this is just using the second sheet as input in creating...filtered cells
I've got in sheet 1 and 2 a autofilter and I want to copy the filter results
to sheet 3. Is that possible and does anybody know how to do it?
You need to copy visible cells only once filtered required data select edit
goto special select visible cells ok then copy this to sheet 3
> I've got in sheet 1 and 2 a autofilter and I want to copy the filter results
> to sheet 3. Is that possible and does anybody know how to do it?
I don't exactly know wh...keeping cell references during sort
I have data that I am summing. For example, I have B1 =SUM(A1:A3).
Then B2 = SUM(A4:A6). I need a way that if I sort the data in column
the cell references in Column B update. So if my B1 data is no
contained in A5, A12, A15, my B1 formula will update to B1 =SUM(A5
A12, A15). Is there a way to do this?
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com
The formula in B1 won't change when column A is sorted -- it will
continue to sum A1:A3.
You ...Insert copied cells above hidden Named Range
I'm not sure what happened here - this worked in the past, but suddenly there
is a problem with "Selection.Insert Shift:=xlDown".
The "record end" named range is still there, so it's not that. The marco is
supposed to go to "RecordEnd" which is hidden, unhide it, insert the copied
cells while pushing "RecordEnd" below the inserted cells. It worked before -
anybody know what I'm doing wrong?
Selection.EntireRow.Hidden = True
...Copy cells to variable number of rows
I need to copy a formula (that a macro has entered at W3 & X3) down to the
last row for that particuliar wk. I've figured out how to get the number of
rows for that wk( in this case i=93, i will change for each wk) but can't
figure out the syntax for the Range:
Range("W4: ? ").Select
Range("W4: ? ").Select
"Acct Supr - DCTC" wrote:
> I need to copy a formula (that a macro has entered at W3 & X3) down to the
> l...Summing instances of text across worksheets
Hello, I have a workbook with separate sheets for each month. The sheets
contain a list client names (entered exactly the same on each sheet). I need
to summarize how many times a client name appears in total. For example, John
Brown may appear Jan & Feb (=2) where Mary Brown may appear Jul, Sep, Dec
I've been trying pivot tables without luck. Hoping someone can help.
Thanks in advance!
Never mind, I consolidated all the worksheet data into one and the pivot
table works fine.
> Hello, I have a workbook with separate sheets fo...How do I create a bulleted list text format inside cell?
I am collecting data and want to format the text inside the "notes" (my
title) cell so that it can be more easily read. I would like to make a
bulleted list. There are several cells that need this formatting. Is this
There is no help for this. You have to do it manually.
"DEH" <DEH@discussions.microsoft.com> wrote in message
> I am collecting data and want to format the text inside the "notes" (my
> title) cell so that it can be more easily read. I would...Question 2:Color cell using conditional formatting
Using conditional formatting to change color of cell if
something is entered works! (thanks Anonnymous!)
Now, how woudl I go about to change the color to red if
data entered starts with a 1, and change to yellow if if
it starts with a letter. I have seen that you can add a
2nd condition in conditional formatting, but I cannot find
what formula to use. Or should I use cell value is?
For the 1st condition, use:
For the 2nd:
...Convert hours to minutes within cell?
Is there a way to have a value typed into a cell automatically converted
from minutes to hours? Not a formula - at least not in the usual sense -
since the formula would be overwritten.
Thanks in advance,
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
If InStr(1, .Text, ":") > 0 Then
.Value = .Value * 24 * 60
.NumberFormat = "General"
...summing adjacent data in a worksheet
I have a column of data and I need to sum the values in adjacent cells when
they are bounded before and after by cells that contain a value of 0. Then I
need to sum the next group of cells bounded by zeros. So I want a column with
subtotals rather than one total. The number of cells in one group may vary.
I've tried using the sumif function, but what I want doesn't seem to quite
I'm using Excel 2007.
So, you have something like this:
and you want to get something like this:
...How to Tab over to a specific cell... not the adjacent one?
In order to make a form more user friendly, I would like
to allow the user to use the "Tab" key to jump from one
input field to another (unlike moving to the adjacent
Is this possible?
Thanks in advance for your suggestions.
Lock all cells> unlock desired cells>lock worksheet>test
"SK" <firstname.lastname@example.org> wrote in message
> In order to make a form more user friendly, I would like
> to allow the user to use the "Tab" k...cell value
How do I get a cell value - I want to use the text in P3 for the subject line in an email using .sendmail "address@address", cell P3 text her
For more examples visit my website
Use a cell containing an email-address like this
..SendMail Sheets("mysheet").Range("a1").Value, _
"This is the Subject line"
Use also a cell for the subject like this.
..SendMail Sheets("mysheet").Range("a1").Value, _
Regar...Selecting X cells based on answer to formula
I am trying to put together a spreadsheet that performs a couple of tasks.
The first is basic resourcing which i am ok with, sort of how many heads and
how many hours available means X cases will be done.
The next thing I need to do is use the X cases result to identify from a
list the oldest date and work through sequentially X dates in that column,
which will not be consecutive, and tell me what the date of that case is. So
if there were 10 cases done I would like one cell saying the oldest case
worked on was - first date in column. And following doing the 10 cases I'd
lik...how do i use the data in one cell as the row number of another cel
in one of excell sheets i have formula in one cell. in this formula i have
a reference to a cell whose row number is the data contained in another cell.
how do i extract this data to reference my destination cell.
thanking in advance
See if this helps:
Your formula would be more like: =INDIRECT("C"&A1)
So, if you have a 4 in cell A1, this will return the contents of C4.
"vidhya" <email@example.com> wrote ...Hide/Delete entire rows based in the content of one cell
Hello all. I have a spreadsheet that is over 500 rows long. As it is I have
no use for all of the rows at the same time and have to keep hiding and
showing them as need arises. Is there a macro to hide chunks of it based on
the value of one cell of the row? In other words, en each row I will have a
formula like =if(a1=0,"HIDE","") and this value will tell the macro wether to
hide the row or not. I tried case.select but it takes a LONG time and I would
have to write a piece of code for every line. FYI, the rows that need hiding
will be in sequence, in other words, fro...Help with cell totals
My problem is that I have two rows, one contains the manufacturers
name, and the other the amount that manufacturer supplies. What I need
to do is get the sum of the amount supplies for each manufacturer.
Company 1 8
Company 2 5
Company 2 7
Company 1 2
Company 1 10
Company 2 12
blackie's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15223
View this thread: http://www.excelforum.com/showthread.php?thre...2 Cells on 2 Sheets
I have a workbook with 2 sheets.
Currently - Sheet 1, A1 is a dollar amount entered by the user
Sheet 2, B1 references Sheet 1, A1 and is locked.
Now - We would like the user to be able to change Sheet 2, B1 and have
it update Sheet 1, A1 and vice versa. So that the user has the option
of changing the dollar amount in 2 places, rather than on Sheet 1
Is this possible?
Thanks in advance!
You can do it by using an event macro that looks for changes. But this can
easily break if the user doesn't allow macros to run--or even turns off events.
I wouldn't use this....Linking chart parameters to cells
Is anyone aware of a way of linking chart parameters
normally set in the various dialog boxes, to a value in a
cell. For example, suppose I'd like the value (Y) axis
minimum and maximum value to change based on the value of
a certain formula, can I somehow link those values to a
cell in the spreadsheet. If not, it'd be cool if I
could. Maybe the next version of Excel?
>Is anyone aware of a way of linking chart parameters
>normally set in the various dialog boxes, to a value in a
>cell. For example, suppose I'd like the value (Y)...how do I insert a filename into a formula from another cell?
I have a spreadsheet with a filename in a cell.
I would like to reference that filename in a formula. I can't seem to get
it to work. "+cell number" doesn't work.
You would normally use INDIRECT to do this, along the lines of:
where A1 contains your filename (with the .xls extension) and you are
trying to return data from C2 on Sheet1 of that file. However,
INDIRECT will only work with files that are open, so you would have to
have the file open for this to work.
Hope this helps.
On...Conditional format to determine if last character in a cell is a number or a letter
I have a spreadsheet where I enter weights. The weights can be any
whole number 1-99999. I would like to be able to flag suspicious
weights using conditional formatting by putting a letter as the last
character, such as 123a, or 57b, 4471c, etc.What conditional
formatting formula can I use to accomplish this?
Assuming your weights are in column A, starting with A2, highlight the
cells in that column with A2 as the active cell, and use this formula
in the CF dialogue box:
Then click on the Format button a...Return Range Based on Cell Value
This topic has been covered in varying degrees, but the problem is
that I do not want to sum or count the range. I want the range itself
returned as the value.
For instance in A1 is 7. i.e. July
In B1-B12 are dates 201101, 201102, etc.
I need a formula that will count out the cells and return the range
based on that value in A1. In this case it would return B1:B7. This of
course would be nested in another formula.
On Tue, 4 Oct 2011 17:18:20 -0700 (PDT), Pablo < > wrote:
>This topic has been covered in varying degrees, but the problem is
>that I do not want to sum or count the...