Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Why use a dollar sign in Mid function

Hi

I'm using the mid function to return a character.

I was wondering what is the difference in Mid and Mid$ ?

I saw somebody using the dollar sign, and i can't find the reason for this.
For example


	VB:
	
Mid$(baseString, Int(Rnd() * Len(baseString) + 1), 1) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Post your answer or comment

comments powered by Disqus
I'm having issues with the dollar sign format in excel. I currently use eBay's File exchange format (FEF) and I have a template in excel for my inventory. Under the price column, I need to enter several different prices to look like this $1.00-Basically a dollar sign followed by a decimal point and two places to the right of it.

Because this info is being converted into an html format, this format cannot use text to make it look like the above example. In fact, when I formatted it to text, my html came back with =inventory!RC[-4] under the price header. Also, I used custom, and currency formats, but when it is exported to html, I get results such as 1 (no dollar sign or decimal) So, the formatting inside the cells are useless in this situation. I was only successful once, with the help of a friend to convert this. He basically cut and paste onto notepad and inserted a space, then pasted back into excel. In excel he removed the space, and it worked. Unfortunately, I am not able to replicate this. I have excel 2003, if it matters. I also included an example of what he did, in column D. My prices (the unformatted ones) are in column C. I'm sure that there is an easier way to accomplish this. Thanks in advance!

How do I align a column of numbers where the first number has a dollar sign?
Like this,
$280
60
50
Total $390

When I try to add the dollar sign, Excel "shifts" the number slightly more
to the left of the other numbers, resulting in a slightly uneven alignment.
Also, I'd like the dollar sign number to align w/ the total amt. What's the
best way to do this?

If I use ">32" as the criteria in my sumif formula, I receive the correct
answer.

If I use >C3 as the critera, I receive zero as the answer.

Is some way to use a cell reference in the criteria? Excel help says that
the criteria can be a "number, expression, or text."

Just wondering how to write the vba code for placing the dollar sign in each cell in a particular column (eg.column A)...
thanksssss
every help is appreciated

How do I align a column of numbers where the first number has a dollar sign?
Like this,
$280
60
50
Total $390

When I try to add the dollar sign, Excel "shifts" the number slightly more
to the left of the other numbers, resulting in a slightly uneven alignment.
Also, I'd like the dollar sign number to align w/ the total amt. What's the
best way to do this?

I have formulas in multiple cells that have dollar signs in the formula. So
when I copy and paste the formulas won't change. However I want them to
change. So is there a way I can clear the dollar signs in multiple Cells at
once So I don't have to do one by on?

Hi all,

I have to combine / add up 2 regions of data.
To do so I have made a bit of code that determines the "old value" in a cell, then copies the 2nd value in the activecell, and as last, adds up the previous value.

This works if I use a fixed number for the column_index. However, this column_number changes for each column.
Therefore I want to use the value of "j" as column_index.
Simply adding this to the code doesn't seem to work.
This is the code as I thought would do the trick:

    Dim i As Long
    Dim j As Integer
    
    For j = 1 To NumberOfColumns    		‘NumberOfColumns is determined in 1st part
        
    For i = 1 To NumberOfRows		‘NumberOfRows is determined in 1st part

        Dim OldValue as long    
        OldValue = ActiveCell.Value
        
        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1, C10:C17, j + 1, FALSE)"  

        Dim NewValue As Long
        NewValue = ActiveCell.Value

        ActiveCell = OldValue + NewValue     'This replaces the formula with a value
        ActiveCell.Offset(1, 0).Range("A1").Select
    Next i
               ActiveCell.Offset(-NumberOfRows, 1).Range("A1").Select  
    Next j
I've tried every trick I know to get the column_index working: adding a ", declaring a new variable, (, [, etc etc.
None of these work.

What can I do to make it work ??

Thanks in advance
Hein

I just want it to have a dollar sign in front of the number the formula above is outputting. Any ideas? And no, I can't hit
the dollar sign on the menu, that does nothing

