• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to update current table with new data from database

S

Sheela

New Member
Hello,

I extract data into excel from a database. I insert rows in between the extracted rows (Sheet1), and then transpose it to Sheet2 using Index formula. I am getting "0" in the blank columns in Sheet2. (Any way to get rid of this)?
In Sheet2, I am performing a calculation in every other column, row 3 (B3=A3*10). Please see highlighted cells in Sheet2.
How do I automate this calculation to be performed in every other column (row 3) in Sheet2? Now I am copying and pasting this formula in the columns with "0".
When I bring in the excel file from the database, will a macro help populate the cells in Sheet1 where currently cells are blank (highlighted yellow).
This will then automate entries in both Sheet2 and Sheet3.

Really appreciate your help, since I do not have a background in writing codes.
This forum is awesome. I have had my earlier question answered. Thanks so much.
Regards,
Sheela
 

Attachments

  • File-Posted on Chandoo-July 20-2015.xlsx
    11.2 KB · Views: 1
Hi Sheela,

I think some of the problems are coming from you inserting rows in original raw data and then trying to manipulate things. It is 99% of the time easier to leave raw data as a single group, and manipulate via formulas.

Can you post a new example, showing what the exact raw data layout starts off as (before you do anything to it), and then what layout (w/ formulas) you want it changed into?
 
Hi Luke,
I have posted the file and added comments in the worksheets where I am requesting help with either the formula or the macro.
Thanks again for all your help.
Sheela
 

Attachments

  • File-RePosted on Chandoo-July 20-2015.xlsx
    12.2 KB · Views: 0
Hi:

Why so much of transportation problem , Can't you create a simple pivot to get what you want. Find the attached.

Thanks
 

Attachments

  • File-RePosted on Chandoo-July 20-2015.xlsx
    16.3 KB · Views: 3
I would agree with Nebu, I think using a PivotTable would be the fastest/easiest. I'm still not sure what the final layout needs to look like...do all your values need to be multiplied by 10?
 
Thanks for the suggestion. I can use the pivot table if it is to multiply by 10.
I do not always need to multiply by 10. Sometimes I need to use the data as is but be able to extract into different worksheets for further analysis. Maybe my only option is to manually enter new data from the database. at every data cut. I was not sure whether a macro would do this job, probably not..
Thanks to both Luke and Nebu.
Regards,
Sheela
 
Hi:

In that case apply a formula in your base data and pull a pivot form this data. It will always save you from unnecessary copying and pasting and will lean your process with a better turnaround time.

Thanks
 
Well, that depends on your business logic. You will be knowing better than anyone when to multiply the values by 10 and when not, based on this you can decide upon a formula.

Thanks
 
You must log in or register to reply here.
Back
Top