Free Microsoft Excel 2013 Quick Reference

- Camera Function or Macro?
- Finding text strings in complex situations
- Label problem
- Insert rows with Formulas
- Returning all values from a lookup - not just the first/last one
- Compare cells, copy, loop
- Find date duplicates Col 2 or Col 3 then combine Col 1 text
- Numbers to Letters?
- V lookup with 2 criteria to return results for multiple columns
- Formula OR text
- Pivot Tables: get pivot data formula problem
- User Search Form To Find Matching Values Then Populate Field Based On Click.
- What Excel functions changed your life/career?
- Please Need an Example!
- Why does my formula in Excell return a #N/A?
- Re: Large formulas
- Adding If Statement Problem
- Remove double quote marks when pasting into notepad
- Sumproduct-Has anyone ever used these formulas together
- Shrinking Size of Excel File

2.Have a store listing of 500 units

3.I am printing call record forms for use by reps that make calls on the

units.

4.The actual worksheets fill in info on this call record form, triggered by

a store number (lookups data from#2 above, e.g. address, phone#, etc), this

becomes a single page, with space for comments etc, without having to write

in all the contact data. Simply enter the store # and the form is printed

out and ready for use.

5. This call record form is 12 columns wide to contain all the info and to

fit on 8.5x11 sheet of paper.

The question: The new county data I want to include on this sheet is 15-20

columns of data; which is too wide to fit on the call record. Is it possible

to use the camera function to "snap" say columns 25-45 and paste it to the

call sheet to fit in a specified area? (This data would be static and used

only for reference, no math performed.)

Can VBA do this? Obviously, I want to automate this, so that when a new

call record form is printed, this new county data would be on the sheet.

This would be similar to using Vlookup and Concatenate to pull the coulmns

of data...I'm looking for an easier way. Could these Coulmns be Named, and

the use Vlookup to pull the named range as "one piece" so as to fit in the

space allowed?

Appreciate any thoughts.

I need to find all the text strings, which matches to certain criteria.

Briefly, I have a criterium "test". Then, I'm interested in the rows which

have "test" string in a column B. I'm looking for the text strings in the

column C, which correspond the rows according to "test" criterium. I have to

eliminate all the duplicate and blank values and finally concatenate rest of

the values.

VLOOKUP just returns the first match, which can even be blank. In theory, I

could use the OFFSET and MATCH -functions for all the matches, changing the

reference to correspond the next row from the previous match and continue.

However, there are so many matches that it would be impossible to link so

many loops.

DGET would be an ideal function, only if it could handle the multiple

matches. If there was an IS-function to #NUM!- and #VALUE errors

particularly, I could determine, if there are matches for the criteria.

Array functions don't seem to solve this problem either, because the rows

which corresponds the criterium are in disorder, so I can't get an array,

which would correspond the criterium.

dashes, so I can convert the labels to the same format as my source file in a

VLOOKUP. I'm using the following formula:

=CONCATENATE("'",left(a1,1),mid(a1,3,5),mid(a1,10, 3),right(a1,1), which takes

0-16830-400-7, and converts it to '0168304007. The problem is, the VLOOKUP

doesn't work until I value the formula, then hit F2 and F9 to convert it to a

label. The problem is the ' symbol, which shows in the cell until I hit F2 &

F9. If I don't put the ' symbol into the CONCATENATE formula, CONCATENATE

converts the 0168304007 to a value and removes the leading zero. My source

file is a label that contains a leading zero.

Is there a better way to do this conversion? If not, is there a way to

change the result to a label without manually hitting F2 & F9. I'm

converting a few thousand labels, so doing this manually would be a nightmare.

I have a spreadsheet that starting at row 18 contains the following

information

A18=M$2

B18= Datavalidation indirect ($M$2)

C18=IF(B18="No More

Options",VLOOKUP($B18,INDIRECT(CONCATENATE($M$2,"_ info")),1,FALSE),IF(B19

list would look concatenated with a semi-colon - as in

value1;value2;value3. Not sure how to do this...

Everything I'm reading is returning single values (as a vlookup does).

But my data set has more than 1 value that meets the criteria and I'd

like to return them all (preferrably in a single cell).

Any help would be welcomed.

Thank you.

--

Jim Burns

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

Jim Burns's Profile: http://www.excelforum.com/member.php...o&userid=24389

View this thread: http://www.excelforum.com/showthread...hreadid=379834

If there is a way to do this with Functions, i would rather that because

this is a report that is run over night when i'm not here. If not, i guess

we can find a way.

I'd like to look down column A and look at every single item.

If there is no duplicate, do nothing

If there are duplicates, it should first find where A and B = each other for

that particular #. Then take all the cooresponding #'s in B and concatenate

them in Column C (each seperated by a colon) on the row where A and B were =.

see below for a visual.

examples

A1 = 364691-001 B1 = 364691-001 C1 = 364692-001:364695-001

A2 = 364691-001 B2 = 364692-001 C2 = (empty cell)

A3 = 364691-001 B3 = 364695-001 C3 = (empty cell)

A4 = A3509A B4 = A3509A C4 = (empty cell)

A1,A2,A3 are duplicates

A1 and B1 are equal to eachother

Take B2 and B3 and concatenate in C1.

C2 and C3 can stay empty

A4 and B4 = eachother, but there are no duplicates so C4 stays empty

I"m not sure if indexing, Vlookup, if, then, next and loop will be needed or

not.

I'm open to any suggestions.

I have over 400 rows of data where Column 1 is text (project titles) and

Column 2 is a start date and Column 3 is an end date. In each of the date

columns, there are duplications.

I want to get these start and end dates into another spreadsheet which is

formatted like a calendar. However, I gather VLOOKUP on its own won't work

with the duplications, and when the dates in the first spreadsheet aren't in

chronological order.

There may be other ways of solving this, but my immediate thoughts were to

insert a new sheet search for all duplications in Column 2 and then

concatenate the corresponding text in column 1. I then need to find some way

of getting the concatenated text into the right day in the calendar.

Any suggestsions gratefully received.

=CONCATENATE("A",MATCH(B2,A:A,0)+1)

This will return something like A6 when im searching for yesterdays date and

I want to know the cell reference where today's date should go. Although,

thats a simple example of the macro's I use that puts information in certain

cells depending on what stuff is in other place.

My Question is, that this is fine for me, but when it comes to a Horizontal

match instead of a vertical one the answer is still a number. This leaves me

having to dedicate over 200 lines to do a vlookup on this number, so 3 = C

and 28 = AB.

Is there anyway to automatically determine from a number which column it is?

So I can use it in concatenated matches. What im currently working on looks

as 1:1 for "Total" then minus's 1, so each time the total is moved right for

a new column my information will go in this column.

Cheers for your time!

generate shorter reports for items currently in use. My column headings are

as below:

A Product

B Color

C Volume Used

D Manufacturer

E Catalog Number

F Lot number

G Date received

H Expiration Date

However, we may have a given product in several different colors.

All 8 columns need to show up in my report.

I would like to be able to use VLOOKUP (or something else that works) to

find (for example) Product X in green and then be able to drag the formula

to return the rest of the 7 columns as well. If I concatenate first, I can't

drag. If I just link the cells then the report gets jumbled up every time

somebody sorts the inventory sheet. If I have to type in a separate vlookup

formula in each column, I'll still be typing this time next year. I know

this really should be done in Access, but unfortunately, that's not an option.

Any help would be greatly appreciated.

Thanks,

JenL

book/sheet act as text? ie will produce "Joe Bloggs" if A3="Joe" and

A2="Bloggs" but in another worksheet will remain "=CONCATENATE(A3," ",A2)"

2. How do I lookup a column of text which are a result of formula? eg.

=VLOOKUP(A2,NAMES,2) where A2="Joe Bloggs" and NAMES=Array of results of

formula "=CONCATENATE(A3," ",A2)". Currently produces "N/A" even though I

KNOW that Names contains "Joe Bloggs".

Any help with this would be greatly appreciated as I only have one or two

handfulls of hair left!!

--

Bob JONES

The name that changes depending on the row in the getpivot formula is obtained from a vlookup, which the data in the pivot table is also based on. So if they're the same, it should be returning the figures, not zero...

I thought it must be a formatting issue, but I've copied and pasted so that I'm sure the fields are the same...

Any suggestions on what I could do??? Please let me know if you need more info

Many thanks!!!

PS Using excel microsoft office 97 pro

Here's an example of my formula, if this helps

=IF(ISERROR(GETPIVOTDATA('Prem PT'!$C$5,CONCATENATE(Test!C6," ",D6," ",Test!$A$6," "))),0,GETPIVOTDATA('Prem PT'!$C$5,CONCATENATE(Test!C6," ",D6," ",Test!$A$6," ")))+IF(ISERROR(GETPIVOTDATA('Prem PT'!$C$5,CONCATENATE(Test!C6," ",D6," ",Test!$B$6," "))),0,GETPIVOTDATA('Prem PT'!$C$5,CONCATENATE(Test!C6," ",D6," ",Test!$B$6," ")))

I haven't found online examples quite like what I'm trying to do, but I think it's do-able and quite easy?

I have a worksheet where each row is an individual. To the right of this I want some users to populate the employee numbers of who they feel could replace them if that individual left the company.

Because we want these replacements as employee numbers (so that we can populate additional sheets/do vlookups), and the number of possible candidates is quite large (1-2 thousand), I want to provide a search form they would use to find each replacement. This will allow more accurate data entry while still keeping the document in excel format, which they are familiar with.

So.. What the data looks like (as per first attachment example):

Sheet 1:

User,~extra data~,Replacement 1,Replacement 2, Replacement 3 etc....

Sheet 2:

Employee ID, First and last name, Last Name, First name, ~extra data~

Note that the last/first names on sheet two will be calculated (I'll use a formula to extra the First/Last names from the concatenated name in the 2nd cell). I'm doing this as I believe to allow searching on first/last name separately they should be in different columns.

Functionality desired:

Triggered by either clicking the "replacement" cells, or selecting the cell and starting the macro (ctrl function perhaps?) I'd like a search form to come up.

Search form would show 2 boxes:

-First Name

-Last Name

and a search button.

Entering either or both lists any matches (using wildcard search) and their matching employee number. If the user doubleclicks any of the results, that user's employee number is pasted into the originally highlighted "Replacement" cell.

Workflow Summary:

-User clicks one of the "replacement" cells on the first worksheet and starts the macro. If clicking the cell it'self would trigger the macro/form (or perhaps hitting a combination of keys) that would be ideal.

-Search form appears.. User enters first and or last name and clicks search.. matching results (based on first/last name from 2nd worksheet appear) **Note, would be desirable to show results as their first/lastname plus their employee number.

-User clicks the correct user (if there are multiple results).

-That employee's employee number gets populated to the cell they selected before starting the macro.

I've attached a simple workbook showing what the data looks like:

Closest I found to this was here:

http://www.ozgrid.com/forum/showthre...t=27799&page=2

But that one returns the sheet the matching user(s) is on, then brings you to that sheet/row if you click it.

I have been an observer of this forum but recently joined (because I needed some help!).

And, I was just able to offer my first piece of advice back to the community. Woo Hoo!

The evidence

I have a long way to go in my Excel journey I was just wondering what functions changed people's life in terms of career and outlook on how to use Excel.

IF

Where would I be without If?

Such a great function that allows multiple options in one cell.

Changes the way I processed information

VLOOKUP

I was transferring data manually in one job and my manager at the time and asked how I was doing.

I was saying it was going finr. Although, I was kind of new to the job and was struggling to see how I could do this all in a data especially without mistakes.

He came round and asked how I was doing it. He asked if I knew what the VLOOKUP function was.

I said no. He sat down with me and I was literally shocked, surprised and amazed.

When I show other people who have a similar problem the look of wonder that appears on their face is child-like!

Deffo one of the best functions I think that shows people why Excel can be so powerful.

SUBSTITUTE/TRIM/CONCATENATE/LEFT/RIGHT

I am almost always using the above functions to cleanse my datasets

Often getting them ready to be vlookupped!

INDIRECT

This is such a neat little feature. I must have gone a solid year without ever having come across it.

Sometimes you may have ten different ranges e.g. Admissions to different hospitals over a year separated by months.

You have a formula you want to run and then you want to give the user option to see the different values dependent on what they choose in filter.

But, your formulas are dependent on a range. How do you change the range dyamically?

I'm doing a rubbish job at explaining it but follow this video here http://www.youtube.com/watch?v=L6Ow1wLfw2Q

Anyway, just thought I would ask: What Excel functions changed your life?

I am particularly interested what got you to use VBA as I have tried to learn it but I usually give up and decide let me go with a formula instead.

how to do a VLookup for a drop down box. Thank you.

some return #N/A as a result. Any suggestions? Here is my formula:

=IF(ISERROR(VLOOKUP((CONCATENATE($C42,"INSQUOTE-AUTO")),Pronto2!$1:$2500,8,FALSE)),"",VLOOKUP((CONCATENATE($C42,"INSQUOTE-AUTO")),Pronto2!$1:$1940,8,FALSE))

production

and in this case there are 20 items. An example would be if item 301 were

completed, it would return a production value of 1.5. Here is the basic idea

=IF(B3=301,1.25,IF(B3=302,1.5,IF(B3=303,1.5,IF(B3=304,1.75,IF(B3=305,1.25,IF(B3=306,1.5,IF(B3=307,0. 75,IF(B3=308,0.75,))))))))

"Peo Sjoblom" wrote:

> It's possible to use IF function 20 times given that you concatenate each

> part, however it is hard to audit and if you indeed need many conditions you

> might be better off using something else like a lookup table or index,

> here's an example of how to bypass the 7 nested limits

>

> =IF( A1=1,"a","")&IF(A1=3,"c","")&IF(A1=5,"e","")&and so on

>

> however you might as well use a vlookup formula

>

> =VLOOKUP(A1,{1,"a";3,"c";5,"e"},2,0)

>

>

> --

> Regards,

>

> Peo Sjoblom

>

> (No private emails please)

>

>

> "JimB" <JimB@discussions.microsoft.com> wrote in message

> news:938978BB-AD3C-4517-9880-EF8ADA233ED8@microsoft.com...

> > Do you know of any of creating formulas using the "if" function more than

> > seven times. It is normally limited to seven, I need as many as 20

>

>

=IF(ISERROR(VLOOKUP(CONCATENATE($A8,AW$3),'Inbound Baseline 2010'!$B:$V,21,FALSE)),"",VLOOKUP(CONCATENATE($A8,AW$3),'Inbound Baseline 2010'!$B:$V,21,FALSE))

That formula works fine.

However, when I add in an additional IF statement (to see whether a specific criteria is met) in order to reduce the processing time, I don't get the correct value, and end up with 0.

=IF(AA8="Y",IF(ISERROR(VLOOKUP(CONCATENATE($A8,AW$3),'Inbound Baseline 2010'!$B:$V,21,FALSE)),"",VLOOKUP(CONCATENATE($A8,AW$3),'Inbound Baseline 2010'!$B:$V,21,FALSE)),0)

Any ideas of what is causing this to happen?

Any help would be greatly appreciated! Thanks!

When I try to paste the finished result into notepad/textpad/word it adds extra double quote marks throughout the text. Does anyone know how to paste it without these marks?

Thanks!

New forum member here. I'm trying to create a formua that will reference two variables (so both variables must hold true for this to work) and sum the dollar values for all true instances. The formula will reference a set of data that is imported into a file monthly. (This is obviously a budget file that I'm working on.)

I think the formula will require an if-then statement, vlookup and sumif, but so far I haven't had much luck pulling all three of these functions successfully into one formula that will work. I'm also using the concatenate function to blend the two variables together. So this is fairly complicated. My fear is that it's beyond Excel's capability.

Has anyone ever used these formulas together successfully for this purpose? Please let me know if any of this is unclear. I've attached the file and highlighted in yellow the cells that I need looked at.

I appreciate your help and suggestions.

Thank you.

Dan

I am looking for help to reduce the size of the file which will help me to be able to access it more frequently since it takes over and freezes all other excel files and makes the entire computer run slowly.

My spead sheet is approx 330,000 MB. Not sure if I can even send that large of a file through the internet so I have not attached a link or the file on this page.

I have created an allocation system. In short, I am tying in open component purchase orders, with current inventory and running them against our production schedule to determine the date the components will be available by sales order.

The major constraints are that we have multiple sales orders that can produce using the same component on multiple lines on the same day.

To be short, the main table which uses the most memory contains the following formulas:

INDEX

IF

VLOOKUP

OR

ISNA

CONCATENATE

SUM

I use the same formulas for each part number. There are 260 parts. The primary fomula totals all the previous usages for the same component and compares it to the daily inventory.

Which is 159 columns x 152 rows per part.