Free Microsoft Excel 2013 Quick Reference

- SMALL FUNCTION - How it works
- Improvements for text finding functions
- Question regarding how to search a column and print a row(s)
- Excel 2007 backwards compatibility - nesting functions
- Find first nonblank cell row # in pivot table using MATCH
- Worksheet function match - run time error
- Adjusting a Macro
- Dynamic Excel Formulas
- Re: "Saturday as a work day?
- Re: Extracting data without using database functions
- Preventing endless loops
- IF and ISERROR Functions
- UserDefined Functions
- Row parts and range confusion
- Microsoft Function: Need it reversing?
- Code to delete rows with no value/zero
- Auto adjust Indirect function
- Find first nonblank cell row # in pivot table using MATCH
- Function to pull cell information from column based on cell location
- Modifying macro to search for string instead of matching value.

I just can't figure out how to operate the function.

I found the Small function in this Formula:

=IF(ISERROR(SMALL(Data!$F:$F,ROWS($A$1:A1))),"", INDEX(Data!A:A,

MATCH(SMALL(Data!$F:$F,ROWS($A$1:A1)),Data!$F:$F,0 )))

-Max xl 97.-

---------------------------------------------------

--Extracted from the HELPER!--

SMALL

Returns the k-th smallest value in a data set. Use this function to return

values with a particular relative standing in a data set.

Syntax

SMALL(array,k)

Array is an array or range of numerical data for which you want to

determine the k-th smallest value.

K is the position (from the smallest) in the array or range of data to

return.

Remarks

If array is empty, SMALL returns the #NUM! error value.

If k â‰¤ 0 or if k exceeds the number of data points, SMALL returns the #NUM!

error value.

If n is the number of data points in array, SMALL(array,1) equals the

smallest value, and SMALL(array,n) equals the largest value.

Example

SMALL({3,4,5,2,3,4,5,6,4,7},4) equals 4

SMALL({1,4,8,3,7,12,54,8,23},2) equals 3

I am even more confused after this.

Thanks.

error if they didn't find the "find_text" string within the "within_text"

string. This forces the user to use long formulas such as

=IF(ISERROR(SEARCH(A1, A2)), 0, SEARCH(A1, A2)) in order to avoid errors.

Since in case of success, these functions return the serial number of the

first matching character, starting from 1, I suggest that these functions

return either 0 or -1 if they can't find that text.

2. I suggest to add functions that count the number of times that one text

exists within another, for example:

FINDNUM(find_text, within_text), SEARCHNUM(find_text, within_text)

FINDNUM is case-sensitive, SEARCHNUM is not.

Find_text is the text you want to find.

Within_text is the text containing the text you want to find.

