Jump to content
Sign in to follow this  
snakehips

Excel

Recommended Posts

Yo! Any of you boffs a whizz with Excel ??

 

I've created the spreadsheet with various formulas and calculations etc but what I would like to do is this:

 

When I enter a figure in the main sheet, I would like various information, pertaining to that figure I have entered, automatically updated from other sheets which I have created using tabulated information.

 

eg

 

I dip an engine sump and get a figure 5cm. I enter 5 cm into the main sheet and I would like the corresponding row to update automatically with eg volume and weight, from tabulated information on other excel sheets.

 

Anyone??

Share this post


Link to post
Share on other sites
Yo! Any of you boffs a whizz with Excel ??

 

I've created the spreadsheet with various formulas and calculations etc but what I would like to do is this:

 

When I enter a figure in the main sheet, I would like various information, pertaining to that figure I have entered, automatically updated from other sheets which I have created using tabulated information.

 

eg

 

I dip an engine sump and get a figure 5cm. I enter 5 cm into the main sheet and I would like the corresponding row to update automatically with eg volume and weight, from tabulated information on other excel sheets.

 

Anyone??

 

Well you can quite happily make sums that relate to other sheets. Just have those open at the same time when you make the sum. For example you could say something like:

 

=SUM(B36*[youotherworkbookname.xls]Sheet1!$G$36)

 

But you just click the cell in the relevant sheet as part of your sum.

 

Is this what you mean?

Share this post


Link to post
Share on other sites
Yo! Any of you boffs a whizz with Excel ??

 

I've created the spreadsheet with various formulas and calculations etc but what I would like to do is this:

 

When I enter a figure in the main sheet, I would like various information, pertaining to that figure I have entered, automatically updated from other sheets which I have created using tabulated information.

 

eg

 

I dip an engine sump and get a figure 5cm. I enter 5 cm into the main sheet and I would like the corresponding row to update automatically with eg volume and weight, from tabulated information on other excel sheets.

 

Anyone??

 

Well you can quite happily make sums that relate to other sheets. Just have those open at the same time when you make the sum. For example you could say something like:

 

=SUM(B36*[youotherworkbookname.xls]Sheet1!$G$36)

 

But you just click the cell in the relevant sheet as part of your sum.

 

Is this what you mean?

 

Thanks, J

 

No, not really. I don't need the sum, just a recognition of other sheets figures.

 

On the tabulated other sheet I will have a row of info eg 'dip' 'volume' 'weight' that I have put in as fixed values.

 

So when I enter the 'dip' figure on the main page, I want it to automatically recognise the figure I have just entered, read the corresponding 'volume' & 'weight' from the other sheet and enter it onto the row of the main page.

 

Understand what I mean?

Share this post


Link to post
Share on other sites

excel.png

 

H5 is the value you put in.

D5:F9 is the range you want it to look in.

2 is the column you want it to return.

FALSE means if the value isn't present it returns a 'N/A' error to remind you.

Share this post


Link to post
Share on other sites

What nufc4ecer said.

 

Say you have a sheet called DataEntry and want to put the Dip figure in column A

 

You also have a sheet called SumpData which holds all of those pre-determined figures, on that sheet you would select the cells that have the data and click the little white box directly above cell A1, in there type a name for the range, say "SumpFigures" (without the speech marks).

 

On DataEntry you want to display 3 columns of data taken from SumpData based on the Dip figure then (assuming you have row 1 as headings)

 

Enter the following in Cell B2

=VLOOKUP(A2,SumpFigures,2)

 

In C2

=VLOOKUP(A2,SumpFigures,3)

 

and in D2

=VLOOKUP(A2,SumpFigures,4)

 

This tells Excel to lookup whatever is in Cell A2 in column 1 of the SumpFigures range and return whatever is in column 2, 3 or 4 of that range

 

what you need to do is ensure that the table in SumpFigures is in order of Dip.

Share this post


Link to post
Share on other sites
what you need to do is ensure that the table in SumpFigures is in order of Dip.

 

The order shouldn't matter. The FALSE thing is handy though, otherwise it goes mental if you plug in a value that's not in the range.

Share this post


Link to post
Share on other sites
what you need to do is ensure that the table in SumpFigures is in order of Dip.

 

The order shouldn't matter. The FALSE thing is handy though, otherwise it goes mental if you plug in a value that's not in the range.

 

If its not sorted then you need to use the FALSE. Personally I always sort the table (if he data is never going to change that is) and dont bother with the FALSE but the proer way is to sort and use FALSE.

Share this post


Link to post
Share on other sites

Am I the only one who has the mental image of old Snakey somehow sinking the ship, while trying to type in a simple VLookup? :)

Share this post


Link to post
Share on other sites
Am I the only one who has the mental image of old Snakey somehow sinking the ship, while trying to type in a simple VLookup? :razz:

 

 

so what was it again? VLOOKUP or OPENBOWDOORS? :)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this  

Recent tweets

Toontastic Facebook

Donate to Toontastic

Keeping the lights on since... well ages ago
TT-Staff


×