Edited by author 21-04-2009 11:03 AM
I have followed the steps for creating a holiday calendar, the information provided was very useful (http://www.meadinkent.co.uk/xl-holiday-planner.htm)

One thing the calendar seems to miss is bank holidays, any idea how they can be added,

Many thanks

baber (ub4u2 at hotmail dot co dot uk)

This si Rebecca again. I am still working on my question below, but have gotten a little further. Can you use a Vlookup function to return a pull down menu?

Thanks

Good morning ... I am working with self created pull down menues. I have an initial pull down menue. When you pick a selection from there, I would like it to reference another pull down menu (there are a total of three options in the first menu. Depending upon which option is chosen, I would like another pull down menue to appear with anywhere from 5 to 20 options. Is there a way for one pull down menu to reference another menu from which the quantity is selected?

Thank you!

Hello there - I would be very gratful if anyone has any ideas re this....

I am putting together a sheet that records staff sickness - the department managers have a 4 trigger points (see below) that they need to be allerted of when a member of staff falls into these categories. I would be really interested to hear any ideas on this - I have worked on a number of if formulas - but I think there may be a better solution.

These records need to be on a rolling 12 month basis

:- as below
Trigger Levels
1st Trigger Level 3 occasions within a 12 month rolling period or 2 occasions if absences exceed 7 days

2nd Trigger Level A further 2 occasions since previous meeting within a 12 month period or 1 occasion if absence exceeds 3 days

3rd Trigger Level A further 2 occasions since previous meeting within a 12 month period or
1 occasion is absence exceeds 3 days

4th Trigger Level A further 2 occasions since previous meeting within a 12 month period or
1 occasion if absence exceeds 3 days

Many Thanks

How do I remove duplicate addresses in my excel spread sheets

Edited by author 29-03-2009 09:18 PM
I have a number of sheets each containing information for a particular venue with each tab named after the venue. I have a summary sheet which list the totals in a column for each venue with a grand total column. I may need to add more - the total column for each sheet has the formula: =SUMPRODUCT((Venuename!$C$2:$C$21=$B2)*(Venuename!$H$2:$H$21)). The column with this formula has in the first row the name i.e. "Venuename" - my queston is: rather than "Venuename" (refering to the sheet name) can I substitute this for the column name so that when I copy the formula across I don't have to go and change the sheet name in the formula?

Kent

/2200 Raymond Bewsey
Had a more thorough look at your post, and it ties in very closely with what I've been doing. Call it beginner's luck. I'd like to show you a sample result, but don't know how to do that. Wonder if this will work... nope.

Next problem is how to present the results. One helpful way might be to plot a graph/chart like a weather chart, with the isobars being the equivalent of lines of equal temperature. Preferably also with orthogonal streamlines, pushing my luck. Any ideas?

Thanks again
Mike

Edited by author 22-03-2009 12:57 PM
/2200 Raymond Bewsey

Wow! It's late, so a quick but large thankyou for going to all that trouble.

I have meanwhile made a bit of progress along the lines you suggest. I found Excel does do iteration, you just have to click through the warnings that flash up. It is set up via tools-options-options-calculation.(edit: If you check the iteration box here the circular refernce warnings don't come up).

I realised I could get somewhere using several sheets, and so far it seems just three are enough - one for the formula,the geometry (copied through to the other sheets) and the result (temperature), another one for conductivity (taken as being constant for any particular material), and a third for volumetric heat capacity. That last property takes care of both density and specific heat.

It took me a while to come up with a formula that worked ok for materials of of very different properties (eg wet clay next to expanded polystyrene), and it does the iteration by a very basic method which although slow to converge, mimics the actual dynamics of the ground warming or cooling throughout the seasons - or at least I think it does!

I can't tell you how encouraged I am to have your response. I will study it more closely earlier in the day when my grey cells are less likely to be operating in old geezer mode. Meanwhile, profound thanks.

Mike

/2201

I think you will need to upgrade or ask the sender to do the truncating for you.

Unlucky.

I have received an excel file with more than 256 columns. The columns are getting truncated after 256. I have excel 2003. How do I access these columns

/2195 Grime Reaper

There may be a better or easier way to do this. The main problem I can see is that EXCEL doesn't do Iteration (or does it?) as it doesn't like circular references. Richard may have another simpler answer to this or a way around this? - I would do a little bit of visual Basic code to update the appropriate sheet/cells, and trigger the VB code via a button. But for now lets just set up the basics... I have chosen to set up separate sheets even for the 'fixed variables' just in case any part of the (ground areas) representing their respective fixed variables requires changing at a later date - that way you are not delving into each separate formula on one sheet. Plus it makes the 'formula' sheet easy to set up too...

You require 3 fixed variables per cell (lump of ground), a temperature sheet and a formula sheet (just the one formula? as I recall from 'physics' doesn't seem quite enough!)... ie doesn't the conductivity change as temperature changes???.. anyhooo...

For the moment you require 5 sheets, so right click the tab on sheet1, select Insert.. Worksheet (do this until you have 5 worksheets)

Right click each worksheet tab and rename each sheet to the following names (or similar) for a formula sheet, a Temperature sheet and the 3 'Fixed Variables' sheets.

'Formula', (or 'New Temperature')
'Temperature',
'Specific Heat',
'Density',
'Conductivity',

Now you have the onerous task of filling in each of the 'Variables' (the last 3) sheets & 'Temperature' sheet with appropriate values for however large you want your finite area to be.

In the 'Formula' sheet you will need to refer to the appropriate cells (probably the equivalent cell) in each of the 'Fixed Variables' and 'Temperature' sheets as nec.

FOR EXAMPLE
If the formula is -: f = t * sh / d + c :- and 'say' you have data set up in cell A1 (one particular lump of ground) in each of the fixed variable sheets etc... then...

Go to the Formula sheet click cell A1
Type = Then Tab click 'Temperature' sheet and click cell A1.
Type * Then Tab click 'Specific Heat' sheet and click cell A1.
Type / Then Tab click 'Density' sheet and click cell A1.
Type + Then Tab click 'Conductivity' sheet and click cell A1.

You wil need to adapt this method for the actual formula you are using, especially as I am guessing that the result in each cell may also have to rely in some part or in some way on adjacent cells to A1 in the other 'Fixed Variables' sheets, unless there are further formulas to include (then do additional formula's sheet as nec)

Once you have set up your formula in cell A1 in the 'Formula' sheet you can simply Copy & Paste this cell to cover the 'finite' area as required in the 'Formula' sheet..

If you get to a point with this where you need to transfer the results of the 'Formula' sheet back into the 'Temperature' sheet (for the iteration) you can do this by the 'Copy and Paste Special by VALUE' (painstaking) method - or use a bit of VBasic triggered by a FORM button for a quick'n'easy update to transfer the results (can talk u thru this if needed).

Let us know how you get on with this. Hope this helps.

Ray

/m2198,

Hello Zolu,

If the source data is always going to be sorted in order then you could try the foloowing formulae.

=MATCH(SMALL($B$1:$B$4,1),$B$1:$B$4,0)
=MATCH(SMALL($B$2:$B$4,1),$B$2:$B$4,0)+1
=MATCH(SMALL($B$3:$B$4,1),$B$3:$B$4,0)+2
=MATCH(SMALL($B$4:$B$4,1),$B$4:$B$4,0)+3

I have not tested it extensively but I think it might work for you.

Good Luck

Richard

Dear Richard,


Kindly help as follows,


A1=10, A2=10, A3=30, A4=40


B1 contain Formula =MATCH(SMALL(A1:A4,1),A1:A4,0)


B2 contain Formula =MATCH(SMALL(A1:A4,2),A1:A4,0)


B3 contain Formula =MATCH(SMALL(A1:A4,3),A1:A4,0)


B4 contain Formula =MATCH(SMALL(A1:A4,4),A1:A4,0)


B1 Answer is 1


B2 Answer is 1


B3 Answer is 3


B4 Answer is 4


Sir, why we r not getting answer in Cell B2 is 2, Kindly assist me with this match function formula or any other formulas, We must need small A1 to A4 values locations. Hope u understand and assist me as soon as u can.

Thanks.

Hi Margaret,

Try =sumproduct

=SUMPRODUCT((B6:B14="a")*(C6:C14=1))

Where B6:B14 is the range with the names in, C6:C14 is the range with the status in. The "a" is the name you are looking for and the 1 is the status you are looking for.

So you should end up with something like:

=sumproduct((a1:A150="Oxford")*(B1:B150="successful"))

HTH

I am keeping a spreadsheet of data around tender submission and success rate. In column a I have the name of the customer (about 5 different names used consistently) in column b I have either successful or unsuccesful. In a separate table I want to count the number of successful tenders to each individual customer. How do I write a formula which says "Count If a1:a150 says Oxford and b1:b150 says successful"

Edited by author 11-03-2009 03:50 AM
Being interested in energy economy,I'm trying to work out how heat flows in the earth using a finite element - or is it relaxation - method. I'm a retired engineer, so I know it should be possible, but my computer skill is too little to get me further than very simplified arrangements. Any offers to guide me, or suggestions of where to look?

For example: I want each cell to represent a lump of ground and to assign to it three fixed properties (specific heat, density, conductivity) and a calculated property (temperature). Much gratitude for any help.