Macro or Look-up?
in [General]
|
Prev: How to keep the print output colour setting for the same file?
Next: File Open: No Preview Available...why please?
From: Tracy on 4 Feb 2010 21:04 Hello I am trying to update a worksheet to make it more efficient and easier to use. I would like one point of data entry for the final user, 'hide' the main formula worksheet, and then just have the printable, user-friendly formatted report sheet. I know a little about macros (but not so much in relation to VBA) and a little about other excel formulas (v-look up etc), but am not sure which way to go to solve this issue. Your help would be appreciated :o) The spreadsheet that I have contains a worksheet that lists data manually entered (from a range of different sources and computer software system reports unfortunately) in columns, under date headings. I have amended this worksheet to contain all the data required and perform some basic sum calculations for sub-categories. This all works fine – let's call this worksheet “Raw Data”. Q 1) Then…there is another worksheet (let's call this “Report Data”) that will have columns, again under date headings, that will need to pull some information from the matching date column in worksheet “Raw Data” and then have some further calculations preformed on that data. How do I ask/tell excel to pull the data from the matching date column in “Raw Data” into “Report Data”? Preferably in one go/step. (Without having to have write or act out “=RawData!A2” in ReportDataB2 each week and for every cell?) Column to column. Q 2) Finally…in another worksheet (called “Weekly Report <date>”), by simply entering the date wanted, how do I pull the final calculated figures into the printable, user-friendly formatted report sheet? Column to specific cells through the worksheet. Thank you very much and sorry for writing a novel of a question! -- Regards, Tracy
From: Darren Miller on 4 Feb 2010 21:09
Perhaps if you post a sample of the file you're referring to this may help to understand your request (not even sure you can do this here). However, it seems to me the best solution for you might be pivottables. Use a dynamically adjusting range name to name the source data and then do the reports and analysis you need via a pivottable or multiple pivottables. --- frmsrcurl: http://msgroups.net/microsoft.public.excel.misc/Macro-or-Look-up |