data mapping help?

Hi there,

I am new to data mapping and would like some help if possible?

Please see attached for a snapshot of a trial balance download (trial balance tab).
What I would like in the first tab is that the revenue grouped and totalled together next to revenue and other, based on numbers from trial balance.

For example, I would like a formula to add all headings under revenue and populate the cell in D7 with £29,700 and the total next to the Other heading £8,425 (cell D8)

So all data to be picked up from trial balance and populate into the first tab.

I hope you can help?

Regards

N

You can use this formula in D7:

=SUM('Trial Balance'!C8:C13)

and this one in D8:

=SUM('Trial Balance'!C16:C19)

Hope this helps.

Pete

Thanks for the quick reply Pete,

However, I was hoping you can help with a named list or data mapping as sometimes the revenue and other amounts my be mixed up. I have tried to make it simple by putting them together. In other words how would you do it if they were mixed up and the list was a lot longer?

I hope you can help?

Regards

N

guessing it would be name range then Mike?
I was advised to do data mapping on another sheet (using chart of account numbers) but have no idea where to start.

Regards

N

Create a named range each for Revenue and Other.

Revenue:

Formula:

Please Login or Register  to view this content.

Ctrl+Shift+Enter to capture the formula above.

Formula adapted from Haseeb A:
https://www.excelforum.com/excel-for...ml#post4708675

Hi Shuka,

I think this is what I am looking for.
I will try It out on the full trial balance and let you know.
Can I ask you to explain the formula please you have placed in D7?
I am not clear what the OFFSET formula does? Why are the cells merged together?

Regards

N

Hi Mike,

This seems to have worked as well.
Can I ask in steps how you created the name range for Revenue and Other, in order for me to learn and do myself?
Thanks Mike,

Regards

N