how to reference different workbooks/sheets in formula?

i have 30 different workbooks,
each workbook has >20 individual sheets.
i have a list with two columns, one is the name of the 
workbooks, and one is the name of each sheets.
here is what i want to do.
i want to do a count on 'column z' on every sheets.
(column z on every sheets has n number of data, i want to 
count how many.)

ie,

column a  column b     column c
(sheets)  (workbooks)  (number of data point)
--------  -----------  ----------------------
g-1       a.xls        =count('[a.xls]g-1'!$z:$z)
g-2       a.xls        =count('[a.xls]g-2'!$z:$z)
g-3       a.xls        =count('[a.xls]g-3'!$z:$z)
g-4       b.xls        =count('[b.xls]g-4'!$z:$z)
g-5       b.xls        =count('[b.xls]g-5'!$z:$z)
..
..
..

since, i have 30 workworks and each has more than 20 
sheets.
the above list will have > 600 rows.
is there a easy way to put a formula in 'column c' above,
so that it will use 'column a' and 'column b' as references
and i don't have to manually type in that formula 
in 'column c' >600 times.

thx in advance.

0
anonymous (74722)
4/25/2004 8:51:30 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
388 Views

Similar Articles

[PageSpeed] 23

First, if those 30 different workbooks are closed, you're going to need the path
of the workbook in your formula.  And it becomes a tiny bit more than just
creating the formula:

Kind of:
=COUNT('C:\My Documents\excel\[a.xls]g-1'!$Z:$Z)

I could build a string that looked like that formula with this:

="=count('c:\my documents\excel\["&B1&"]"&A1&"'!$z:$z)"

You could put the path in the formula or refer to in in another cell--say D1
contained:
c:\my documents\excel, then my formula that builds the string is:

="=count('"&$D$1&"\[" &B1&"]"&A1&"'!$z:$z)"
(watchout for the trailing backslash)

Now after you've verified that the formula is ok, you can select that range of
formulas and convert them to values:  Edit|copy, Edit|paste special|values

Now, you have strings that look exactly like your formulas, but they're not
formulas, yet!

Select that range and 
edit|replace
= with =
This'll make excel reevaluate the strings as formulas.

I'd do it with a couple of cells first.  If you screw up, you might be pointing
at a worksheet/workbook that doesn't exist and you'll be prompted to fix it.  If
you did all 600 at once (incorrectly), it'll get really boring dismissing those
dialogs.





Steve wrote:
> 
> i have 30 different workbooks,
> each workbook has >20 individual sheets.
> i have a list with two columns, one is the name of the
> workbooks, and one is the name of each sheets.
> here is what i want to do.
> i want to do a count on 'column z' on every sheets.
> (column z on every sheets has n number of data, i want to
> count how many.)
> 
> ie,
> 
> column a  column b     column c
> (sheets)  (workbooks)  (number of data point)
> --------  -----------  ----------------------
> g-1       a.xls        =count('[a.xls]g-1'!$z:$z)
> g-2       a.xls        =count('[a.xls]g-2'!$z:$z)
> g-3       a.xls        =count('[a.xls]g-3'!$z:$z)
> g-4       b.xls        =count('[b.xls]g-4'!$z:$z)
> g-5       b.xls        =count('[b.xls]g-5'!$z:$z)
> .
> .
> .
> 
> since, i have 30 workworks and each has more than 20
> sheets.
> the above list will have > 600 rows.
> is there a easy way to put a formula in 'column c' above,
> so that it will use 'column a' and 'column b' as references
> and i don't have to manually type in that formula
> in 'column c' >600 times.
> 
> thx in advance.

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
4/25/2004 11:20:13 PM
Reply:

Similar Artilces:

formula help #36
I need a formula for cell A1 to show what is in cell B1 when cell D1 has Y and and shw what is in cell C1 when cell D1 has N. Thanks to all Assuming it's either Y or N: =if(D1=Y,B1,C1) Ross "Bob Bob" <NOeMAILl@mail.com> wrote in message news:jJo3j.64715$cD.27626@pd7urf2no... >I need a formula for cell A1 to show what is in cell B1 when cell D1 has Y > and and shw what is in cell C1 when cell D1 has N. > > Thanks to all > > Ross has an answer but we need quotes: =if(D1="Y",B1,C1) If Y and N are not the only possibilities, then =if(D1=&q...

