Excel Question 1

[GrnEyedLdy]

What I am trying to do, (with limited success so far)...

I want to copy certain values from the MASTER worksheet to the BH worksheet in the following sequence;

MASTER BH
A2 A1
A6 A2
A10 A3
A14 A4
A18 A5
A22 A6
A26 A7

ETC.,


Master A2 to BH A1. Master A6 to BH A2. Master A10 to BH A3. Master A14 to BH A4 (I need cell/row increments of 4 coming from the Master to go to cell/row increments of 1 in the BH worksheet).

1. I go to the BH sheet, click in cell A1 and type = then I go to master and click cell A2 - enter (works fine)
2. Go back to BH sheet, click in cell A2 and type = Then I go to master and click cell A6 - enter) (works fine)
3. Go back to BH sheet, click in cell A3 and type = Then I go to master and click cell A10 - enter)(works fine)
4. Go back to BH sheet, click in cell A4 and type = Then I go to master and click cell A14 - enter)(works fine)


So, it seems like I have established a pattern of every 4th cell from the A column in MASTER will become the contents of the A column in the BH sheet in increments of 1. I then try to drag the formula down to populate the rest of the cells in the A column. But, it is not working.

What seems to happen is that the first cell that I drag the formula into, the number off and then the rest going down will increment correctly by 4 from that number.

I know I must be missing something...any help will be GREATLY appreciated!

I would continue doing this manually, however the MASTER has about 3000 rows and of course this is impractical.

Thanks!

Patty

 

[SkipVought]

Hi,

Create a HELPER column with...
[tt]
=MOD(ROW(),4)
[/tt]
this will result in rows numbered from 0 to 3

Turn on the AutoFilter and select the number you want from the helper column.

Copy the DATA RANGE in the DATA column

Edit/Paste Special - VALUES in the desired destination.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[sawedoff]

After doing 2 or more, select at least 2 of the formulas and copy those down. It's easiest to use the fill handle (little black + at the bottom of the selected area. THen they should copy in intervals matching the two or more that are selected.

Sawedoff

 

[DaleWatson123321]

Hi Patty,

I believe what you've described is a perfect example of where the use of Excel's "Advanced Filter" would be the best solution.

I'll describe the steps for setting up a basic example, which you might want to follow and set up an "independent" file FIRST - to see how it works - and then apply this knowledge to your current file.

1) Change the Sheet Tab names:
- Sheet1 to "Database"
- Sheet2 to "Extraction"
- Sheet3 to "Criteria"

2) On the Database sheet, you need to have "field names" on the first row of your data. Therefore, in cell A1, enter: "colm1", in B1, enter "colm2" (don't enter the "")

3) Enter data into column A - For this example use a simple method such as: enter "1" in A2, "2" in A3. Then highlight A2:A3, click-and-drag these two cells down for a couple hundred rows or thereabouts.

4) In Column B, place an identifier - example "x" opposite those numbers you want to have extacted to the "Extraction" sheet.

(In your "real" file, you probably already have an existing column or columns that you could use for identifying those records you want to extract)

5) Assign the Range Name "data" to the block of data. To do this, you could simply:

a) Highlight the entire columns A & B - i.e. Click on the "A", hold down <Shift>, then click on the "B".

b) Hold down the <Ctrl> key, and hit <F3>. This brings up the "Define Name" window.

c) Type the word "data", and then hit <Enter>.

6) On the "Extraction" sheet, in cell A1, enter: "colm1".

7) Assign the Range Name "ext" to A1, using the same steps described in 5). (In other examples/situations, the number of columns/fields in the Extraction sheet will be greater than 1 column. In such cases, you need to assign the range name to all the fields. You only need to assign the name to the top row (the one containing the field names). Be aware, though that assigning the name to only the top row will mean that any data directly below the fields will be over-written each time you do an extraction. Therefore don't place any data you want to keep in this area immediately below the extraction fields.

8) On the "Criteria" sheet, I normally reserve the first column for entering a label - to identify the range name I create for a variety of different criteria I normally create. In this example, you'll initially only have 1 criteria, but I suggest you should adopt my method.

9) In A4, enter the label: "crit".

10) In B4, enter the column name: "colm2"

11) In B5, enter "x" - this being the same identifier you used on the Database sheet to identify those records you want to extract.

12) Highlight B4:B5, and assign the range name "crit".

13) Copy/Paste the following VBA code into a Module.

Sub extract_data()
    Range("data").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:="crit", _
    CopyToRange:=Range("ext"), _
    Unique:=False
End Sub

14) For activation of the code, you could place a "button" from the "Forms" toolbar on the "Database" or "Extraction" sheet (or both). Then right-click on the button, choose "Assign Macro", and assign "extract_data".

I hope this helps. In my opinion, the so called "Advanced Filter" has FAR MORE advantages than are made known in Excel's Help and/or Help books.

Unfortunately there isn't sufficient room in Tek-Tips to describe ALL of the "tricks and techniques" that make using this POWERFUL component of Excel easier. But trust me, the more you experiment with this feature, the more you'll appreciate and utilize its tremendous power.

Here's one example of an important technique regarding the creation of more complex criteria.

First, you can refer to as many field names as you want or need to, in order to extract the data you require. You can also refer to field names by using them (the field names) in one formula. The formula can be as complex as you require - i.e. using compound conditions. NOTE: IF you do use a formula, it must be on the second row - BUT do NOT place a field name above the formula. Also, when you enter the formula, the formula will show "#NAME?" - which is normal in this situation. Using the "x" example, the formula would be: =colm2="x".

Also, be aware that the same criteria can be used with Excel's database functions - i.e. DSUM, DCOUNT, DCOUNTA, DAVG, etc. to generate summary results.

Date ranges can be used - i.e. where you specify criteria from one date to another - in addition to other criteria. For example, you could extract (and/or provide a summary) of all monthly activity for any given product, employee, etc. With proper setup, all the user would do, is pick from a dropdown list and the data will be automatically extracted / or summarized.

If you run into a hurdle, please advise and I'll step you through it (or over it ;-)). Or there will be others able to provide assistance if I'm unavailable at the time.

Regards, Dale Watson

 

[GrnEyedLdy]

Wow! Thanks to everyone for the replies! I will give it a try, (after digesting it all)!

You all are super!!!!!


Patty

 

[byundt]

Patty,
A formula will link the two worksheets and automatically update if the values in MASTER worksheet are changed. Consider copying one down like:
=INDEX(MASTER!A:A,ROW()*4-2)

The -2 in this formula is used to adjust the starting point. ROW() returns the row number of the cell containing the formula. Since you want MASTER!A2 returned to cell A1, you need to subtract 2 from 4 * row number

If you have data in column B, you can copy the formula down very quickly using this trick:
1) Select cell A1
2) Double-click the little black square at lower right of the selection marquee. This will copy the formula down until the first break (blank cell) in column B.

Brad