Excel - Data input

[arvarr]

Hi
Trying to build a model but having formulas across many rows and columns seemed to render the file size really huge.
Sheet 1 - Each column represents the day and extends to 2 years.
Sheet 2 - Input data for expenses (various category of expenses that I will determine/make an assumption which day it is meant to be take place)
Example of data on Sheet 2:
- Commissions payment - Paid every Tuesday
- Rent - Paid last day of the month
- Creditors - Paid every Monday

Is there any way that I can transpose the values from Sheet 2 to Sheet 1 without using formulas?

Thanks,
arv

 

[Andrzejek]

VBA...?

>Each column represents the day and extends to 2 years
That would be 365 x 2 = 730 columns of data!

You know your data and needs better, but looks to me a better approach would be to have a column with the dates going down the rows instead.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[arvarr]

Hi Andrzejek
I was comtemplating to convert to rows however, there is a lot of historical data associated hence keeping the format in columns.
Sample code that you can provide?

Thanks,
arv

 

[dhookom]

Are all entries recurring? I'm a database guy and wondering if this could be a rules type setup with you storing the recurring information rather than the single events. I'm not sure how that would look but it might be an interesting project. I would do this in some database but that might not be an option for you.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SkipVought]

having formulas across many rows and columns seemed to render the file size really huge.

Have you considered a Pivot Table?

How many DIFFERENT formulas do you have?

And it would also be helpful to have a representative sample of each of your tables to demonstrate what transposition needs to occur.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

>Sample code that you can provide?
Sure, but we don't know what your data looks like, and how you would like to "transpose the values from Sheet 2 to Sheet 1".
In other words:
1. This is what I have now
2. This is what I would like to have...
3. ...based on these rules

And I am with Duane - maybe Excel is not the best choice for your needs. Data base may be a better tool.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson