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

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then
        For Each ce In Target
            MsgBox ce.Address
        Next
    End If
End Sub

Thanks, that makes it easy. All this time I never realized that function was passed more than one cell.

Quote

02-13-2012 , 10:11 PM

#652

alpinestyle

centurion

Join Date: Jun 2005 Posts: 180

I have created a folder on my desktop that contains .pdfs for a number of widgets.
I have linked cells in a 2010 excel sheet to these pdfs.

For example:
Within the excel sheet a cell is named "purple widget", I hyperlink this to the pdf in the folder for "purple widget".

Is there a way to "bind" these files together (excel sheet + folder containing .pdfs) so that they always travel together.

So that I can email someone the excel sheet (with all the linked files embedded).

Thanks,
Alp

Quote

02-13-2012 , 11:53 PM

#653

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Have you tried going Insert > Object > Create New > Adobe Acrobat File. I think that embeds it as long as you uncheck link to file

Quote

02-14-2012 , 01:22 AM

#654

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by CrazyEyez

zomg,

When working with sheet names, instead of defining sheet variables I use the "code name" that you can see in the VBA Project Explorer window. So rather than

Worksheets("Sheet2").Range....

I use

Sheet2.Range....

Which works regardless of what the sheet is named. (I learned this early on, I think from a jwalk book.) But I pretty much never see this technique in other code I encounter. Is there a drawback to it that I'm not aware of? I've been doing it for years and I can't recall coming across a problem.

Missed this at the bottom of the last page. Off the top of my head times where this would not work would include:
- Worksheets with a space or special chars in it
- Worksheets with reserved names used for other functions
- Referencing sheets with variables
- Referencing sheet names using values from cells

There are probably more scenarios i can't think of but tbh that's enough reasons for me not to use it because i've got OCD with using the same referencing style through my code for everything and switching it up several times in a function would irritate me

Quote

02-14-2012 , 03:21 AM

#655

d10

Carpal \'Tunnel

Join Date: Jan 2005 Posts: 7,513

I think he was referring to the sheet's object name, not the name on the sheet tab. I didn't know referencing like that was possible before that post so I've got no opinion on it.

Quote

02-14-2012 , 09:13 AM

#656

CrazyEyez

Carpal \'Tunnel

Join Date: Sep 2003 Posts: 8,669

d10 is correct.

Quote

02-14-2012 , 10:33 AM

#657

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

What if you change the language in Excel, it probably wont work.
This is probably not relevant to a lot of people in here, but does it translate it?

Quote

02-14-2012 , 11:04 AM

#658

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Ohhh right, that's even worse. I wouldn't use it just because its confusing. Most of my sheet names are long because in applications where more than one person is looking at the code i'd rather have sheet names that mean something

Quote

02-15-2012 , 01:05 PM

#659

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

Got another question:

I got 2 sheets (A and B).
A is an overview, B is a product specific sheet. C, D, E and F are also product specific sheets like B and A gives an overview over all of them.

Usually A references B (with sumifs, etc.), but for one row, B references A.
I use match and indirect. It works fine with one exception.
When B and C are identical products, but different colors, C references the values for B instead of C, because I use the product name as a reference for match.

Let's say that column 1 is the product name and column 2 is the color. Is it possible to tell match to use both the product name and the color for reference without making it a dynamic array?

An example screenshot here:

Quote

02-15-2012 , 01:39 PM

#660

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

When you say dynamic array you mean an array formula?

The only way not to use one would be to create a third column overview (say column C) with =A1&B1 then in cards blue B1 you can use

Code:

=vlookup("CardsBlue", overview!C1:D2,2,0)

edit: you could also use

Code:

=SUMPRODUCT((overview!A1:A10="Cards")*(overview!B1:B10="Blue")*(overview!C1:C10))

Last edited by zomg; 02-15-2012 at 01:45 PM.

Quote

02-15-2012 , 02:16 PM

#661

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

Sumproduct might actually work. I will test it tomorrow, thanks man.

Quote

02-16-2012 , 01:09 AM

#662

d10

Carpal \'Tunnel

Join Date: Jan 2005 Posts: 7,513

Is there an easy way to write a formula that can find the row number of the last instance of cells with 3 consecutive "x"s? For example, with this data:

Code:

the formula should return 7. You can assume that column A will only ever contain an "x" or a blank cell.

Quote

02-16-2012 , 01:23 AM

#663

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Are user defined functions an option or you want formula only?

Quote

02-16-2012 , 01:34 AM

#664

pokerplaya5

journeyman

Join Date: Oct 2007 Posts: 346

What do you think is the best online excel tutorial from A-Z?

I'm decent with excel but i'm sure there are a lot of simple things that I have skipped over learning and a lot of advanced things I have no idea how to use or heard of.

Thanks!

Quote

02-16-2012 , 01:36 AM

#665

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

d10, are you prepared to use a helper column?

if so in cell B2 put

Code:

=IF(A1="x",B1+1,0)

in cell C1 put

Code:

=ROW(INDIRECT("B" & MAX(IF(B1:B100>=3,ROW(B1:B100)))))-1

needs to be confirmed with CTRL+SHIFT+ENTER as it's an array formula.

i think this MAY be possible in one formula without a helper column but ill have to look again tomorrow

Quote

02-16-2012 , 02:02 AM

#666

d10

Carpal \'Tunnel

Join Date: Jan 2005 Posts: 7,513

User defined function would be fine.

I figured out one way after I posted without using a helper column using

Code:

{=MAX(IF(A1:A25="x",IF(OFFSET(A1:A25,1,0)="x",IF(OFFSET(A1:A25,2,0)="x",ROW(A1:A25),0),0),0))}

which gives the start of the consecutive rows, but that's fine too. Only problem is I'll be using this formula in 90 cells and although it works on a test of 25 cells I'll need it to work on more like 3000-4000 cells, so it's not nearly efficient enough.

Also a helper column would be fine if it's the best way, but I'd need to spend maybe 30-45 minutes redesigning some things to fit it in.

Quote

02-16-2012 , 05:09 AM

#667

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

Quote:

Originally Posted by Spurious

Sumproduct might actually work. I will test it tomorrow, thanks man.

Ok, I got one problem with this.
The product and the color are in row 1, but the value is in row 2.
It works when I move the value range one row below. Is it possible to do it otherwise? Because ideally, I just want to give the columns (I never know the full range).

Quote

02-16-2012 , 06:46 AM

#668

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

I just tried sumproduct and it takes way too long, I got thousands of those formulas in some sheet and it would significantly prolong the process.

Quote

02-16-2012 , 10:43 AM

#669

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Spurious + d10,

If you are worried about speed and adding a helper column isn't an issue it will always be faster especially if it completely removes the need for an array formula.

Spurious,

You Should be able to use to use INDEX(tableRange, MATCH("CardsBlue",helperColumn,0)+1,2)

the 2 at the end will need to change depending on which column in the tableRange the value is in and which column is the helper column.

d10,

Your solution is pretty clever but not really elegant/scalable if performance is an issue any one cell formula is going to be pretty intensive but it shouldn't be that big of a deal as you are only running it once and not copy pasting for a thousand cells

Quote

02-16-2012 , 10:53 AM

#670

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

d10,

if you want to try a UDF you can use

Code:

Public Function countX(amt As Integer) As Long
    For i = Range("A65000").End(xlUp).Row To 1 Step -1
        cnt = 0
        If i - amt < 1 Then
            countX = 0
            Exit Function
        End If
        If Range("A" & i) = "x" Then
            For x = 0 To amt - 1
                If Range("A" & i - x) = "x" Then cnt = cnt + 1
            Next x
            If cnt >= amt Then
                countX = i - amt + 1
                Exit Function
            End If
        End If
    Next i
End Function

run by putting =countX(3) on the sheet

Quote

02-16-2012 , 11:17 AM

#671

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

Quote:

Originally Posted by zomg

I did it that way, although I am not a big fan of helper columns.

Quote

02-16-2012 , 11:33 AM

#672

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Neither but if i had to rank it i would probably say

array formula > helper column > UDF

if speed is the only factor than a helper column is usually the way to go

Quote

02-16-2012 , 01:07 PM

#673

noname6520

veteran

Join Date: Feb 2008 Posts: 3,414

Thanks for previous answers they have been really helpful.

I have another question. Say I have a function that is expecting an array to be passed to it.
The array I would like to pass to it has to be constructed from all columns except a column i and all rows except for the first row. Column i can be anywhere in the array.

How would I go about doing that? thx alot

Quote

02-16-2012 , 01:10 PM

#674

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Not sure exactly what you mean. Literally column I? then you could do something like this which will sum the range except for column I and row 1

Code:

Public Function arrayTest(cRng As Range) As Long
    For Each ce In cRng
        If ce.Column <> 9 and ce.Row <> 1 Then
            cSum = cSum + ce.Value
        End If
    Next ce
    arrayTest = cSum
End Function

Quote

02-16-2012 , 01:16 PM

#675

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

He said column I could be anywhere, so probably defining it in the header would be the quickest and easiest solution.
The difference would be instead of 9, you would have <>notClm (or w/e you define it as).

Quote

Page 27 of 120

First

7 17 22 23 24 25 26 27 28 29 30 31 32 37 47 77

Last

Post Reply Subscribe

...

Page 27 of 120

First

7 17 22 23 24 25 26 27 28 29 30 31 32 37 47 77

Last