Example: FINDNUM("ma", "Mamma mia!") returns 1, SEARCHNUM("ma", "Mamma

mia!") returns 2.

----------------

This post is a suggestion for Microsoft, and Microsoft responds to the

suggestions with the most votes. To vote for this suggestion, click the "I

Agree" button in the message pane. If you do not see the button, follow this

link to open the suggestion in the Microsoft Web-based Newsreader and then

click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Assume source data is in Sheet1, cols A to O, data from row2 down

Assume the key col is col B, with the criteria: >150

In an empty col to the right, say col Q

Put in Q2: =IF(B2="","",IF(AND(ISNUMBER(B2),B2>150),ROW(),"") )

Copy Q2 down to say Q100 to cover the max extent of data expected in the

source

(Leave Q1 empty)

Col Q is the criteria col which will simply assign arbitrary row numbers to

lines which satisfy the criteria. Col Q's returns will be read by the

formulas in Sheet2.

In a new Sheet2

-------

Copy > paste the same headers from Sheet1 into A1:O1

Put in A2:

=IF(ISERROR(SMALL(Sheet1!$Q:$Q,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA

LL(Sheet1!$Q:$Q,ROWS($A$1:A1)),Sheet1!$Q:$Q,0)))

Copy A2 across to O2, fill down to O100

(cover the same range as done in col Q in Sheet1)

Sheet2 will return only the rows from Sheet1 which satisfy the criteria, i.e

those rows with values in col B > 150. Result rows will be bunched neatly at

the top

---

If the criteria to be set in col Q is say:

where the text string "OK" appears in col B

Then we could put instead in Q2:

=IF(B2="","",IF(ISNUMBER(SEARCH("OK",B2)),ROW(),"" ))

and just copy down as before

(Change SEARCH to FIND if the case is important. FIND is case sensitive)

Sheet2 will then return the desired results ..

Adapt to suit ..

--

Rgds

Max

xl 97

---

GMT+8, 1° 22' N 103° 45' E

xdemechanik yahoocom

----

"Gary Braida" > wrote in message

news Hello,

>

> I have a spreadsheet consisting of 15 columns and 100 rows. I would like

to

> search a column for a specific string or based on some other criteria

(e.g.,

> > 150) and for columns meeting the desired criteria, I would like to

> the entire row (all columns) within the same worksheet or on a new

> worksheet. Can this be done using one or more formulas but without

getting

> into VB or fancy macros? Can this be done period? I'm looking for

> something that searches the column using the same or similar approach as

the

> "sumif" function but instead of summing the specificed range, I want to

> print the rows.

>

> Thank you in advance for your support. Please reply to Thank you very much!

>

> Regards,

> Gary Braida

>

>

at the office with designing a worksheet where difference between two

time values is calculated. The problem was that a user may enter

something that isn't recognized by Excel as a time - for example,

"10am".

I figured I could make these assumptions: the entry will be either

"10am", "10:30am", or something else that will be recognized by Excel

as a time and thus will not need to be interpreted by the formula.

So, here was my first attempt (these are array formulas, use Ctrl+Shift

+Enter):

=IF(ISNUMBER(E14),E14,TIMEVALUE(LEFT(E14,MATCH(1,

(MID(E14,ROW(INDIRECT("1:"&LEN(E14))),

1)":")*ISERROR(MID(E14,ROW(INDIRECT("1:"&LEN(E14 ))),1)*1),0)-1)&"

"&RIGHT(E14,2)))-IF(ISNUMBER(D14),D14,TIMEVALUE(LEFT(D14,MATCH(1,

(MID(D14,ROW(INDIRECT("1:"&LEN(D14))),

1)":")*ISERROR(MID(D14,ROW(INDIRECT("1:"&LEN(D14 ))),1)*1),0)-1)&"

"&RIGHT(D14,2)))

Where D14 is Time In, and E14 is Time Out. Of course, this creates 9

levels of function nesting, which won't work in earlier versions of

Excel.

Well, the workaround was to specify a larger array than would ever be

necessary:

=IF(ISNUMBER(E14),E14,TIMEVALUE(LEFT(E14,MATCH(1,( MID(E14,ROW($1:$100),

1)":")*ISERROR(MID(E14,ROW($1:$100),1)*1),0)-1)&" "&RIGHT(E14,2)))-

IF(ISNUMBER(D14),D14,TIMEVALUE(LEFT(D14,MATCH(1,(M ID(D14,ROW($1:$100),

1)":")*ISERROR(MID(D14,ROW($1:$100),1)*1),0)-1)&" "&RIGHT(D14,2)))

Just right! So, one more thing to look out for when working with the

new version.

anything except a blank cell inside a Pivot Table. I also tried OFFSET, but

the data I need to get to is to the left of the lookup column. Plus, I need

the row # for other functions.

=MATCH('anything but blank',CO869:CO933,0)

I have tried >"0", >0, NOT("0"), NOT(0), NOT(ISERROR(CO869:CO933), ISBLANK

with previous, NOT(""), NOT(" ").

The column I'm searching has various numbers and I need to find the data to

the left of any number (nonblank).

Please help?

If IsError(Application.WorksheetFunction.Match(chk_hd r1, hdr_rng2, 0)) Then

where chk_hdr1 is a variable and hdr_rng2 is a column range name.

It gives the run time error 1004,

Unable to get the match property of the worksheetfunction class.

This happens only when the value of chk_hdr1 is not found in hdr_rng2

--

Sajit

Abu Dhabi

that I pasted below. For some reason when I have Sub

CopyRangeFromMultiWorksheets() in my workbook Sub MakeQuestions() doesn't

work but as soon as I remove Sub CopyRangeFromMultiWorksheets() from my

workbook Sub MakeQuestions() starts working again. Sub

CopyRangeFromMultiWorksheets(), on the other hand, works regardless of

whether Sub MakeQuestions() is in the workbook or not.

When I have both Sub MakeQuestions() & Sub CopyRangeFromMultiWorksheets()

in my workbook and I try to run Sub MakeQuestions() I get a visual basic help

box with a message that reads Compile error: Argument not optional. Then the

LastRow = part of this part of the code LastRow = .Range("E" &

Rows.Count).End(xlUp).Row ....gets highlighted in blue.

Do you know how I might be able to fix this? Both macros are below.

Sub MakeQuestions()

Dim SortArray(Questions, 2)

With Sheets(StatSht)

LastRow = .Range("E" & Rows.Count).End(xlUp).Row

RowCount = LastRow + 1

End With

'Randomly choose 12 , 16, 24

Quest = Int(3 * Rnd())

Select Case Quest

Case 0: NumberofTests = 12

Case 1: NumberofTests = 16

Case 2: NumberofTests = 24

End Select

For TestNumber = 1 To NumberofTests

'create numbers questions

For I = 1 To Questions

SortArray(I, 1) = I

SortArray(I, 2) = Rnd()

Next I

Sheets(StatSht).Range("B" & RowCount) = Questions

'sort array to get random question

For I = 1 To Questions

For j = I To Questions

If SortArray(j, 2) < SortArray(I, 2) Then

Temp = SortArray(I, 1)

SortArray(I, 1) = SortArray(j, 1)

SortArray(j, 1) = Temp

Temp = SortArray(I, 2)

SortArray(I, 2) = SortArray(j, 2)

SortArray(j, 2) = Temp

End If

Next j

With Sheets(StatSht)

'Save numbers in worksheet

.Range("E" & RowCount).Offset(0, I - 1) = _

SortArray(I, 1)

End With

Next I

RowCount = RowCount + 1

Next TestNumber

MsgBox "Click Begin Sentence Completion"

End Sub

Sub CopyRangeFromMultiWorksheets()

Dim sh As Worksheet

Dim DestSh As Worksheet

Dim Last As Long

Dim CopyRng As Range

With Application

.ScreenUpdating = False

.EnableEvents = False

End With

'Delete the sheet "RDBMergeSheet" if it exist

Application.DisplayAlerts = False

On Error Resume Next

ActiveWorkbook.Worksheets("RDBMergeSheet").Delete

On Error GoTo 0

Application.DisplayAlerts = True

'Add a worksheet with the name "RDBMergeSheet"

Set DestSh = ActiveWorkbook.Worksheets.Add

DestSh.Name = "Summary Report"

'loop through all worksheets and copy the data to the DestSh

For Each sh In ActiveWorkbook.Worksheets

If IsError(Application.Match(sh.Name, _

Array(DestSh.Name, "Questions", "Status"), 0)) Then

'Find the last row with data on the DestSh

Last = LastRow(DestSh)

'Fill in the range that you want to copy

Set CopyRng = sh.Range("A1:B24")

'Test if there enough rows in the DestSh to copy all the data

If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then

MsgBox "There are not enough rows in the Destsh"

GoTo ExitTheSub

End If

'This example copies values/formats, if you only want to copy the

'values or want to copy everything look at the example below

this macro

CopyRng.Copy

With DestSh.Cells(Last + 1, "A")

.PasteSpecial xlPasteValues

.PasteSpecial xlPasteFormats

Application.CutCopyMode = False

End With

'Optional: This will copy the sheet name in the H column

DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value =

sh.Name

End If

Next

ExitTheSub:

Application.Goto DestSh.Cells(1)

'AutoFit the column width in the DestSh sheet

DestSh.Columns.AutoFit

With Application

.ScreenUpdating = True

.EnableEvents = True

End With

End Sub

Function LastRow(sh As Worksheet)

On Error Resume Next

LastRow = sh.Cells.Find(What:="*", _

After:=sh.Range("A1"), _

Lookat:=xlPart, _

LookIn:=xlFormulas, _

SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious, _

MatchCase:=False).Row

On Error GoTo 0

End Function

Function LastCol(sh As Worksheet)

On Error Resume Next

LastCol = sh.Cells.Find(What:="*", _

After:=sh.Range("A1"), _

Lookat:=xlPart, _

LookIn:=xlFormulas, _

SearchOrder:=xlByColumns, _

SearchDirection:=xlPrevious, _

MatchCase:=False).Column

On Error GoTo 0

End Function

m@discussions.microsoft.com> wrote:

>I am using the formula "=Workday" but I want this formula to include

>Saturdays. I have a Start Date and a duration and want to calculate the end

>date with Saturday included as a work day. Can anyone tell me how to do

>this? Any assistance would be greatly appreciated. Thank you.

Here is a UDF (User Defined Function) that will accomplish that.

To enter this function, <alt><F11> opens the Visual Basic Editor. Ensure your

project is highlighted in the Project Explorer window, then Insert/Module and

paste the code below into the window that opens.

To use the function, in some cell enter the formula:

=WDincSat(Start,NumDays,Holidays)

The variables can be cell references or entered directly. The Holidays

argument can be a range; but it is optional.

====================================

Function WDincSat(StartDate As Date, ByVal NumDays As Long, _

Optional Holidays As Range = Nothing) As Date

'Workday function without Analysis Toolpak

Dim i As Long

Dim TempDate As Date

Dim c As Range

Dim Stp As Integer

Stp = Sgn(NumDays)

TempDate = StartDate

For i = Stp To NumDays Step Stp

TempDate = TempDate + Stp

If Weekday(TempDate) = vbSunday Then _

TempDate = TempDate + Stp + (Stp < 0)

If Not Holidays Is Nothing Then

Do Until Not IsError(Application.Match(CDbl(TempDate), Holidays, 0)) = False

If IsError(Application.Match(CDbl(TempDate), Holidays, 0)) = False Then

TempDate = TempDate + Stp

If Weekday(TempDate) = vbSunday Then _

TempDate = TempDate + Stp + (Stp < 0)

End If

Loop

End If

Next i

WDincSat = TempDate

End Function

========================================

HTH,

--ron

1) I copied your data into Shee1 of a new workbook, beginning in cell B1.

(Note: I changed the date formats to US)

2) I built a pseudo criteria range, beginning in cell I1:

Col_I Col_J Col_K

--------- ------- ---------

StartDate EndDate COUNTRY

04/01/05 04/30/05 Syria

Yemen

3) Col_A is a helper column with the following formula entered in A2 and

copied down:

A2: =IF(OR(B2<$I$2,B2>$J$2,ISERROR(MATCH(C2,$K$2:$K$3,0))),0,MAX($A$1:A1)+1)

The data table looks like this:

SHEET1

Col_A Col_B Col_C Col_D

------- -------- ------- ----------

TEST DATE COUNTRY DATA

1 04/10/05 Syria xxxx

0 05/15/05 Kuwait xxxx

2 04/21/05 Yemen xxxx

0 05/12/05 Oman xxxx

3 04/04/05 Syria xxxx

4) On Sheet2, I copied the column headings from Sheet1

5) Down Col_A, I entered sequential numbers beginning with 1.

6) I entered the following formulas in cells B2, C2, and D2...and copied

them down:

B2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,2,0)

C2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,3,0)

D2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,4,0)

The table in Sheet2, only picks up valid items from Sheet1:

SHEET2

Col_A Col_B Col_C Col_D

------- -------- ------- ----------

TEST DATE COUNTRY DATA

1 04/10/05 Syria xxxx

2 04/21/05 Yemen xxxx

3 04/04/05 Syria xxxx

4 #N/A #N/A #N/A

5 #N/A #N/A #N/A

6 #N/A #N/A #N/A

Note: I left the #N/A items to demonstrate that there is no match for those

items.

Am I on the right track here?

Ron

--

Regards,

Ron

The below code manages to pick 5000 random items out of 10000 from Sheet1

columnA and display them on Sheet2. But the program locks if there are not

10000 data written in Sheet1 columnA.

Hope I need not have to enter that much data just to try it functions as it

should...

Help will be appreciated.

Regards

J_J

'----------------------------------------------

Sub Rast()

Dim RS As Long

Dim objRangeA As Range

Dim objRangeB As Range

Dim objRangeC As Range

Dim blnNotThere As Boolean

Set objRangeA = Worksheets(1).Range("A1:A10000")

Set objRangeB = Worksheets(2).Range("B1:B10000")

Set objRangeC = Worksheets(2).Range("C1:C10000")

'

If WorksheetFunction.CountA(objRangeA) < 10000 Then

MsgBox "Missing items from " & objRangeA.Parent.Name & " ", _

vbExclamation, " Maks10000"

GoTo DontCallMe

End If

Worksheets(2).Select

StartOver:

If WorksheetFunction.CountA(objRangeC) = 0 Then

objRangeC.Value = objRangeA.Value

objRangeC.Columns.AutoFit

objRangeB.ClearContents

objRangeB.ColumnWidth = objRangeC.ColumnWidth

'Range("A1").ClearContents

Range("A1").ColumnWidth = objRangeC.ColumnWidth

GoTo DontCallMe

End If

Do While blnNotThere = False

Randomize

RS = Int(Rnd * 10000 + 1)

If Not IsError(Application.Match(objRangeC(RS), objRangeC, 0)) Then

blnNotThere = True

Range("A1").Value = objRangeC(RS)

objRangeB(WorksheetFunction.CountA(objRangeB) + 1).Value =

objRangeC(RS)

objRangeC(Application.Match(objRangeC(RS), objRangeC, 0)).Delete

shift:=xlUp

End If

Loop

If WorksheetFunction.CountA(objRangeC) = 0 Then

If MsgBox("OK? ", vbQuestion + vbYesNo, _

" Randomly") = vbYes Then GoTo StartOver

End If

DontCallMe:

Set objRangeA = Nothing

Set objRangeB = Nothing

Set objRangeC = Nothing

End Sub

Private Sub CommandButton1_Click()

For z = 1 To 5001

Rast

Next z

End Sub

find any duplicates between the two columns. Column A has 469 rows and

Column C has 4713 rows. Example -- Column A -- Duck Donald and Column B --

Duck Donald

I found the formula below and it does work, but I need to know if I can use

a wildcard character in this formula to also find for instance: in Column A

-- Duck Donald and in Column B -- Duck Donald D. The difference being the

middle initial D. Any suggestions are greatly appreciated.

=IF(ISERROR(MATCH(A10,$C$1:$C$4713,0)),"",A10)

a cell formula is a userdefined function? That is, I know

I can load the Excel Functions into an array and then do a

match on the function found in the cell against the array

and check to see if it is in the array. If its not found

(iserror =true) its userdefined. But this means I have to

actually find funtions in formulas to do this? Is there

an easier way to spot userdefined functions in formulas?

Thanks

range of the contents of a cell - in a different column for the same row.

