compiling data from worksheets

Tom F

Active Member
Joined
Oct 19, 2002
Messages
263
Suppose there is a worksheet as depicted below.


Directions: Place an x in the cell to indicate Mark x for yes. Leave blank for no.

x 1. Age 10 - 20

2. Age 21 – 30

3. Age 31 – 40

x 4. male

5. female


The worksheets are saved as files in a folder.
How would you compile the data and print the results similar to the one below?



Directions: Place an x in the cell to indicate Mark x for yes. Leave blank for no.

12 1. Age 10 - 20

6 2. Age 21 – 30

4 3. Age 31 – 40

10 4. male

12 5. female

22 Total worksheets


Many thanks,
Tom F
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Tom - I am not too hot on code for cycling through files in folders but I have seen people using similar code - so use the Search Function for that.

The approach I would take would be to have a loop that opens the files one by one and then checks the cells in the open file for x's and if an x is found it adds 1 to the value in your "consolidating" file (say MASTER.xls) - if the files are not identical that's ok - you would just need to amend the code where it's pasting the additional 1 to the value etc

So say

B1 = 1. Age 10 - 20
B2 = 2. Age 21 – 30
B3 = 3. Age 31 – 40
B4 = 4. male
B5 = 5. female

and obviously A1,A2,A3 = the count.

Sub COUNTX()

'here you want to do the loop through the files in the folder - so one file is now open and is active (that's important)...

IF Range("B1") = "x" then Windows("MASTER.xls").activate
NEWTOTAL = Sheets("sheet1").range("a1") + 1
Range("a1") = NEWTOTAL
Windows("...YOUR OPEN FILE IN THE LOOP.xls").ACTIVATE
END IF

IF Range("B2") = "x" then Windows("MASTER.xls").activate
NEWTOTAL = Sheets("sheet1").range("A2") + 1
Range("A2") = NEWTOTAL
Windows("...YOUR OPEN FILE IN THE LOOP.xls").ACTIVATE
END IF

IF Range("B3") = "x" then Windows("MASTER.xls").activate
NEWTOTAL = Sheets("sheet1").range("A3") + 1
Range("A3") = NEWTOTAL
Windows("...YOUR OPEN FILE IN THE LOOP.xls").ACTIVATE
END IF


IF Range("B4") = "x" then Windows("MASTER.xls").activate
NEWTOTAL = Sheets("sheet1").range("A4") + 1
Range("A4") = NEWTOTAL
Windows("...YOUR OPEN FILE IN THE LOOP.xls").ACTIVATE
END IF


IF Range("B5") = "x" then Windows("MASTER.xls").activate
NEWTOTAL = Sheets("sheet1").range("A5") + 1
Range("A5") = NEWTOTAL
Windows("...YOUR OPEN FILE IN THE LOOP.xls").ACTIVATE
END IF

'then you want to close the file from the loop and then loop to the next file...

End Sub

_________________
LASW10
This message was edited by lasw10 on 2002-10-25 08:18
 

Tom F

Active Member
Joined
Oct 19, 2002
Messages
263
There is a lot to work on and now there is a place to start.

THANKS !!!
 

Forum statistics

Threads
1,144,148
Messages
5,722,791
Members
422,458
Latest member
Muirzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top