Ask me anything about Microsoft Excel - Page 9 - Other Topics Forum - Discuss Pop Culture, Arts and Entertainment

Most databases have a separate datatype for large chunks of text. In Access it looks like this is called "memo".

You should probably look at actual document database systems. A document database in Access is probably a bad idea (putting it in Excel is much worse).

Quote

10-04-2010 , 08:37 AM

#202

HUHandEH

Pooh-Bah

Join Date: Dec 2009 Posts: 5,458

What about Alt F11? how much of that do you know?

Quote

10-04-2010 , 12:45 PM

#203

SuperRams

enthusiast

Join Date: Apr 2010 Posts: 98

Edit: jj got there first!

And yeah, virtual document storage seems to be what you want otherwise you'll spend ages doing something that a. won't be as good as non-database document storage/filing solutions, b. wasting your time a bit.

Quote

10-09-2010 , 05:05 PM

#204

joblessmba

journeyman

Join Date: Feb 2010 Posts: 282

I have an interview for a Jr. Financial Analyst position coming up this week.

Is there any Excel things I could study to prepare?

I am comfortable taking quarterly and yearly financial forms and generating forecasts and such.

Just not sure what an analyst would do on a day-to-day basis....seems like the accounting department would handle a majority of the data gathering.

Quote

10-11-2010 , 09:09 AM

#205

subandi

Carpal \'Tunnel

Join Date: Jun 2007 Posts: 6,836

very basic I think but I know nothing about excel.

I also dont have/use it I use google docs/open office.

I have a spreadsheet where every customer has a specific name and a specific number (plus other propeties),

in daily use I only need the name but for some reports I get
stats for the customers where they are only "named" by their number.

is it possible (and how?)
to make it that the program immediately "knows" that i.e. "123" means "subandi" "124" means "peter" and so on, and maybe even auto replaces all "123"s in that specific (name-) row with "subandi"?

aorn Im doing it manually which is easy but slow & very tedious.

thank you in advance for a help!

Quote

10-11-2010 , 10:44 AM

#206

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

Quote:

Originally Posted by subandi

Should be pretty easily done with vlookup.

Assuming a table like that:

Code:

123 subandi
124 Peter
125 Richard
126 Paul

You could choose a field, for example A1, and write the name or number, then you put

Code:

=VLOOKUP(A1,A3:B6,2,TRUE)

in B1,
assuming A3:B6 is the area you got the values in, thats where it searches
2 is the column with the names
TRUE looks for exact matches.

I hope this is right and it should work, given I understood you correctly. (just saw that you were looking for something else, forget what I just wrote)

Does anyone, not necessarily OP, have a page with VBA projects/tasks? I need to motivate myself and learn VBA properly and I do it best by having a specific goal (I work with in a bank and use excel everyday, but we got all kinds of macros and they are too difficult to understand with my limited knowledge).

Quote

10-11-2010 , 11:09 AM

#207

Rinse Agent

journeyman

Join Date: Dec 2007 Posts: 309

Quote:

Originally Posted by Spurious

Should be pretty easily done with vlookup.

Assuming a table like that:

Code:

123 subandi
124 Peter
125 Richard
126 Paul

You could choose a field, for example A1, and write the name or number, then you put

Code:

=VLOOKUP(A1,A3:B6,2,FALSE)

in B1,
assuming A3:B6 is the area you got the values in, thats where it searches
2 is the column with the names
FALSE looks for exact matches.

FYLOOKUP

Quote

10-11-2010 , 11:16 AM

#208

Malfunction

old hand

Join Date: Jul 2008 Posts: 1,719

Don't forget your numbers should be on the left, names on the right (if you're searching for the numbers).
Else you're formula should be:

Quote:

=INDEX(B3:B6,MATCH(A1,A3:A6;0))

Quote

10-11-2010 , 11:18 AM

#209

RacersEdge

banned

Join Date: Jan 2004 Posts: 14,282

Not sure if OP is still around, so anyone who knows...

1) I use a VLOOKUP to convert city names to a number, i.e.

Chicage 1
Detroit 2
San Diego 3

but it always messes up San Diego, - gives it a number close by but not correct. I thought it was related to the spaces in San Diego, but other cities have spaces and they don't always get the wrong lookup value. Any idea what's going on? (I'm using "exact match" also).

2) What are the uses of Excel besides pivot tables that the average business office is missing out on?

3) What is best way to learn VBA? Is there a specific book?

Quote

10-11-2010 , 01:16 PM

#210

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

Quote:

Originally Posted by Rinse Agent

FYLOOKUP

Thanks, thats obviously what I meant

Quote

10-11-2010 , 03:53 PM

#211

Malfunction

old hand

Join Date: Jul 2008 Posts: 1,719

Quote:

Originally Posted by RacersEdge

Not sure if OP is still around, so anyone who knows...

1) I use a VLOOKUP to convert city names to a number, i.e.

Chicage 1
Detroit 2
San Diego 3

