I know how to protect cells whereby other users are not able to change the cell, but someone sent me a worksheet where the only cells I can click on are the one I can edit. But I don't know how to do this. Does anyone know how to lock cells where you can't even click on it. Thanks.

]]>Dear all

I need help as per attached file

Can i create a detailed table to show me percentages of how many males and females did the course based on their levels? Thanks a lot

I need help as per attached file

Can i create a detailed table to show me percentages of how many males and females did the course based on their levels? Thanks a lot

Hi all,

I am looking for a formula which will:

- Find a value in a column.

- Then return the value in the adjacent cell to the left.

I've tried using the LOOKUP function for this. In the attachment, the formula would go in cell C4.

I'd like the formula to search E4:E13 and identify the value which matches the value in B4.

Then I'd like the value in D4 to appear in C4.

So in the attached example, inputting '2' in B4 would result in '99' in C4.

Ideas welcome, thanks.

I am looking for a formula which will:

- Find a value in a column.

- Then return the value in the adjacent cell to the left.

I've tried using the LOOKUP function for this. In the attachment, the formula would go in cell C4.

I'd like the formula to search E4:E13 and identify the value which matches the value in B4.

Then I'd like the value in D4 to appear in C4.

So in the attached example, inputting '2' in B4 would result in '99' in C4.

Ideas welcome, thanks.

Hey guys,

First of all, let me say thankyou for having such a large database of past q&a's, it has been very helpful to me in the past when googling how to do something specific. Alas, google has now failed me, so I turn to you.

A-Desired Quantity

B-Supplier 1 Price

C-Supplier 1 Qty

D-Supplier 2 Price

E-Supplier 2 Qty

So in row F, I want to find the Min value of B and D and LIMIT it to the quantity associated with it. So if Supplier 1 has the lowest price but 10 units, then I want F to represent 10 (if A > 10). I can (easily) find the minimum price, but I can't figure out how to restrict the quantity based on the corresponding maximum value in C and E and my own desired quantity in A. And then of course I'd like to have the second supplier fill my remaining quantity into G or something, but that's another task.

I've tried inserting a custom data validation setting restricting the limit into F based on C but if it went over I just got an error message because it was too high a result (the result didn't cap itself). I think I can get around it by doing something like =MIN(IF(B2<D2, MIN(A2,C2))) which would give me a result of either the lowest number of units I want to order from supplier A if he was the lowest based supplier but then I'd have to do repeating formulas for if Supplier 2 had the lowest price etc and more formulas for second-cheapest supplier (ie, if supplier 1 has the lowest price but a lower quantity then I want to order, I have to order from 2 suppliers) and it grows exponentially (2 suppliers=4 formulas, 3 suppliers=9)... I think that makes sense? If it doesn't just ignore me, I'm slightly delirious from trying to work this one out. Thanks

]]>First of all, let me say thankyou for having such a large database of past q&a's, it has been very helpful to me in the past when googling how to do something specific. Alas, google has now failed me, so I turn to you.

A-Desired Quantity

B-Supplier 1 Price

C-Supplier 1 Qty

D-Supplier 2 Price

E-Supplier 2 Qty

So in row F, I want to find the Min value of B and D and LIMIT it to the quantity associated with it. So if Supplier 1 has the lowest price but 10 units, then I want F to represent 10 (if A > 10). I can (easily) find the minimum price, but I can't figure out how to restrict the quantity based on the corresponding maximum value in C and E and my own desired quantity in A. And then of course I'd like to have the second supplier fill my remaining quantity into G or something, but that's another task.

I've tried inserting a custom data validation setting restricting the limit into F based on C but if it went over I just got an error message because it was too high a result (the result didn't cap itself). I think I can get around it by doing something like =MIN(IF(B2<D2, MIN(A2,C2))) which would give me a result of either the lowest number of units I want to order from supplier A if he was the lowest based supplier but then I'd have to do repeating formulas for if Supplier 2 had the lowest price etc and more formulas for second-cheapest supplier (ie, if supplier 1 has the lowest price but a lower quantity then I want to order, I have to order from 2 suppliers) and it grows exponentially (2 suppliers=4 formulas, 3 suppliers=9)... I think that makes sense? If it doesn't just ignore me, I'm slightly delirious from trying to work this one out. Thanks

