[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

Reformat contents of template

I have a template which I have created in Excell which has now been populated with information.
The template is a list of individuals with corresponding descriptions, comments, and budget figures.
There are 6 rows for each individual.
My goal is to remformat the content into one logical row of data for each individual( ID# - September Budget %) that I can then import into an access database.

For the columns "Projects" (K) and "Comments" (L) that may have 6 individual rows of data, I would like to string the comments together and Ideally have them combined into one cell.

For columns S- AE (The Forcast section of the template) I would like it to be formated in the one row as "Opex Budget %" followed the 12 figures for each month (S:6 AE:6) , then "Capex Budget %" followed by the 12 figures for each month (S:8 AE:8)

I have attached a mock version of the template with filler data for the first few resources.

Is it possible to accomplish this through using some VBA code to reformat the template?

If the hidden columns M-Q create any difficulty they can be deleted.

Thank you.




Budget-Template-With-Input-2.xls
0
jtr209
Asked:
jtr209
  • 2
  • 2
1 Solution
 
Davy2270Commented:
This can be accomplished without VBA.

Join all cells into one cell with the & sign:
example, type this into cell AF5     =K5&" / "&K6&" / "&K7&" / "&K8&" / "&K9&" / "&K10
I have added a space + slash + space for ease of reading in the combined cell.
It is not fully clear to me what cells you want to combine in what way, but anyway, you sure get the hang of this and can adjust the formula to your likes.
Complete all desired combined formulas on row 5.

Then apply a filter from column C to the last column containing your selfmade formula.
Filter on column C - only the non blanks.
Then copy your selfmade formulas all the way to the bottom of your list.

When your template has been filled with data, you can use the filter once again, and copy the results as values to another sheet. From that sheet you can easily import to an access database.

However, if you're a bit familiar with access you should be gathering this data in access straight away. That's why access is there for ;-)

Davy
0
 
jtr209Author Commented:
Thanks for the response.

The join cells with the & sign works for me for the two columns "K" and "L" perfectly.

However the second portion of the template the "FY 12 Forcast" section I dont want to combine the cells, rather I want to have the "Opex Budget" row  S:6 -  AE:6 move up to row 5
and also the "Capex Budget" row S:8 - AE:8 also move up to row 5 following the "Opex Budget" row
The goal being to have everything in one row but in individual cells.

Really appreciate the help.

Thanks
0
 
Davy2270Commented:
Why don't you use linked cells?
cell AH5: =T6
cell AI5:  =U6

See attach for example.
But again, in my opinion you should use an Access database for these type of excercises.
Excel is a calculator and should be used as such. Access is a data administrator.

Budget-Template-With-Input-2-1-.xls
0
 
jtr209Author Commented:
Ok thanks, that is kind of what I was just figuring out.

The template is already completely filled out - I just sent a mock copy.
It was a coloaborative effort and using access for the data entry was not really an option.

Thanks also for he detailed example.


0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now