Forum Discussion
How to reference to a cell but not with vlookup function or referencing by + sign
Hi,
can someone help me with this issue.
I will have a trial balance in one sheet of excel file. In another sheet I will have costs according to some subgroups, and in third sheet there will be profit and loss statement.
How could I reference to first sheet without using vlookup up function or simply referencing to a cell by plus sign. Why I don't want these two solutions? Vlookup is not suitable, because I will not have a tree structure, so accoutns won't be written in costs sheet. I don't want to work neither with plus sign, because I am 100% sure that after some time some new accounts will be added, and the references won't be correct anymore. I will use this template for a long time, and plan to copy paste new trial balance each time. And each time some new accounts will be added, so referencing with + is not an option.
For example what I want is: in this cell (orange in in picture) show me values from column B, that have in column A in their name first two digits 40*. OR in this cell show me values from column B, that have in column A these numbers 40201, 40202, 40203.....).
How about this?
Though make sure that the account number in A are texts. And in this example I limited the data ranges to only 20 rows. You can adjust that a much larger range, though avoid referencing entire columns like A:A.
Even better to use a structured table for the data. Such a table will expand automatically when you add rows to it and you can reference ranges by the table and column name.
2 Replies
Riny_van_Eekelenthank you for your answer. I actually did it with sumif. I added two more columns next to each account, wrote the P&L item (income, costs....etc...) and did the whole P&L sheet and costs sheet with sumifs. Thank you for the formula ("40*"), might use this one next time.
How about this?
Though make sure that the account number in A are texts. And in this example I limited the data ranges to only 20 rows. You can adjust that a much larger range, though avoid referencing entire columns like A:A.
Even better to use a structured table for the data. Such a table will expand automatically when you add rows to it and you can reference ranges by the table and column name.
