I uesta know how to do this stuff… 20 years ago.

I’m putting together the food plan for a couple of teams participating in the Oxfam Trailwalk next year. I have a spreadsheet of 5 worksheets (PLAN, Energy use, Foods, Hydration, Segment) and need to bring them together into the PLAN sheet such that the participant just needs to enter their weight, walking speed, and select foods from a list for each segment of the walk. The intention is to make sure they’re eating and drinking properly.

Energy Use sheet has a column for body weight (in 5kg increments) and columns for the energy requirement for Slow, Mod and Fast walking at each weight.

Foods is a list of 30 foods with their energy content per serving.

Hydration is a list of 4 drinks with their energy content per drink.

Segment is a list of the segments with distance and difficulty data, from which I have created likely time (at slow, mod, fast speeds) and a separate allowance for rests.

PLAN sheet has a column for foods and a column for each segment, with a ‘goal kj’ and ‘current kj’ cells. The intention is that the participant should be able to see how much energy they will need for each segment and select a food (by placing tick in the corresponding cell of a segment) and the sheet will automatically add the kj to the ‘current’ cell.

I know how to do most of the tricks, but could use some help to create drop-down lists for ‘weight’ and ‘Walk speed’, then bring the values together into a ‘kj/hr’ cell, from which I can make the calculations for each segment.

Lookup the VLookup function.

That will do most of the work to return all the numbers you need, based on an entered weight (or any other variable in a table of data).

The drop-down lists are not too hard, but in this case are they actually adding any value? Why not just have people enter a number?

Just worked out the Drop Down lists (with help from Google).

Will go searching on VLookup.

Cheers.

The Rev Dodgson said:

The drop-down lists are not too hard, but in this case are they actually adding any value? Why not just have people enter a number?

Because it would mean calculating values I’ve already got sitting in a table.

Rule 303 said:

The Rev Dodgson said:

The drop-down lists are not too hard, but in this case are they actually adding any value? Why not just have people enter a number?

Because it would mean calculating values I’ve already got sitting in a table.

That’s what the VLookup is for, but it needs a number input to look up the value from a table. That number can come from a drop down list, but it’s easier just to type in a number.

The Rev Dodgson said:

That’s what the VLookup is for, but it needs a number input to look up the value from a table. That number can come from a drop down list, but it’s easier just to type in a number.

Ahh, OK. The calculations for energy used are for body weight in 5kg increments.

Hope that makes sense.

Rule 303 said:

The Rev Dodgson said:

That’s what the VLookup is for, but it needs a number input to look up the value from a table. That number can come from a drop down list, but it’s easier just to type in a number.

Ahh, OK. The calculations for energy used are for body weight in 5kg increments.

Hope that makes sense.

Seems a strange calculation.

Rule 303 said:

The Rev Dodgson said:

That’s what the VLookup is for, but it needs a number input to look up the value from a table. That number can come from a drop down list, but it’s easier just to type in a number.

Ahh, OK. The calculations for energy used are for body weight in 5kg increments.

Hope that makes sense.

The VLookup function returns values from the last row with a lookup value equal or less than the entered value. If your table had weight values of say 50, 55, 60, … then any weight entered between 55.0 and 59.999999 kg would return the data from the 55 kg row.

But re-reading your OP, I see you have a list of foods to choose from, so a drop-down box is the easiest way to do that, and not have to worry about typos etc.

poikilotherm said:

Seems a strange calculation.

Trying to calculate how much each person needs to eat within each segment of a 100km walk. I have figures for the energy required / kg of body weight / hour at three levels of walking intensity. Need to get this stuff right for ultra-endurance events.

Rule 303 said:

poikilotherm said:

Seems a strange calculation.

Trying to calculate how much each person needs to eat within each segment of a 100km walk. I have figures for the energy required / kg of body weight / hour at three levels of walking intensity. Need to get this stuff right for ultra-endurance events.

I guess taking a backpack full of gels and protein bars would get a bit tiresome ;) .

poikilotherm said:

I guess taking a backpack full of gels and protein bars would get a bit tiresome ;) .

It’s a fully-supported event (ie, each team has a support vehicle which meets them at designated rest areas) so they only need to carry enough for each segment. Not only do you have to get the energy right, the food has to be right. 48 hours of continuous gels and bars (at the rate of about 2000kj/hr) will leave even the strongest gut very sick indeed.

Rev, I’m not sure how the VLookup function will work with this.

I’ve got a table with a column for weight and a column for each of three walking speeds, with the energy used at each weight for each walking speed. I’ve worked out how to put the weights and speeds into drop-down boxes on the PLAN sheet. I’m hoping to read the value for energy that corresponds to the given weight / speed into a cell. Does VLookup do that?

Rule 303 said:

Rev, I’m not sure how the VLookup function will work with this.

I’ve got a table with a column for weight and a column for each of three walking speeds, with the energy used at each weight for each walking speed. I’ve worked out how to put the weights and speeds into drop-down boxes on the PLAN sheet. I’m hoping to read the value for energy that corresponds to the given weight / speed into a cell. Does VLookup do that?

In that case a combination of Match and Index will be better.

Make a table of walking speeds (3 rows), then the formula for energy will be something like:

