Excel Questions

[Hashtag]

Bascially I have a table of information like below

Product       Description       Size
X1                 Thing 1             1
X2                 Thing 2              1
X3                 Thing 3              1.5


I have created a drop down list so that I can choose the product- then I want the other information to be automatcially pulled into the next cells- ie description, size

So if I chose X2 from my drop down list- the description automatically pulled in would be Thing 1 and the Size would be 1.

[Billys Boots]

I'd say use an IF function with LOOKUP for lists on another page or hidden on this page.

[Maiden1]

Something like below should work if the values you are looking for are in Sheet2 in top left corner.

Then if you enter X3 in Cell A:1 in Sheet1

and have below in cell B:1

=IF(ISNA(VLOOKUP(A1,Sheet2!$A1:$C3,2,FALSE)),"",VLOOKUP(A1,Sheet2!$A1:$C3,2,FALSE))

Cell B:1 would have value 'Thing 3'.

There may be better ways of populating the cells.

[Croí na hÉireann]

Back again, if I had 100 files, named File001 to File100 and I wanted to extract a row of data out of each of them and write these rows to individual new files how would I go about it? I'm thinking strings with a counter but which string function would you use?

[Radda bout yeee]

I have a excel sheet where i am trying to create a value in a cell by multiplying it by another but I only want the value if the one cell is over a certain value if it is not I want the calculation to equal zero.

Eg.

Cell A = Anything between 1-16
Cell B = 3.68
Cell C = A x B (I want this to be zero)

However if it is:
Cell A = Anything over 16
Cell B = 3.68
Cell C = A x B (I want this to be the value e.g. 17x3.68=62.56)

Can anyone help?

[Radda bout yeee]

I have a excel sheet where i am trying to create a value in a cell by multiplying it by another but I only want the value if the one cell is over a certain value if it is not I want the calculation to equal zero.

Eg.

Cell A = Anything between 1-16
Cell B = 3.68
Cell C = A x B (I want this to be zero)

However if it is:
Cell A = Anything over 16
Cell B = 3.68
Cell C = A x B (I want this to be the value e.g. 17x3.68=62.56)

Can anyone help?

In Cell C1:

=IF(A1>16,(A1*B1),0)

Thanks for that - only just realising If it is over 16 I want the value to be the amount over 16 if that makes sense:

E.g.
Cell A = Anything over 16
Cell B = 3.68
Cell C = A x B (I want this to be the value e.g. 17x3.68=3.68 or 27x3.68=40.48)

[Radda bout yeee]

In Cell C:

=IF(A1>16,((A1-16)*B1),0)

BRILLIANT.

Thanks

[LeoMc]

I have an Excel spreadsheet where I need to load a demand over a number of months as follows (numbers in red to be calculated rather than manually entered):

Type   Start   Rate   Max    Jan   Feb   Mar   Apr  May Jun Jul  Aug
A         Feb     3         9       0       3       3       3    0     0     0      0
B         Apr      2        8        0       0       0       2    2     2     2      0

I have the basics in that I added a column to calculate the total months (Max / Rate) and added this to the start month to give me the End point. I then put an If (Month>=start and Month<End, rate,0) and this gives me my demand per month. However this only works where the Max/Rate is a whole number.
For example if the rate was 3 and the Max was 10 my formula would give me 
C         Feb     3         10       0       3       3       3    3     0     0      0

Is there any way to get Excel to load the demand so that only the balance goes into the final month rather than the full amount?

D         Feb     3         10       0       3       3       3    1     0     0      0

Hope this makes sense

[armaghniac]

Is there any way to get Excel to load the demand so that only the balance goes into the final month rather than the full amount?

count the demand left to be fulfilled and use a MIN function
something like
=MIN(TRUNC($A$3/$B$3),$A3-SUM($C$3:E3)) should work

[Aoise]

Trying to analyse some survey results. Small survey so was going to do it myself through excel.  I have no problem coding the straight closed ended multiple choice questions, and the open ended qualitative questions (survey has a mixture of both) I'm having some difficulty however coding a closed ended question with a yes, no, don't know response box followed by a related open ended question that reads 'if the answer to the above question is no, can you explain in your own words why not.'

Two questions:
1- Should this be treated as one question when coding?
2- How would this be represented in order to create frequencies and pivot table cross tabs in excel?

Please treat me as a child if responding, my knowledge of excel is minimal to say the least.

[LeoMc]

Is there any way to get Excel to load the demand so that only the balance goes into the final month rather than the full amount?

count the demand left to be fulfilled and use a MIN function
something like
=MIN(TRUNC($A$3/$B$3),$A3-SUM($C$3:E3)) should work

Thanks, I will play about with this. My existing formula is =IF(AND($B2<=L$1,$F2>L$1),$D2,0) so it is the $D2 I need to replace with this additional function.

EDIT: It worded alright, though I didn't need the Truncation. Cheers.
=IF(AND($B2<=J$1,J$1<$F2),MIN($D2,$C2-SUM($H2:I2)),0)

[Orior]

I'm running Excel 2007 on Windows XP.

When I double click on a spreadsheet file, Excel will open, but the document will not appear until I Alt&Tab away then Alt&Tab back again. Google hasnt helped.

Any ideas?

[ludermor]

i am trying to count the number of occurance a certain date appear in a column but only when it is occurs on the planned date
on the wee table below i want folmula to show me how many times the 10/09/2012 occurs beside planned ( so that it doesnt count the 10/09/2012 beside actual)

Planned   10/09/2012
Actual    10/09/2012
Planned   10/09/2012
Actual    17/09/2012
Planned   10/09/2012
Actual    16/09/2012
Planned   17/09/2012
Actual    17/09/2012

[ludermor]

that isa  work of art, works perfectly with 'planned' but i try the exact same formula and range it wont work if i try 'actual'.
i would have thought by replacing the word and leaving everything else the forlula would work?

[ludermor]

Didnt see your reply here Mac, thanks for your help, i have it working but will have to adapt it soon so i might be needing your help again! Ill drop you a line then cheers.