I am trying to use a cell value in a vlookup command in order to perform lookups on several documents without re-typing / editing the command. The cell value I want to insert in the command string is a document name (the data to examine is in separate documents and not in the destination document.

If I use a straightforward vlookup command in 'Document 1' to examine data in 'Doc2' I would have something like this:

=VLOOKUP(C1,'[Doc2]Worksheet1'!A$1:H$31,1,FALSE)

As I want to perform this operation for several documents of the same format I want to insert the 'Doc2' name in cell A1 in 'Document 1', so theoretically like:

=VLOOKUP(C1,'[Value from current document cell A1]Worksheet1'!A$1:H$31,1,FALSE)

I have tried various ways to achieve this but am failing each time. Is it possible to use the command in such a way as to achieve the desired result?

Any help appreciated.

Hi

Just a quick question, is it possible to use a string variable in a Excel formula (in VBA)?

Im trying to do the following in VBA


	VB:
	
mortgageSchedName = acName & "Schedule" 
Range("B6").FormulaR1C1 = "=mortgageSchedName!RC[254])" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
the above results in an error because there is no such sheet as "mortgageSchedName", but I want it to access the value of that string variable.

Any way round it?

Thanks again

Hey,

I want to use a cell reference in my page header. I can do it using a macro but I don't want to have any macros in my workbook. Is this possible yet?

Why does a spreadsheet sent in Excel round the numbers when emailed to yahoo?

I am trying to use a date field in a mail merge of an Excel spreadsheet
with a Word document. Every time I do the merge the date on the merge
document comes out incorrectly. I have tried formatting the field in
the spreadsheet as text and all kinds of date format but it never comes
out in the merge document as I want it to be i.e. dd/mm/yy. Sometimes
it does not even resemble a date but is complete rubbish.

Any ideas would be most welcome.

--
triciaodd
------------------------------------------------------------------------
triciaodd's Profile: http://www.excelforum.com/member.php...o&userid=30861
View this thread: http://www.excelforum.com/showthread...hreadid=505302

I'm trying to use a wild card in the following equation:
=COUNT(IF((B7:B24=$B24)*(I7:I24="C*"),B7:B24))
I tested the same equation with out the wild card by replacing it with the
exact text and it worked fine. What am I doing wrong?

Hi.
Is there any way to use a Named Range in the "Source" field in a Data Validation?
I have multiple sheets in a workbook that have the same layout, and I am using alot of different Data Validations. My problem is that whenever I need to change, or add a choice to one of these, I have to go to each sheet and change the Data Validations that are effected by that change.
My hope was that there is a way to use a "Named Range" or something to put all of my acceptable choices, for that Data Validation, in one spot. And make any changes I had to that field.

Hi.

I was wondering if, instead of using the usual cell code such as A1, B12, L14 etc, if there was a way to use a column heading in a formula instead, and have Excel recognize it. This will probably look more clear with an example.

Say I have the following table, with different player abilities:

Name / Shooting / Tackling / Passing
Marc / 4 / 5 / 10
Jason / 5 / 8 / 9
Jake / 10 / 3 / 6

And I want to use a formula of who's the best defender, forward etc.
So for a forward I would use
=(B2*5+B3*1+B4*3)/5+1+3
(the numbers are just modifiers to give one skill more importance than the others)

My question is: is there a way I can write the same formula, but using for example:
=('shooting'*5+'tackling'*1+'passing'*3)/5+1+3

Can I tell Excel to give one column the parameter "shooting", another column the parameter "tackling" etc. such that when I write 'tackling' in a formula, Excel knows what I'm talking about?

That way, it would be a lot easier for me to re-edit the formula later and pinpoint which skills to give more weight to (think that I have to use many many other skill adjectives).

Any help is appreciated.

Hi

I've got an IF statement that checks weather the number in one cell is < 0 and if so I want a minus sign in front of the numbers or letters in another cell.

Cell 1
-123

Cell 2
-7B

But if it's just letters in cell 2 (i.e FE) I get a #VALUE error instead. Suppose that's because letters can't be negative.
But if I still want that minus sign to be there how to I write that in the IF statement?

It looks like this now:
=IF(C39<0;-D43&D41;D43&D41)

Thanks!

Greetings All,

This may be a basic question, but it's giving me trouble.

I'm trying to set up a worksheet where by inputing a value in a cell, it changes the value reference in another cell to a new address.....(confused yet?)

Ex:

in A1, the person would input "24"
then, the value in B2 would equal the value in C24

if they put in "13" into A1, the value in B2 would equal the value in C13

Does that make sense?

Also, can I use a simple equation in it....like subtraction

Ex:

in A1, the person would input "52"
then, the value in B2 would equal the value in C12, which is 52-40

in A1 the person would input "68"
then the value in B2 would equal the value in C28, which is 68-40

Thanks for your help everyone!!!

Sam

I'm trying to use a wild card in the following equation:
=COUNT(IF((B7:B24=$B24)*(I7:I24="C*"),B7:B24))
I tested the same equation with out the wild card by replacing it with the
exact text and it worked fine. What am I doing wrong?

I'd like to use a defined name in a spreadsheet in a header.

I am trying to use a date field in a mail merge of an Excel spreadsheet with a Word document. Every time I do the merge the date on the merge document comes out incorrectly. I have tried formatting the field in the spreadsheet as text and all kinds of date format but it never comes out in the merge document as I want it to be i.e. dd/mm/yy. Sometimes it does not even resemble a date but is complete rubbish.

Any ideas would be most welcome.

I'm wondering if there is a way i could use a cell reference in an internal hyperlink such as

=hyperlink(C:windows:::CELL REFERENCE HERE:::file.pdf,x)

How do I make a single number equal a dollar amount in the next column cell (same row)? Ex: I enter "7" in F4 and G4 equals $70.00. I enter 11 in F15 and G15 equals $125.

Column "F" will always have the value between 1-17. Each number has a specific dollar amount. Column "G" will always have the corresponding dollar amount. How do I do this?

Thank you!
Marty

I want to use a few variables in any Module I run in the current Workbook
without having to enter a Dim/Set command. These variables hold a value that
does not change via the program. If a change is needed I would like to go to
one place to update them. As well, I would like to give their value in the
same place.

I also know that they need to go on the top above any SUB() statement but I
am uncertain which Module they belong in so that all other modules can
benefit from their value. I have Worksheet Sheet Modules and I see a folder
with MODULES.
When I open this up, I have Module1-3. I figured these need to go in one of
these Modules but am uncertain is it matters which one.

I also am uncertain of the exact syntax I need to use to enter this type of
variable.

Each of these variables are currently define in the SUB() as follows:

Dim srcProgramDataInputWs As Worksheet
Dim srcProgramSummaryWs As Worksheet
Set srcProgramDataInputWs = Sheets("ProgramDataInput")
Set srcProgramSummaryWs = Sheets("ProgramSummary")


No luck finding an answer? You could always try Google.