So I have a column in each of my workbooks that have matching data but they are not in the same order. My issue is i need one of the columns in one of my workbooks to merge into the other workbook where these ids match even though they are not in the same order.

]]>I have problem using list in Data Validation since its not automatically listed.

I want to use automated list, for example (in the attachment) if the country is Trinidad & Tobago, the city list automatically only shown Arnos Vale and Arouca, instead of showing Washington, New York, London, and other cities.

Thanks before.

I want to use automated list, for example (in the attachment) if the country is Trinidad & Tobago, the city list automatically only shown Arnos Vale and Arouca, instead of showing Washington, New York, London, and other cities.

Thanks before.

I have a set of multiple formulas that generate certain values. I need to set limit to the values generated. For instance, if the value is less than 200, I need the actual value which is less than 200, and if the value is greater than or equal to 200, I need 200 only. the formula is copied for reference. Someone plz help

=IF(AND(O3="W",E3>='Workings Model'!$O$2,Trip!E3<'Workings Model'!$P$2),Trip!F3*'Workings Model'!$Q$2%,"")&IF(AND(Trip!O3="W",Trip!E3>='Workings Model'!$O$3,Trip!E3<'Workings Model'!$P$3),Trip!F3*'Workings Model'!$Q$3%,"")&IF(AND(Trip!O3="W",Trip!E3>='Workings Model'!$O$4,Trip!E3<'Workings Model'!$P$4),Trip!F3*'Workings Model'!$Q$4%,"")&IF(AND(Trip!O3="W",Trip!E3>='Workings Model'!$O$5,Trip!E3<'Workings Model'!$P$5),Trip!F3*'Workings Model'!$Q$5%,"")&IF(AND(O3="W",Trip!E3>='Workings Model'!$O$6,Trip!E3<'Workings Model'!$P$6),Trip!F3*'Workings Model'!$Q$6%,"")&IF(AND(Trip!O3="E",Trip!E3>='Workings Model'!$O$7,Trip!E3<'Workings Model'!$P$7),Trip!F3*'Workings Model'!$Q$7%,"")&IF(AND(Trip!O3="E",Trip!E3>='Workings Model'!$O$8,Trip!E3<'Workings Model'!$P$8),Trip!F3*'Workings Model'!$Q$8%,"")&IF(AND(Trip!O3="E",Trip!E3>='Workings Model'!$O$9,Trip!E3<'Workings Model'!P10),Trip!F3*'Workings Model'!$Q$9%,"")&IF(AND(O3="E",E3>='Workings Model'!$O$10,Trip!E3<'Workings Model'!$P$10),Trip!F3*'Workings Model'!$Q$10%,"")&IF(AND(Trip!O3="E",Trip!E3>='Workings Model'!$O$11,Trip!E3<'Workings Model'!$P$11),Trip!F3*'Workings Model'!$Q$11%,"")&IF(AND(Trip!O3="R"),0,"")

]]>=IF(AND(O3="W",E3>='Workings Model'!$O$2,Trip!E3<'Workings Model'!$P$2),Trip!F3*'Workings Model'!$Q$2%,"")&IF(AND(Trip!O3="W",Trip!E3>='Workings Model'!$O$3,Trip!E3<'Workings Model'!$P$3),Trip!F3*'Workings Model'!$Q$3%,"")&IF(AND(Trip!O3="W",Trip!E3>='Workings Model'!$O$4,Trip!E3<'Workings Model'!$P$4),Trip!F3*'Workings Model'!$Q$4%,"")&IF(AND(Trip!O3="W",Trip!E3>='Workings Model'!$O$5,Trip!E3<'Workings Model'!$P$5),Trip!F3*'Workings Model'!$Q$5%,"")&IF(AND(O3="W",Trip!E3>='Workings Model'!$O$6,Trip!E3<'Workings Model'!$P$6),Trip!F3*'Workings Model'!$Q$6%,"")&IF(AND(Trip!O3="E",Trip!E3>='Workings Model'!$O$7,Trip!E3<'Workings Model'!$P$7),Trip!F3*'Workings Model'!$Q$7%,"")&IF(AND(Trip!O3="E",Trip!E3>='Workings Model'!$O$8,Trip!E3<'Workings Model'!$P$8),Trip!F3*'Workings Model'!$Q$8%,"")&IF(AND(Trip!O3="E",Trip!E3>='Workings Model'!$O$9,Trip!E3<'Workings Model'!P10),Trip!F3*'Workings Model'!$Q$9%,"")&IF(AND(O3="E",E3>='Workings Model'!$O$10,Trip!E3<'Workings Model'!$P$10),Trip!F3*'Workings Model'!$Q$10%,"")&IF(AND(Trip!O3="E",Trip!E3>='Workings Model'!$O$11,Trip!E3<'Workings Model'!$P$11),Trip!F3*'Workings Model'!$Q$11%,"")&IF(AND(Trip!O3="R"),0,"")

