snakehips 0 Posted January 30, 2011 Share Posted January 30, 2011 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 comment Share on other sites More sharing options...
Kevin 1 Posted January 30, 2011 Share Posted January 30, 2011 Link to comment Share on other sites More sharing options...
JawD 31 Posted January 30, 2011 Share Posted January 30, 2011 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 comment Share on other sites More sharing options...
nufc4ever 0 Posted January 30, 2011 Share Posted January 30, 2011 You need the VLOOKUP function. Link to comment Share on other sites More sharing options...
snakehips 0 Posted January 30, 2011 Author Share Posted January 30, 2011 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 comment Share on other sites More sharing options...
snakehips 0 Posted January 30, 2011 Author Share Posted January 30, 2011 You need the VLOOKUP function. Thanks. I have been thinking that, but haven't fully grasped what it means. Link to comment Share on other sites More sharing options...
nufc4ever 0 Posted January 30, 2011 Share Posted January 30, 2011 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. Link to comment Share on other sites More sharing options...
peasepud 59 Posted January 30, 2011 Share Posted January 30, 2011 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 comment Share on other sites More sharing options...
nufc4ever 0 Posted January 30, 2011 Share Posted January 30, 2011 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. Link to comment Share on other sites More sharing options...
peasepud 59 Posted January 30, 2011 Share Posted January 30, 2011 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 comment Share on other sites More sharing options...
nufc4ever 0 Posted January 30, 2011 Share Posted January 30, 2011 I sort too. I just like using FALSE so it's obvious if I make an input typo. Link to comment Share on other sites More sharing options...
snakehips 0 Posted January 30, 2011 Author Share Posted January 30, 2011 Thanks gents I'll see how it goes. Link to comment Share on other sites More sharing options...
Tooj 17 Posted January 30, 2011 Share Posted January 30, 2011 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? Link to comment Share on other sites More sharing options...
peasepud 59 Posted January 30, 2011 Share Posted January 30, 2011 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? so what was it again? VLOOKUP or OPENBOWDOORS? Link to comment Share on other sites More sharing options...
Gejon 2 Posted February 13, 2011 Share Posted February 13, 2011 I guess you managed it? Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now