but it always messes up San Diego, - gives it a number close by but not correct. I thought it was related to the spaces in San Diego, but other cities have spaces and they don't always get the wrong lookup value. Any idea what's going on? (I'm using "exact match" also).

3) What is best way to learn VBA? Is there a specific book?

1) Whats your formula, sounds like you def did something wrong...

3) I liked Sams Teach Yourself Visual Basic for Applications (easily found as PDF somewhere)....rest I figured out myself (but I'm not great).

Quote

10-11-2010 , 04:17 PM

#212

RacersEdge

banned

Join Date: Jan 2004 Posts: 14,282

Quote:

Originally Posted by Malfunction

I use a named range, so it's just

VLOOKUP(A1,namedrange,2,0) where the city name is in column A and namedrange has 2 columns like I wrote above.

I just tried to get the error again, but couldn't get it - but I'm in Excel 2003 here and the issue was with 2007 at home. I have 32 names in the list and San Diego always gets the wrong value - the others are always correct.

Quote

10-12-2010 , 02:20 AM

#213

Malfunction

old hand

Join Date: Jul 2008 Posts: 1,719

Thats really weird.
I assume "namedrange" doesn't include A1?

Quote

10-13-2010 , 05:28 PM

#214

Hellrazor

grinder

Join Date: Jul 2003 Posts: 640

Quote:

Originally Posted by RacersEdge

Any spaces on the end of San Diego in the table?
Wasn't able to recreate this unless I added the space to San Diego in the namedrange.

Try inserting a =TRIM(Citynamecol1) in the second column of the namedrange and vlookup on that. Or CLEAN() instead of trim if you imported the text from another application that may have some non-visible codes in the text.

Quote

10-13-2010 , 05:52 PM

#215

RacersEdge

banned

Join Date: Jan 2004 Posts: 14,282

I'll try that - probably CLEAN since I did paste this from another source.

Quote

10-13-2010 , 07:25 PM

#216

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by RacersEdge

I'll try that - probably CLEAN since I did paste this from another source.

It's usually the 'space' that causes the problems, ive had issues where i've had to delete and re-do the space and the v-lookup works. you can try finding out the character code of the space then doing a find and replace on that code

i.e.

=SUBSTITUTE(A1,CODE(MID($A$1,4,1))," ")

where $A$1 is the aforementioned "san diego" if you just use "CODE(MID($A$1,4,1))" it should return 32 which is the standard ASCII code for a space

I'm not familiar with whether the clean function would handle this

Quote

10-13-2010 , 08:03 PM

#217

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

For those of you that are NBA fantasy players, i made a macro that will import the top 500 players into excel but you need to have a valid fantasy log in.

1. Open excel, on the data tab go to Get External Data > From Web
2. in the box that opens navigate to fantasy NBA (http://basketball.fantasysports.yaho...sort=OR&sdir=1) and log in and save password
3. close the above box then right click on a sheet tab and select "view code"

Code:

Sub importNBA()

    Dim tmpWS As Worksheet
    Dim fWS As Worksheet
    
    For i = 0 To 19
        Application.StatusBar = i
        Set tmpWS = Sheets.Add
        With tmpWS.QueryTables.Add(Connection:= _
            "URL;http://basketball.fantasysports.yahoo.com/nba/222/players?status=A&pos=P&cut_type=33&stat1=S_S_2009&myteam=0&sort=OR&sdir=1&count=" & i * 25 _
            , Destination:=tmpWS.Range("$A$1"))
            .Name = _
            "players?status=A&pos=P&cut_type=33&stat1=S_S_2009&myteam=0&sort=OR&sdir=1&count=" & i * 25
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlEntirePage
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
        
        tmpWS.Rows("1:120").Delete Shift:=xlUp
    
        For p = tmpWS.Range("A65000").End(xlUp).Row To 1 Step -1
            If Left(tmpWS.Range("A" & p), 1) = "(" Then
                tmpWS.Range("B" & p - 1) = tmpWS.Range("A" & p)
                tmpWS.Rows(p).Delete
            End If
        Next p
        If i = 1 Then
            Set fWS = Sheets.Add
            tmpWS.Range("A1:Z27").Copy Destination:=fWS.Range("A1")
        Else
            tmpWS.Range("A3:Z27").Copy Destination:=fWS.Range("A" & fWS.Range("A65000").End(xlUp).Row + 1)
        End If
        Application.DisplayAlerts = False
        tmpWS.Delete
        Application.DisplayAlerts = True
    Next i
End Sub

Quote

10-13-2010 , 08:10 PM

#218

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by Spurious

Does anyone, not necessarily OP, have a page with VBA projects/tasks? I need to motivate myself and learn VBA properly and I do it best by having a specific goal (I work with in a bank and use excel everyday, but we got all kinds of macros and they are too difficult to understand with my limited knowledge).

mrexcel.com forums

seriously, post questions answer other peoples questions when you have time its like this thread times a million

Quote

10-14-2010 , 01:55 AM

#219

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

Quote:

Originally Posted by zomg

mrexcel.com forums

seriously, post questions answer other peoples questions when you have time its like this thread times a million

Thanks, gonna do this!

Quote

10-14-2010 , 05:40 AM

#220

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

Is there a way to just add something to an array instead of having to define which index? (Java has this)

And is it possible to just take a range like "A1:C9" and put it in an array? Or do you have to go through every single cell and add them?

Quote

10-14-2010 , 07:39 AM

#221

subandi

Carpal \'Tunnel

Join Date: Jun 2007 Posts: 6,836

just want to say thank you to spurious, rinse agent and malfunction,
didnt come around trying it yet because I didnt have to
but it looks like I what imagined and Im sure it will work - so thank you!

Quote

10-14-2010 , 08:18 AM

#222

PolvoPelusa

adept

Join Date: Aug 2008 Posts: 974

Quote:

Originally Posted by RacersEdge

2) What are the uses of Excel besides pivot tables that the average business office is missing out on?

3) What is best way to learn VBA? Is there a specific book?