Can anybody tell me if there is a way to change the default currency symbol from $ to £ please ? The £ used to be at the top of the list but has now moved right down meaning I have to scroll and change the symbol every time I use it.

It is one of those little irritations that, over a period of time has now started driving me mad :eek:

]]>It is one of those little irritations that, over a period of time has now started driving me mad :eek:

Hello

I'm trying to create VBA code to do this: I have three columns, one is the identifier of the person the other the date of the task and the other the task.

For each person I need to create a string as shown in bold in the example. The string always starts with -3 and ends with -2

All the tasks that are performed by a person the same day are separated by -1 values. Therefore every string ends always with -1-2

I should attach the excel but I'm not able to attach excel files

It doesn't matter if in the result there are blank rows inbetween each different person as in the example. The identifiers are in column A, the date in column B, the Task (is a number) and is in column C, column D is just to show the tasks in one day but I dont have to create it, it is just to show the example, I must create the column E which is in Bold.

I need to traverse the A column keeping track of the changes in B column and adding to the string and when it changes the id I need to create a new string but I'm new to VBA and doing a mess with this

]]>I'm trying to create VBA code to do this: I have three columns, one is the identifier of the person the other the date of the task and the other the task.

For each person I need to create a string as shown in bold in the example. The string always starts with -3 and ends with -2

All the tasks that are performed by a person the same day are separated by -1 values. Therefore every string ends always with -1-2

I should attach the excel but I'm not able to attach excel files

Code:

`IDENTIFIER DATE TASK `

1 11/02/2008 149 (149 18 18 18),(22), (145 43),(25) ** -3 149 18 18 18 -1 22 -1 145 43 -1 25 -1 -2**

1 11/02/2008 18

1 11/02/2008 18

1 11/02/2008 18

1 12/02/2008 22

1 13/02/2008 145

1 13/02/2008 43

1 14/02/2008 25

2 13/02/2008 19 (19 18),(18 18) **-3 19 18 -1 18 18 -1 -2**

2 13/02/2008 18

2 14/02/2008 18

2 14/02/2008 18

3 14/02/2008 39 (39),(149 43 19 19) **-3 39 -1 149 43 19 19 -1 -2**

3 15/02/2008 149

3 15/02/2008 43

3 15/02/2008 19

3 15/02/2008 19

Always starts with -3

Always ends with -2

Different days are in between -1 except the first one

All of them separated by spaces

It doesn't matter if in the result there are blank rows inbetween each different person as in the example. The identifiers are in column A, the date in column B, the Task (is a number) and is in column C, column D is just to show the tasks in one day but I dont have to create it, it is just to show the example, I must create the column E which is in Bold.

I need to traverse the A column keeping track of the changes in B column and adding to the string and when it changes the id I need to create a new string but I'm new to VBA and doing a mess with this

Code:

`Sub lines()`

Columns(1).Font.Color = vbBlack

Dim tasks As String

Dim c As Range, rng As Range

Dim i, after, before, posnul As Integer