=INDEX, MATCH)

here’s a brainless oaf of the finest type created by australia

https://en.wikipedia.org/wiki/Robert_Menzies

When World War I began, Menzies was 19 years old and held a commission in the university’s militia unit. He resigned his commission at the very time others of his age and class clamoured to be allowed to enlist. It was later stated that, since the family had made enough of a sacrifice to the war with the enlistment of two of three eligible brothers, Menzies should stay to finish his studies. Menzies himself never explained the reason why he chose not to enlist. It should be noted that the two brothers, James and Frank, who did enlist did not do so until 1915 after the landings at Anzac which belies the alleged reason. Subsequently he was prominent in undergraduate activities and won academic prizes and declared himself to be a patriotic supporter of the war and conscription. Menzies was admitted to the Victorian Bar and to the High Court of Australia in 1918 and soon became one of Melbourne’s leading lawyers after establishing his own practice. In 1920 he married Pattie Leckie, the daughter of federal Nationalist, and later Liberal, MP, John Leckie.

In August 1938, while Attorney-General of Australia, Menzies spent several weeks on an official visit to Nazi Germany. He was strongly committed to democracy for the British peoples, but he thought that the Germans should take care of their own affairs without British interference. He decided Hitler was a German patriot trying to right the wrongs of Versailles, and was not a swashbuckling adventurer liable to cause serious trouble for the British Empire. He strongly supported the appeasement policies of the Chamberlain government in London, and sincerely believed that war could and should be avoided. In visiting Germany in 1938 Menzies was highly impressed with its achievements such as the suppression of trades unions and outlawing of the right to strike, while producing prosperity and ending unemployment. He said he was “deeply impressed” by the “spirituality” of the German people, their unselfish attitude, their less materialist outlook on life, and their preparation to make sacrifices on behalf of the Nation. Back home he expressed mixed views of Nazi Germany. He told audiences that Hitler’s dictatorship was guilty of “unspeakably bad things”. However, he added, there were features Australians should learn from, such as industrial efficiency. He explained that the great majority of Germans were supportive of Hitler, and that the youth were especially enthusiastic. Above all, Menzies insisted that war with Germany would be a horrible tragedy, and should be avoided at all costs. In July 1939, Menzies was heckled when he stated: “History will label Hitler as one of the really great men of the century.”

In 1938 his enemies ridiculed him as “Pig Iron Bob”, the result of his industrial battle with waterside workers who refused to load scrap iron being sold to Imperial Japan. In 1939,

The Rev Dodgson said:

Rule 303 said:

Rev, I’m not sure how the VLookup function will work with this.

I’ve got a table with a column for weight and a column for each of three walking speeds, with the energy used at each weight for each walking speed. I’ve worked out how to put the weights and speeds into drop-down boxes on the PLAN sheet. I’m hoping to read the value for energy that corresponds to the given weight / speed into a cell. Does VLookup do that?

In that case a combination of Match and Index will be better.

Make a table of walking speeds (3 rows), then the formula for energy will be something like:

=INDEX, MATCH)

Actually it won’t be anything like that.

If you click the Quote button you’ll see what I really wrote.

The Rev Dodgson said:

In that case a combination of Match and Index will be better.

Make a table of walking speeds (3 rows), then the formula for energy will be something like:

=INDEX, MATCH)

Ugh, not explaining myself… Sorry mate.

This is what I’ve got now:

Weight | Slow | Mod | fast

60 | 668 | 690 | 800
65 | 675 | 702 | 808
70 | 681 | 710 | 820

And so on.

The numbers in the three speed columns are the KJs being used.

=INDIRECT,4),“1”,”“)&MATCH)

where F4 contains the selected weight and G4 contains the selected speed.

Just to simplify it A2:A5 contain list of weights and B1:D1 contains list of speeds

Select post quote to see formula in full…

The Rev Dodgson said:

The Rev Dodgson said:

Rule 303 said:

Rev, I’m not sure how the VLookup function will work with this.

I’ve got a table with a column for weight and a column for each of three walking speeds, with the energy used at each weight for each walking speed. I’ve worked out how to put the weights and speeds into drop-down boxes on the PLAN sheet. I’m hoping to read the value for energy that corresponds to the given weight / speed into a cell. Does VLookup do that?

In that case a combination of Match and Index will be better.

Make a table of walking speeds (3 rows), then the formula for energy will be something like:

=INDEX, MATCH)

Actually it won’t be anything like that.

If you click the Quote button you’ll see what I really wrote.

Ahhh… Hehe

You can try furious’s Indirect as well, they essentially do the same thing. I prefer Index because you don’t need to change anything if you move the table.

Offset is another alternative, which essentially does the same as Index.

Testing…

=INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH(G4,A1:D1,FALSE),4),"1","")&MATCH(F4,A1:A5,FALSE))

Thank you for your help guys. I have not been able to get the table look-up working, but I suspect it’s because I fundamentally don’t understand the solutions provided. I’m sure it will be easy enough for the users to put in the ‘energy’ figure from the table themselves.

100000 sign ban trump from uk petition
http://news.sky.com/story/1602440/100000-sign-ban-trump-from-uk-petition

wrong thread sorry