2 exchange servers/2 different sites/1 domain
I have 2 sites, NY/MN, both need to use the same domain name for mail (pm.com), both will be in the same Active Directory forest, I want to have an Exchange server in both locations, the 2 sites are connected via VPN, the mx record for mail going to user@pm.com gets routed into the NY Exchange server, my question is, knowing they will each have a seperate server, running seperate databases, how will mail going to MNuser@pm.com get delivered to the mailboxes in MN? This is possible isn't it? Thanks-Dan Fox If both servers are in the same Exchange site, then the mail that is addre...

Minutes and Seconds Formula
I'm developing a workout programs for runners. I need a spreadsheet that will allow a user to enter min/seconds that is their target for running a mile. Then I need a formula that will provide intervals at every 16th of the mile of what there time should be. Any help. I thought i knew how to format to show min/sec but i'm having all kinds of problems THANKS! Put your target time in A20, and in B20 add =$A$20*COLUMN(A20)/16, then copy across to Q209. I input a target time of 4:32, and got values of 00:17, 00:34,00:52, etc. <vbg> -- HTH RP (remove nothere from the em...

How to open a different form view
How to open a different form view in a formview based MDI Application. thanks ...

How do you convert a formula cell to a constant cell
I use a formula to calculate the value, such as time difference, and want to save the value not the formula. The value (constant) will be used later to adjust both time cells used as arguments in the original formula--thus the need to preserve the value computed rather than the formula. After calculation, copy the cell with the formula then Edit - Paste Special - Values on the same cell. HTH "JQLogan" wrote: > I use a formula to calculate the value, such as time difference, and want to > save the value not the formula. The value (constant) will be used later to > ...

vlookup formula question #2
For the formula below, =IF(ISNA(VLOOKUP(A1,Sheet2!A:A,1,FALSE)),"no","yes") What is "ISNA" and what does each value separated with comma represent? Thanks in advance! -- Message posted via http://www.officekb.com Hi Janice not meaning to be unhelpful here, but you should check Help out for a good explaination of both of these questions. Depending on your version - ISNA is probably listed under a page called IS FUNCTIONS and VLOOKUP should come up if you type it into the paperclip (office assistant) or the ask a question box. Alternatively using the Pas...

compare information from two spreadsheets or excel sheets
Don't know if any one can help, I am trying to find an easy way to compare data from one spread sheet to another on Excel 2000. Every week I create a spreadsheet and need to find out if any further items added on it. example old spreadsheet would be called Andrews 19.04.2010 with 3 sheets 4 columns on each. The new one would be Andrews 26.04.2010 same set up again but possibley new items on the Andrews 26.04.2010 Add a fifth corresponding column on the NEW sheet and insert the following formula in the first cell: =SUMPRODUCT((OldSheet!A:A=A1)*(OldSheet!B:B=B1)*(OldShee...

Workbook default for borders
I have a number of workbooks which began their lives in Excel and the cell border setting is just fine. Nice dark lines, etc. Howver, I have other Excel files that were created from Lotus 1-2-3 files. The cell border settings in these files is not the same as the other files. The cell border line is gray. When I set a cell with a border and then click on Format, cell, border and then click on Choose the black color, the cell in question gets the desired format. But, no where can I find a way to set the default cell border this way for the whole workbook. Isn't there a menu op...

Print selected worksheets in multiple workbooks?
We would like to print two specific worksheets that exist in multiple workbooks at the same time. In other words, we would like to be able to select all workbook files in a directory and send to the printer to have the same two pages from each workbook print, not the entire workbook. We would like to avoid having to select each file individually to send two sheets to the printer, and then close and go on to the next file. Is this possible, and if so, how do we accomplish this? Thanks for any direction you can provide. I wasn't sure what all you needed, so the code below will ...

Electronic Time Sheets
Hello, I'm in a bit of a pinch. I've been working for a couple of days with this company, (internship) and they have asked me to design a way to allow employees to enter their worked-hours time sheets electronically. Currently they just write them on a piece of paper and hand them in. They are using Great Plains 8. I asked for the manuals and they said they never bought them because they were too expensive. (I didn't even know you had to buy the manual.) So basically, I don't know how to work this out. Would it be possible for me to create an ASP.NET application that genera...

External Referance over Network
I need to take a total from one workbook and link it to another book on the same network. The 2nd book will be edited by other users and i need to be able to have their amounts added to my total from the first workbook. I've tried paste link but all I get is an image that shows the value of the cell in the linked workbook. This does not allow me to use it in a formula. How can I reference the total in the first workbook in a way that will allow me to use it in a formula in the second workbook? Add the reference to the 2nd Workbook and then Cut and move the reference to ...

Utilizing a Cell even when a Formula is present
Hi, I'm completely new to this board and was wondering if I could possibly get some help. I'm trying to utilize a cell in which has a formula in it, however everytime I insert a value to utilize the formula, it of course.., gets cleared out. Is there anyway to maintain the formula inside the cell, then be able to just type a basic number into the cell and have the formula calculate. The formula I have now is =IF(A2=0, B2=0,""), I need when A2 is empty, for B2 to empty itself out. However, I need when A2 has something in it, for C2 to subtract the value from B2, but I can'...

Hiding Formula in cells
Hello everyone, I used to know this, but ..... old age... There is a way to hide formulae in cells, but the sheet needed to be protected I believe. Anyone knows how this was done? Thanks Hi Thrava, Format | Cells | Select Protection tab | Check "Hidden" Then protect the worksheet. --- Regards, Norman "thrava" <anonymous@discussions.microsoft.com> wrote in message news:15de01c4e884$17eea760$a501280a@phx.gbl... > Hello everyone, > > I used to know this, but ..... old age... > > There is a way to hide formulae in cells, but the sheet > nee...

Timesheet formula
I'm wondering if anyone can give me a hand with writing a formula to calculate hours on a timesheet? I need the formula to calculate the number of hours of ordinary time, where ordinary time is paid for the first 4 hours of work or until 12 noon (ie if I have worked 11am - 1 pm there would be only 1 hour of ordinary time, 7am-3pm is 4 hours etc). I am not using 24 hour clock or am/pm but hoping that the formula will recognise whether the number written is in the morning or afternoon depending on whether start time is bigger than finish time. -- kddd ------------------------------------...

Cell formatting or formulas
How do I make 1.50 appear as 1.30 in a given cell? I have a cell with times such as 1.00 minute, 1.50 minutes, 2.50 minutes, 3.00 minutes, etc. I want all the x .50 digits to appear as x.30 digits (30 seconds to a half minute) Divide by 1440 and format as mm:ss -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "George" <George@discussions.microsoft.com> wrote in message news:0E77414D-FED2-4BB7-BA36-E226D3AE4D05@microsoft.com... > How do I make 1.50 appear as 1.30 in a given cell? I have a c...

Defaul start up sheet on a workbook
Does anyone know how to make a workbook open in the same or default sheet every time is opened? I have a workbook with 15 sheets. How can I make excel to open it in the first sheet everytime I open this workbook?.... Excel always remembers the last active sheet that the workbook had open at the time it was save and close.. Anybody has any suggestions? Thank you in advance Hi put the following code in your workbook module: Private Sub Workbook_Open() Worksheets("your_sheet").Activate End Sub and replace the sheetname Frank GeoN wrote: > Does anyone know how to mak...

Updating different worbooks to workbook
Is there a simple way update certain parts of a workbook from other workbooks? It depends. It may be that you could use a custom macro or simply copy and paste. The best way would be determined by the specifics of what the differences are and what changes need to be made, and what you mean by update - combine? write-over? change the formulas to match? HTH, Bernie MS Excel MVP "aggi" <anonymous@discussions.microsoft.com> wrote in message news:2cd6f01c46a77$05e6c9c0$a601280a@phx.gbl... > Is there a simple way update certain parts of a workbook > from other workbooks? ...

If Formula displays error message
Hi This was the formula IF(J16=J19,"RIGHT","WRONG") and the column j16=j19 but the messgae displayed was wrong instead of right when i pasted the same formula in other cells and tested and it works. Is it because that the cell J16 & j19 was with formula which linked to other sheets. Please show the light Thanks -- Life isa journey not a destination Hi, It may be that the values in J16 & J19 'look' the same because of the way they are displayed but in fact may be slightly different. Maybe something like this =IF(ROUND(J16,2)=ROUND(J19...

simple formula I'm sure using offset? or index?
In cell b1 i have a named range "month". I want to be able to change the value of month and have the cooresponding values appear in b2-b6. What I'm after here is the formula I put in b2-b6. Thanks. a b c d e f 1 Product jan jan feb mar apr 2 prod-a 100 100 200 300 400 3 prod-b 125 125 225 350 450 4 prod-c 175 175 210 325 400 5 prod-d 190 190 220 350 325 6 prod-e 150 150 235 375 390 Put this in B2: =3DINDEX(C2:F2,MATCH(B$1,C$1:F$1,0)) then copy down. Hope this helps. Pete On Dec 3, 4:54=A0pm, Steve <St...@discussions.microsoft.com> wrote...

existing workbook,want to create chart-wizard will not open
New to computers-excel,Istill believe the transitor radio just came out.Anyway, I have an exisiting spreadsheet(workbook) that i need to create chart(s) for.January thru Dec for 06 across the top of the page. 6 catagories down the left column. The chart wizard will not allow me to pull it up when I have my spreadsheet displayed. How do i create if I can't the wizard to open? ...

How to quickly remove ALL formatting on a sheet
I usually just do a control+A and hit delete but this does not remove any formatting (font, background, borders, alignment). Is there any quick way to do that (short of deleting the sheet) Any help here will be appreciated. Thanks in advance Dave OK - I found Edit/Clear/All which seems to do what I want EXCEPT Put the row/column sizes back to standard Is that doable? dave "Dave" <dave@accessdatapros> wrote in message news:BAAFF047-6808-485D-9598-454FF24B0E61@microsoft.com... > I usually just do a control+A and hit delete but this does not remove any > formatt...

How do I get totals from several different worksheets on to one?
I have made my own statistic worksheet for my husband's baseball team. I would like to enter stats from each game & then get season totals on one sheet without having to physically add each stat up for each player. I have tried to do the consolidation thing but am at a loss. I have formulas to figure specific stats. I have multiple rows, with multiple columns on multiple sheets. Any help would be useful. Ex: Player A stats include Hits, At bats, Runs, Fielding Errors, Left on Base, Hit by pitch, Sacrafice, ect. Some of these stats are then used in formulas to gener...

Reference Footnotes
How do I enter a footnote at the bottom of a page? The footnotes are important--it's for a college internship assignment. Thanks! -- kasper kasper <kasper@discussions.microsoft.com> was very recently heard to utter: > How do I enter a footnote at the bottom of a page? The footnotes are > important--it's for a college internship assignment. Thanks! You have to create footnotes manually. -- Ed Bennett - MVP Microsoft Publisher ...

Count if formula with two variables
Hello, Am trying to figure out a formula in a cell to analyse data in tw columns. One column will have a referance and the frequency of th corresspomnding data in the other column need to be counted -- shrikan ----------------------------------------------------------------------- shrikant's Profile: http://www.officehelp.in/member.php?userid=474 View this thread: http://www.officehelp.in/showthread.php?t=123405 Posted from - http://www.officehelp.i attach an example. "shrikant" wrote: > > Hello, > > Am trying to figure out a formula in a cell to analyse dat...

Content in user's calendar different than other calendars
Good morning from Wisconsin, In our network, one user's calendar is not seeing the same events/ notes as everyone else. The updates that he puts in don't get pushed out to the shared calendars, and he can see some of the updates that other users put into the shared calendars but not all updates. It appears as if his calendars won't update beyond a date somewhere in the neighborhood of about three weeks ago. However, he can send/ receive email from the server just fine. The clients are all running Outlook 2003 and log into Active Directory on a Windows SBS 2003 box running Exch...