e.g. I want to sum the range contained in cell H36 - e.g. "query!$M$2$M13$'

and I have already found the matching row by locating the single range cell

B36 which is set via Set rng2 = rng1(resFinal)

I set a particular cell as a range(rng2 is set to the value of B36) and then

try to refer to the same row but another column in the totals line

Can someone explain to me how ranges work e.g. what is Cells(rng2.Row,

"H").Value and why the totals line below is falling over

- I did not write it but when I run it the locals window blanks.

Many thanks again

Jello.

Public Function regionTotal(countryRng As String) As Long

>

> Dim rng As Range

> Dim rng1 As Range

> Dim rng2 As Range

>

> Dim Total As Long

>

> Dim res1 As Variant

> Dim res2 As Variant

> Dim resFinal As Integer

>

> Set rng = Range("calc!B10") ' Apples

> Set rng1 = Range("calc!B33:B65") ' range to be searched

> res1 = Application.Match("*" & rng & "*", rng1, 0)

> res2 = Application.Match("*" & countryRng & "*", rng1, 0)

>

> resFinal = Application.WorksheetFunction.Max(res1, res2)

>

> If Not IsError(resFinal) Then

> Set rng2 = rng1(resFinal)

> Total = Application.WorksheetFunction.Sum(rng2.parent.Range( _

> rng2.parent.Cells(rng2.Row, "H").Value).Value)

> MsgBox Total & " for range: " & Cells(rng2.Row, "H").Value

> Else

> MsgBox Range("calc!B10").Value & " was not found"

>

> End If

>

> regionTotal = Total

>

> End Function

I have found a formula on the Microsoft support site that will compare two columns, and show the similar cells.

http://support.microsoft.com/kb/213367/en-us

I want to do the opposite e.g. show the unique cells.

Does anyone know how to do this?

dvent

Option Explicit Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Function LastCol(sh As Worksheet) On Error Resume Next LastCol = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 End Function Sub CopyRangeFromMultiWorksheets() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim CopyRng As Range With Application .ScreenUpdating = False .EnableEvents = False End With 'Delete the sheet "Timberline" if it exist Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets("Timberline").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Timberline" Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = "Timberline" 'loop through all worksheets and copy the data to the DestSh For Each sh In ActiveWorkbook.Worksheets If IsError(Application.Match(sh.Name, _ Array(DestSh.Name, "FP SUMMARY"), 0)) Then 'Find the last row with data on the DestSh Last = LastRow(DestSh) 'Fill in the range that you want to copy Set CopyRng = sh.Range("X1:Z600") 'Test if there enough rows in the DestSh to copy all the data If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then MsgBox "There are not enough rows in the Destsh" GoTo ExitTheSub End If 'This example copies values/formats, if you only want to copy the 'values or want to copy everything look at the example below this macro With CopyRng DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _ .Columns.Count).Value = .Value End With 'Optional: This will copy the sheet name in the H column DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value = sh.Name End If Next ExitTheSub: Application.Goto DestSh.Cells(1) 'AutoFit the column width in the DestSh sheet DestSh.Columns.AutoFit With Application .ScreenUpdating = True .EnableEvents = True End With End Sub

When I run the macro it populates a new sheet titled "Timberline" with all of the values in the X:Z columns. I would like to only pull those rows that have values of greater than 0, as it is set up to pull the first 500 or so rows from each tab. Some of these files get to be 30 sheets, so of the 15,000 rows that are populated, roughly 1/10th of them are really needed. It's not a big deal for myself to clean the page up, but I'm not the end user. Any help would be greatly appreciated. Thanks.

There are six types of different formulas in the cells