2. pivot tables/charts based on cubes are insanely useful from a BI perspective.

also, something that's sure to be hot with users who have a decent understanding of relational databases is powerpivot in 2010. publishing to sharepoint is pretty big...offloading processing to the server + rendering the display in HTML keeps users from ****ting the bed.

3. find a project that interests you and dissect it to discover how it works. if you don't know what a line of code does, use google

Quote

10-16-2010 , 12:09 PM

#223

subandi

Carpal \'Tunnel

Join Date: Jun 2007 Posts: 6,836

Quote:

Originally Posted by Spurious

Should be pretty easily done with vlookup.

Assuming a table like that:

Code:

123 subandi
124 Peter
125 Richard
126 Paul

You could choose a field, for example A1, and write the name or number, then you put

Code:

=VLOOKUP(A1,A3:B6,2,TRUE)

I got it kinda working but still requires me to enter/change the 1st variable for each lookup,

what I need is something like

=vlookup(R3,A1:B70,2,false)
=vlookup(R4,A1:B70,2,false)
=vlookup(R5,A1:B70,2,false)
..

without having to copy and paste then edit it for every line.

fwiw the line where the vlookups are are in Q

so is there something like a universal placeholder that says
"take the # of this field and put an R before it"
so it gives out R3 when put in Q3 and R4 when in Q4 and so on..?

that way I would only have to paste "=vlookup(X,A1:B70,2,false)"
where X is the variable Im looking for..

does something like this exist?

also, even more basic question,
when I enter 1.89 into a field it display it as 01.01.89 how can I disable that?

it obviously thinks its a date..

thank you very much in advance for any help!

Quote

10-16-2010 , 12:22 PM

#224

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

Quote:

Originally Posted by subandi

I dont know if there is a loop function for regular excel, I'd only knew a solution for your problem using VBA.

You can just go to format (Format), cells (Zelle(n)), then change the format from Date to Number.

Quote

10-16-2010 , 03:36 PM

#225

Malfunction

old hand

Join Date: Jul 2008 Posts: 1,719

Quote:

I got it kinda working but still requires me to enter/change the 1st variable for each lookup,

Subandi you're kinda asking 2 questions.
First one requires the use of dollarsigns:
You use dollar-signs to tell Excel not to auto-change your range as you drag it down/right/left/up etc.

Put =A1 in B1 and drag it down gives =A2.
Put =A$1 in B1 and drag it down gives =A1
Put =$A1 in B1 and drag it right gives =A1

Depending where you put the dollarsign (in front of column or row) Excel won't change it. Obv you can use 2 dollarsigns to fix a position forever.
IF you ever drag a range to use in a formula, hit F4 to toggle the different options.

So you're solution would be:
=vlookup(R3,A$1:B$70,2,false)

and dragged down.

Quote:

So is there something like a universal placeholder that says
"take the # of this field and put an R before it"
so it gives out R3 when put in Q3 and R4 when in Q4 and so on..?

This is a different question!
=VLOOKUP(INDIRECT(ADDRESS(ROW(),18)),$A$1:$B$70,2, false)

ADDRESS gives a 'cell' according to row/column. So ROW() gives 4 when put in row 4 (say Q4!) and 18 gives 'r' as R is the 18th column.

INDIRECT tells Excel to use the result of ADDRESS(ROW();18), which is $R$4, as a range.
So actually, when put in row 4, your formula looks like this:
=vlookup(R4,$A$1:$B$70,2,false)

But when put in row 1598 your formula looks like this:
=vlookup(R1598,$A$1:$B$70,2,false)

.

Hope this helps!

Quote

Page 9 of 120

First

4 5 6 7 8 9 10 11 12 13 14 19 29 59 109

Last

Post Reply Subscribe

...

Page 9 of 120

First

4 5 6 7 8 9 10 11 12 13 14 19 29 59 109

Last