Free Microsoft Excel 2013 Quick Reference

Hi

I have tried to find solution for my problem but so far no luck. My formula with defined names is this

=INDEX(zflexSTATdelDte,MATCH(KHL_AUK_ID,zflexAUKid,0))

If in array there are duplicates it always picks up the first line. Its great as thats what I need. But now I need exactly the same formula but now I would like it to pick up the last line from array. That is if there are duplicates.

Can someone advise me how to change the formula. I will give an example

---zflexAUKid------------zflexSTATdelDte

180081418310------------ 14/04/2011

180081418410------------ 11/04/2011

180083049910------------ 01/03/2011

180083049910------------ 01/03/2011

180083049910------------ 01/04/2011

180083066010------------ 29/03/2011

180083064710------------ 09/04/2011

So as you can see in array column there are x3 duplicates highlighted in red. Current formula picks up the date in green. The new formula that I'm after should pick up the date in blue.

Any ideas?

Kind regards,

Rain

So if in array zflexAUK there are some duplicates

I have tried to find solution for my problem but so far no luck. My formula with defined names is this

=INDEX(zflexSTATdelDte,MATCH(KHL_AUK_ID,zflexAUKid,0))

If in array there are duplicates it always picks up the first line. Its great as thats what I need. But now I need exactly the same formula but now I would like it to pick up the last line from array. That is if there are duplicates.

Can someone advise me how to change the formula. I will give an example

---zflexAUKid------------zflexSTATdelDte

180081418310------------ 14/04/2011

180081418410------------ 11/04/2011

180083049910------------ 01/03/2011

180083049910------------ 01/03/2011

180083049910------------ 01/04/2011

180083066010------------ 29/03/2011

180083064710------------ 09/04/2011

So as you can see in array column there are x3 duplicates highlighted in red. Current formula picks up the date in green. The new formula that I'm after should pick up the date in blue.

Any ideas?

Kind regards,

Rain

So if in array zflexAUK there are some duplicates

