I have a sheet in my workbook which makes a calculation.
On another sheet I would like to list various numbers and next to the numbers the calculation from the first sheet.
How can I do that?
Replies (11)
Please login or register to join the discussion.
Nothing like enough information!
To help we will need to know what the calculations represent and what is the relationship between them and the numbers on sheet 2.
It may be.
the subject matter is outside my area of expertise but I am sure someone will help.
Vlookup
You will need to use the VLookup function to return the exit charge applicable to various distributions being considered. One word of warning though, you'll need to make sure your list of possible distribution levels and the appropriate exit charges are sorted into numerical order from low to high. Also, if you use Vlookup you can either tell it to find an exact match (ie your distribution level on sheet 2 exists within the table on sheet 1) or a closest match (in which case it would return the exit charge for the distribution level which is just below the figure you're searching for, eg if your table on sheet 1 was £100k, £150k, £200k, £250k etc, and you entered £105k on sheet 2, it would return the exit charge applicable to the £100k from sheet 1). You could force the sheet 2 entries to only come from the range of possibles on sheet 1 using data validation and specifying the range on sheet 1 eg if your table on sheet 1 gave 10 possible distribution levels, your entry on sheet 2 could be set so that it was only possible to pick one of the values from the table. I hope this helps
I know nothing about Trust exit charges
but I assume it is not just a table of percentages (like Stamp duty) in which case you could use VLOOKUP.
So on the assumption that you have a complex formula which you wish to evaluate with a number of different inputs, a Data table may well be the answer. This is found under the What-if icon on the Data tab. There is an explanation of its use here which is rather better than I would manage:
http://office.microsoft.com/en-gb/excel-help/calculate-multiple-results-...
Worksheets
Cant help with the accounts, but if you just want to reference another work sheet it's like this:
=Sheet1!A1
where 'Sheet1' is the name of the other sheet, and 'A1' is the cell ID. Apologies if I've misunderstood your question.
Answers the question given
@dnicholson
Your answer really cheered me up. I suspect the problem the OP has is a bit more complicated than that, but your answer perfectly covers the question as originally stated. This is why it is always best to include detail in the OP rather than doling it out in as small parts as possible when people ask specific questions, which is what is happening here.
Indexing
I'd suggest avoiding VLookups if you can - they link to cells, so if anything ever changes the dynamics of the spreadhseet, if can result in errors. I'd use an INDEX formula - they are a lot more accurate.
I don't understand...
I'd suggest avoiding VLookups if you can - they link to cells, so if anything ever changes the dynamics of the spreadhseet, if can result in errors. I'd use an INDEX formula - they are a lot more accurate.
I don't understand the basis of your comment. Vlookup works with arrays of cells whereas Index works with a one dimensional list. Both have to identify the range of cells that the function will be working with. Whilst an Index / Match function combination may be more efficient and flexible than Vlookup, I can't see how it would ever be more "accurate". Perhaps you could explain in a bit more depth?
Regards,
Ian
Reasons to pick an INDEX MATCH over a VLOOKUP
I'd suggest avoiding VLookups if you can - they link to cells, so if anything ever changes the dynamics of the spreadhseet, if can result in errors. I'd use an INDEX formula - they are a lot more accurate.
I don't understand the basis of your comment. Vlookup works with arrays of cells whereas Index works with a one dimensional list. Both have to identify the range of cells that the function will be working with. Whilst an Index / Match function combination may be more efficient and flexible than Vlookup, I can't see how it would ever be more "accurate". Perhaps you could explain in a bit more depth?
Regards,
Ian
Hi Ian - please allow me to apologise for the formatting of this - my computer seems to all of a sudden have an issue with line breaks, so if it doesn't come out right...
The key difference between an Index Match and VLookup is that Vlookup requires a static column reference while Index uses a dynamic column reference. With VLookup, most people will input a specific, static number to indicate which column they want to return from. When you use INDEX MATCH, the formula allows you to manually choose which column you want to pull from.
The reason this leads to fewer errors is because when you follow the Index Match Syntax, you click directly on the field containing the value you want to return where the Vlookup syntax means you specify a table array and THEN a column reference (or range lookup). It may seem like a small difference, but the additional step can lead to more errors, especially when dealing with very large table arrays - it reduces this risk using Index Match.
The best thing is that you can also add some column immunity, which means if you were to add a column into your array, it will not mess up your data, rather than a vlookup which depends on that column reference.
Plus it is easier to drag and drop, you are not restricted to square table arrays, you can lookup right-to-left, it can double up to replace Hlookup, and finally, the Index Match formula has a lower processing need, which means you can have more in there before your spreadsheet starts slowing down.
So that is 7 reasons why an Index Match is always better than a Vlookup. the accuracy is mostly because of the reduced risk of error.
Thanks
Matt