Set rng = Range("A1:A18")

i = 1

posnul = 1

tasks="-3"

after = ""

For Each c In rng

If after=before Then

tasks = tasks & "-1 " &

tasks = tasks & " " & Cells(i, 3)

Else

posnul = i

tasks=tasks &"-2"

i = i + 1

Next c

End Sub

This afternoon I was working on a worksheet that had numerous form controls on it. I come back 5 hours later and all these controls are gone, plus a place where an embedded picture goes. The other worksheets still have their controls. Just this main sheet. Nobody else was on the computer during this time.

Needless to say, it's gonna be hard getting these controls set back up.

Is there a setting in Excel 2010 that hides these controls?

]]>Needless to say, it's gonna be hard getting these controls set back up.

Is there a setting in Excel 2010 that hides these controls?

Dear Forum.

I am trying to find a formula that is checking various cells for specific text. The solutions I've seen (ISNUMBER or COUNTIF) only show the formulas for if the cells that you are checking are adjacent (e.g. A12:A26). However in my case the cells that I'm looking to check are not next to each other.

For the example of the screen shot below… I’m looking for a formula for cell E5. That formula would look in cells A10,A11,C10,C11,F10,F11 for the word “Paint”. If the word “Paint does exist in any of those ceils then I want the word “Yes” to be shown in cell E5.

Thank you.

ExcelContainTextScreen.jpg

I am trying to find a formula that is checking various cells for specific text. The solutions I've seen (ISNUMBER or COUNTIF) only show the formulas for if the cells that you are checking are adjacent (e.g. A12:A26). However in my case the cells that I'm looking to check are not next to each other.

For the example of the screen shot below… I’m looking for a formula for cell E5. That formula would look in cells A10,A11,C10,C11,F10,F11 for the word “Paint”. If the word “Paint does exist in any of those ceils then I want the word “Yes” to be shown in cell E5.

Thank you.

ExcelContainTextScreen.jpg

Dear Forum.

I am looking to get the results from filtering a table to report outside of the table (so I can use to look up against further tables, etc.).

Please see simple file to illustrate what I'm trying to achieve. (For some reason I am currently unable to attach the file. Please see below link to said file on Dropbox).

I am looking to know what formula would be entered into cells A11 thru F11 so that it would report the relevant entry after filtering the table (A16-F31). The intent is that the user will filter the table so that there is only one entry.

As per the example in the simple file, my actual real world file has both text and numbers.

Any insight as to how to best achieve would be greatly appreciated.

https://www.dropbox.com/s/2hnvr5mpdn...ulas.xlsx?dl=0

]]>I am looking to get the results from filtering a table to report outside of the table (so I can use to look up against further tables, etc.).

Please see simple file to illustrate what I'm trying to achieve. (For some reason I am currently unable to attach the file. Please see below link to said file on Dropbox).

I am looking to know what formula would be entered into cells A11 thru F11 so that it would report the relevant entry after filtering the table (A16-F31). The intent is that the user will filter the table so that there is only one entry.

As per the example in the simple file, my actual real world file has both text and numbers.

Any insight as to how to best achieve would be greatly appreciated.

https://www.dropbox.com/s/2hnvr5mpdn...ulas.xlsx?dl=0

Where can I change my email address. Please reply to E-mail address removed by moderator.

]]>Hello,

an ex colleague of mine had an excel, where columns and rows were automatically formatting when you added something new.

For example, I added some new data into rows, and all the subsequents columns would be formatted.

How can you do this, that if its empty the cells are blank, but when you fill them out they format ? Cheers

]]>an ex colleague of mine had an excel, where columns and rows were automatically formatting when you added something new.

For example, I added some new data into rows, and all the subsequents columns would be formatted.

How can you do this, that if its empty the cells are blank, but when you fill them out they format ? Cheers

Hello! Im having some problems. Excel automatically converts my seconds to millions. How can I solve this? The Y-axis should show 16 as maximum, not 16 million :)

Timeline.png

Best regards,

Totte_

]]>Timeline.png

Best regards,

Totte_