- How to look up the last cell/last but one in a dyamic column
- Picking up the last non-empty cell in a given range
- Picking up the last 10 entries which are spread out
- How to sum up the data which match two criteria?
- How to print all text in a cell? - It cuts off the last few lines.
- Pick up the word from right end
- How to sorting out the data by worksheet function
- Need formula to pick up cell in row to left that is not empty
- HOW TO PICK UP PRICE??
- Save or open file as YYYYMMDD in Macros, how to record it
- OFFSET function to pick up monthly groups of data
- How to look up value in pivot table using row and columns
- Picking the last non-zero value in a row
- OFFSET function to pick up monthly groups of data
- How to return only the last entry from a range which matches a criterion
- Numbered file names - Pick the last one
- How to Combine Data Regions
- Code to pick up the last modified file in a folder
- Formula to add up the number of 'T's input in cells (T's used as a code for training)
- Can you link (pick up) the font colour of a cell from one worksheet to another?
- How do I set up a way to add up the number of lines used when cal.
- How to pcik up the number that falls within a certain range?
- How to select automaticly the last cell not empty of a area?
- Picking up the XValues of a chart

I am trying to find a function to look up the last and last but one cells in a column. My array is constantly being updated with new rows of data but I want a function to find the latest value and compare it to the one before so I can work out the change. (The array updates via a web query every 10 mins)

I have this function that finds the last value in the column L

=LOOKUP(99^99,L:L)

Does anybody know how I would make it look up the last but one (preceeding) value?

Thnaks for any wisdom you can share!

Steve

my users. They are to enter value into the cell everyday continuously.

Can anyone help me with VBA codes for me to pick up the last entered value

(say A42, A43 onwards are still empty) on anyday, to be linked to another

workbook?

I used the SpecialCells(xlCellTypeLastCell) feature, it doesn't work. It

always return A100, possibly because A100 contains an IF statement.

Thanks.

{=IF(ROWS($E$3:$E3)>MATCH(REPT("Z",255),$A$3:$A$2901),"",INDEX($A$3:$A$2901,MATCH(REPT("Z",255),$A$3 :$A$2901)-MIN(10,MATCH(REPT("Z",255),$A$3:$A$2901))+ROWS($E$3:$E3)))} and it works great!

I've been trying to use this formula to pickup the last 10 entries which are spread out every 7 cells in columns B and C (see FORUM SAMPLE 6) and getting a return of #NA –

Is there a formula or how could I modify the one I'm using that would pick up the last 10 equally spread out every 7 cells?

e.g.

A Home BB Call 80

B Office Mobile 8370

C Home Mobile 870

D Home BB Call 860

E Office BB Call 850

G Home Mobile 480

H Office Mobile 380

I Home BB Call 10

Is it possible to sum up all match "Home" and "Mobile" by a formula?

870 + 480

Thanks a lot

cell will be cut off. Is there a way to extend the parameters of a cell to

not do this? Or - are there other solutions?

601 St Kilda Road Suncorp/ AAMI Level GD Melbourne

Inala Avenue Inala Plaza Shopping Centre Shop C7(B)&C8, Inala,

Coles Liquor Group Liquorland North Lakes 1 Macmillans Road North Lakes

I have to 2 worksheets. One of them is used for storing up the data

and the other worksheet is to display specified type of data in worksheet

one. I'd written the worksheet formula for it. But my problem is I have to

sort the data in worksheet two according to one of column. Since I use the

Index formula to pick up the necessary data, I can't use the sort function

to so. Anyone know how to implement sorting by worksheet functions? Please

help. Thanks

Best Rdgs

Ellis

I have a spreadsheet that has Jan-Dec as the column heading. Each month, I enter a number into the row under the month heading. I need a formula that will pick up the number in the current month cell, but ignore any that are empty.

For example, I may have a number of 50 under January, 55 for February, 45 for March, but April thru Dec. are still blank. I need the formula to pick up the number of 45 from March, then the next month, I would need the April number. (I cannot total the 12 months because the number I enter is already the year to date number).

After that I need to divide....

There is a "target" number for the year. I need to take the current month total, divide by the target for the year to get a result to show percentage of how close or far off the number is from the target.

I tried using an "if" statement but Excel only allows up to seven if statements, I need 12.

Help please

I have this huge data base which consists of a quote date, a contract date,a dn a price. How do I use a formula to pick up a price corresponding to a particular quote date and contract date?

Colomn A: Quote Date

Colomn B: Contract Date

Colomn C: Price

Thanks,

from a cell where date is mentioned as =today() with format as YYYYMMDD. or

any other alternative to pick up the file with naming convention as mentioned

above.

I have a series of dates in "Daily!" worksheet, cells C2:IV2, starting from

1-Jan-05 and running through till 11-Sep-05.

In cells A6:A62, I have various cost centres which can be allocated costs,

such as sales, rent, overheads, miscellaneous, etc.

So I therefore have a grid (C6:IV62) in which I can allocate costs against

the appropriate cost centre & date.

In "Weekly!" worksheet, I have the same list of Cost Centres again appearing

in cells A6:A62. However, instead of having daily dates in row 2, I have the

start of the week headings (i.e. in C2 is 02-Jan-05, in cell D2 is 09-Jan-05,

in cell E2 is 16-Jan-05).

In cell C6, I have the following formula:

=SUM(OFFSET(Daily!$D6,0,(COLUMN(C$2)-COLUMN($C$2))*7,1,7))

which I can copy across and down and will pick up the weekly totals for each

cost centre (note it starts at "Daily!$D6 as this is the first Sunday in

2005).

However, I also have a "Monthly!" worksheet, which is set up in exactly the

same way, and in which I want to put a formula in C6 which I can copy across

and down to pick up the monthly totals for each cost centre. However, I

can't think how to do it as the number of days in each month is not constant

(because the number of days per week is always 7, the Cols and [width]

parameters in the OFFSET function above were relatively easy to write).

Any help greatly appreciated,

Regards,

Gary T.

After reading a lot of info online I learned how to create a pivot table where the rows are my different categories (i.e. groceries, insurance, restaurants, etc), and my columns are year, then month (I used the MONTH() and YEAR() functions to extract that from the date I entered for each receipt). This pivot table allows me to look up how much I spent in each category for a given month.

Now here's the next step. There are a lot of things I want to be able to do with this setup, but the first is to add a column to my general budget page that has this month's expenditures in each category right next to the budget for each category. I figured out how to get today's year and month (by using MONTH(TODAY()) and YEAR(TODAY())) but I cannot figure out how to look up the values in my pivot table. I can use lookup() to find a specific category, but then I couldn't seem to nest lookup()s to find a value using category, year, and month.

Please share your wisdom with me! I'm attaching my spreadsheet as it is and all data is fake, so no personal info is given.

Any thoughts much appreciated!

I have a series of dates in "Daily!" worksheet, cells C2:IV2, starting from

1-Jan-05 and running through till 11-Sep-05.

In cells A6:A62, I have various cost centres which can be allocated costs,

such as sales, rent, overheads, miscellaneous, etc.

So I therefore have a grid (C6:IV62) in which I can allocate costs against

the appropriate cost centre & date.

In "Weekly!" worksheet, I have the same list of Cost Centres again appearing

in cells A6:A62. However, instead of having daily dates in row 2, I have the

start of the week headings (i.e. in C2 is 02-Jan-05, in cell D2 is 09-Jan-05,

in cell E2 is 16-Jan-05).

In cell C6, I have the following formula:

=SUM(OFFSET(Daily!$D6,0,(COLUMN(C$2)-COLUMN($C$2))*7,1,7))

which I can copy across and down and will pick up the weekly totals for each

cost centre (note it starts at "Daily!$D6 as this is the first Sunday in

2005).

However, I also have a "Monthly!" worksheet, which is set up in exactly the

same way, and in which I want to put a formula in C6 which I can copy across

and down to pick up the monthly totals for each cost centre. However, I

can't think how to do it as the number of days in each month is not constant

(because the number of days per week is always 7, the Cols and [width]

parameters in the OFFSET function above were relatively easy to write).

Any help greatly appreciated,

Regards,

Gary T.

I currently have a worksheet (Excel 2007) that keeps track of our receipt and use of inventory. Each item in our inventory has a “stock number,” and every time we receive a new shipment of the stock we assign it a “load tag” as well. We track each use (“pull”) and return of the stock. Sometimes, we make partial pulls and returns of the stock numerous times, meaning the load tag appears multiple times in the inventory spreadsheet. Other times, we pull and use it all at once. As such, the corresponding columns of the spreadsheet look like this:

LOAD TAG STOCK # QUANTITY

AH00015 43750062879 25,000

AH00015 43750062879 -25,000

AH00016 43750062879 27,600

AH00016 43750062879 -27,600

AH00016 43750062879 23,000

AH00016 43750062879 -23,000

AH00775 43750062879 27,600

AH00775 43750062879 -27,600

AH00775 43750062879 18,400

AH00775 43750062879 -18,400

AH01071 43750062879 27,600

What I am trying to do is create a separate worksheet where I can type in the stock number and get returned the latest of each load tag and its corresponding quantity (I can handle the formula for the quantity). In other words, I want to return only the last (i.e. latest/most current) entry for each load tag – not all instances of each load tag.

Would really appreciate any help you may have!

On a daily basis we have a report that is run from an SQL server, the file is called Adhoc Payments Applied.xls. the report is run each day and saved in a file where it adds the number of times it has been saved in the file.

Eg, if the report was first run 2 days ago then it will be called 'Adhoc Payments Applied.xls'

then run yesterday it will be called Adhoc Payments Applied_1.xls

I can locate the file but how do I get it to pick up the last one. I just can't work it out.

this is what I have so far. What it does is looks for one labelled number 1 first, if this does not exist then the file will not have a number label after it (I.e start of month) If it does exists i need it to see how far the numbers go up and select the last one.

Sub check() Dim fs As Object Dim strfile As String Set fs = CreateObject("Scripting.FileSystemObject") Filename = "Adhoc Payments Applied" & AdhocNum strfile = "G:MI Reports24th March 2010Finance" & Filename & ".xls" If fs.FileExists(strfile) Then MsgBox Filename Else MsgBox "NO FILE" End If End Sub Function AdhocNum() Dim fs As Object Dim strfile, dash As String AdhocNum = 1 strfile = "G:MI Reports24th March 2010FinanceAdhoc Payments Applied_" dash = "_" Set fs = CreateObject("Scripting.FileSystemObject") If fs.FileExists(strfile & 1 & ".xls") Then Else AdhocNum = "" End If End FunctionPlease can someone help?

Thanks

Rich

I have a worksheet which holds the results of two database queries. I want

to combine the regions programmatically saving the results to a new sheet. I

cannot create a single query to retrieve the combined data.

The initial worksheet looks similar to the following illustration:

Sheet 1:

Region 1:

Header1a Header1b Header1c ... Header1(n)

Row1a Row1b Row1c ... Row1(n)

Row1a Row1b Row1c ... Row1(n)

.... ... ... ... ...

.... ... ... ... ...

Row(N)a Row(N)b Row(N)c ... Row(N)(n)

Region 2:

Header2a Header2b Header2c ... Header2(n)

Row1a Row1b Row1c ... Row1(n)

Row1a Row1b Row1c ... Row1(n)

.... ... ... ... ...

.... ... ... ... ...

Row(N)a Row(N)b Row(N)c ... Row(N)(n)

The regions are separated by one or more blank lines. I'm going to remove

the sorting, grouping, and subtotals being applied to each region. Finally,

I want to grab the data combining it onto a new sheet. The columns and

formatting will alight between the regions. I'm not sure how to pick up the

data.

I was looking to save this file down from the email into a folder on a daily basis and create a macro which formats the report (its always comes in on the same format each day)

I was going to create a macro in a seperate spreadsheet which with the click of a button will open the last modified file from that folder.

I will then be able to run my macro to format the report.

Can anyone help?

Cheers

What I have is a spread sheet that doubles up as an annual/vacation leave and Training calendar.

If someone is on leave/vacation they simply enter a ‘1’ or if on training day they enter a ‘T’ into a cell for that date.

I have managed to use conditional formatting to colour code leave/vacation and training days and can add up the number ‘1’ signifying the total leave/vacation days but I need to know how to add up the total number of T’s entered to signifying the total number of training days taken

e.g.

in cell A1 input T

in cell B1 input T

in cell C1 input T

in cell D1 sum = 3 i.e. total number of T's in cells A1:C1

Any help would be greatly appreciated

Excel version 2003

Many thanks

Rob

sold. Along with that I want to add up the number of the objects that have

been sold, by just adding up the number sold in a column. Is there a way to

do this, beside creating another column and putting a qty in it.

If i have a column of numbers, can i pick up the number that falls within a certain range? For example, i want the number that is between 10000 and 20000, is that any formula or function i can use to pick up that number? Appreciate if somebody can help me. thxs.

I don't find how to find automaticly my last data (non blank cell) inside a

table

thanks

table

thanks