Jump to content

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??

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?

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?

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.

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.

Link to post
Share on other sites
  • 2 weeks later...

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...