=IF(ISERROR(MATCH(INDIRECT("K"&3);$B$3:$B$35);"";"X") (formula for collumns L, N,P, R, T, V)

=IF(COUNTIF($B$3:$B$35;INDIRECT("K"&3))>1;"Dubblett";"") (formula for collumn M,O. Q, S, U, W)

both of these should always have the 3:35but should change collumn one step until collumn G is reached

=IF(COUNTIF($B$38:$B$68;INDIRECT("K"&3))>1;"Dubblett";"")

=IF(ISERROR(MATCH(INDIRECT("K"&3);$B$38:$B$68);"";"X")

same thing here only collumn should be changed

=IF(COUNTIF($B$56:$D$56;INDIRECT("K"&3))>1;"Dubblett";"")

=IF(ISERROR(MATCH(INDIRECT("K"&3);$B$56:$D$56);"";"X")

as wll as here

All formulas are supposed to go down to an INDIRECT("K"&110)

If the problem can be solved using another macro please describe here:

http://www.excelforum.com/excel-prog...ml#post2554689

So is it anyway to auto adjust when using the INDIRECT function?

anything except a blank cell inside a Pivot Table. I also tried OFFSET, but

the data I need to get to is to the left of the lookup column. Plus, I need

the row # for other functions.

=MATCH('anything but blank',CO869:CO933,0)

I have tried >"0", >0, NOT("0"), NOT(0), NOT(ISERROR(CO869:CO933), ISBLANK

with previous, NOT(""), NOT(" ").

The column I'm searching has various numbers and I need to find the data to

the left of any number (nonblank).

Please help?

Please bare with me, this is somewhat hard to explain.

I wish to grab a cell value from one sheet and bring it to another based on a previous cell from the same row on the original sheet compared to the column on the second sheet.

For instance

Sheet 1

A 1

B 2

C 3

Sheet 2

B Formula

C Formula

A Formula

Formula should make sheet 2 look like this

Desired Result

B 2

C 3

A 1

please also see attached excel sheet for example

I have gotten pretty close with

=IF(ISERROR(MATCH(A2,Sheet1!$A$2:$A$8,0)),"Missing",Sheet1!B2)

but of course that ends up pulling

B 1

C 2

A 3

which obviously is not what we are shooting for

I have a spreadsheet with 2 worksheets:

- Worksheet 1 is named Inventory

- Worksheet 2 is named Addresses

The Inventory worksheet contains 2 columns, ie:

- column 1 (named Hostname) is a list of names

- column 2 (named SN) is a list of serial number

The Addresses also contains 2 worksheet, ie:

- column 1 (named SN) is a list of serial number

- column 2 (named Address) is a list of addresses

I am using the macro below (which I kindly got from stanleydgromjr) to match the Serial Number from column 2 of the Inventory worksheet to column 1 of the Addresses worksheet, and then copy the associated address (from column 2 of the Addresses worksheet) to column 3 of the Inventory worksheet.

File Inventory - Addresses.xlm is an example.

Sub FillinAddress_column() Dim LR As Long, a As Long Application.ScreenUpdating = False Worksheets("Inventory").Activate LR = Cells(Rows.Count, 1).End(xlUp).Row ' Begin of column 3 update (i.e. Address column) For a = 2 To LR Step 1 With Cells(a, 3) .FormulaR1C1 = "=IF(ISNA(INDEX('Addresses'!C2,MATCH(RC2,'Addresses'!C1,0))),"""",INDEX('Addresses'!C2,MATCH(RC2,'Addresses'!C1,0)))" .Value = .Value End With Next a ' End of column 3 update (i.e. Address column) Application.ScreenUpdating = True End Sub

The macro works fine as long as the Serial Number being matched between both worksheets is exactly the same. However sometimes the Serial Number can slightly differ in the second worksheet, for example the Serial Number could be FOC09353DKS in the first worksheet while it could be listed as FOC09353DKS (123423) in the second worksheet. Hence I cannot retrieve the address for Serial Number FOC09353DKS

How could I modify the above macro to search for a string (i.e. FOC09353DKS in this case) instead of matching a value?

Note that actually it does not have to be done through a macro, I attempted to use something like =IF(ISERROR(VLOOKUP...) function without success.

Thanks,

Ant