Trying to Figure Out a Better Way to Setup My Dataset

[EdwardSnowden]

Please ignore the terrible title. Attached is a workbook I created as a demonstration of what my current method is for the following:

I have quite a few funds and they are in order from which account to draw from first. The leftover amounts go to the subsequent account from which funds are allowed to be pulled from. I don't like the way this is currently looking and I've tried to make this more visually understandable. My actual file has about 15 different fund accounts and 20+ projects which creates quite a lengthy scrolling process.

Do you have any recommendations on how to make this workbook structured differently for a more effective presentation/summary? Any advice/guidance in the right direction is highly appreciated :D

[AliGW]

The terrible title has passed muster - it says what you are trying to do. If it dodn't, I would not ignore it, even if you asked me to!

I will have a look.

[AliGW]

Are you using a Mac?

[EdwardSnowden]

I'm using a PC. Also, thank you so much :D!

[AliGW]

So which version of Excel on your PC?

[EdwardSnowden]

Just checked and it is Excel 2016.

[Glenn Kennedy]

I'd go for something like this...

[EdwardSnowden]

Glenn, I do like this method! I need to wrap my head around on how to set it up. I'm also interested in AliGW's thoughts. If I don't respond right away it's because I'm trying to replicate Glenn's workbook

[AliGW]

That's what I would go for, too.

[EdwardSnowden]

Ali, thank you! You and everyone on this forum are always so kind and helpful.

[MarvinP]

Hi Edward,

I'd have only 4 columns in a table: Date, Fund, Project and Amount. If you are adding dollars they would be put in as Positive numbers and Projects would be negative numbers. Then you could do a pivot table or even a filtered table and they would sum to the Amount Left.

Learn a bit more about Pivot Tables and the Refresh ability. Also I'd use a Dynamic Named Range for my pivot so you could simply add rows on the bottom of your data and it would include them.

I'd also use exact dates for project transactions as Pivot tables can deal with those very well.

https://www.ablebits.com/office-addi...c-named-range/

https://www.excel-easy.com/data-****...ot-tables.html

Marv

[EdwardSnowden]

Marvin, thank you so much for these resources! I'm going to look into them now.