Biff

thank you for taking the time to write out the explanation of the

formula, some of it made sense to my limited knowledge.

=VLOOKUP(C10,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10 )0,0))&"'!A:Z"),15,0)

I input the above formula as an array and excel offered to correct it

as below

=VLOOKUP(C10,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10 )*0,0))&"'!A:Z"),15,0)

adding in the * near the add. Having accepted this the formula returns

#N/A

even though I have added data into the worksheets and asked to look up

something I know is there.

I know #N/A means that the formula cannot find the referenced data,

does this mean that I have entered the names of the monthly worksheets

wrong

(I tried entering them, by typing, as 'Jan'!, 'Feb'!, 'Mar'! etc). Is

there another way of entering the names of the worksheets?

Sorry about this but it is so frustrating as it appears so close to

completion

thanks again for any help

stm

Biff Wrote:

> Hi!

> -

> I cant follow the formula and was wondering if you could explain each

> bit

> this appears far more advanced than anything I've done before.-

>

> Ok, if I do a good job of explaining what the formula does after you

> read it

> you'll be thinking to yourself, Man, that's really simple after all!

>

> I'll change the formula a little using the info and references you

> provided

> in your response.

>

> =VLOOKUP(C10,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10 )0,0))&"'!A:Z"),15,0)

>

> Since you have 12 sheets for monthly data I'll take a wild guess and

> assume

> that the names a

>

> Jan

> Feb

> Mar

> Apr

> May

> ...

> ...

> Dec

>

> I think Domenic (knows his stuff!) probably figured out why the

> formula

> didn't work initally. Since one of your goals was to reduce the number

> of

> sheets in the workbook why create a sheet just to list the sheet names

> to be

> used for this formula? Just put the list somewhere on your summary

> sheet.

> You can put the list anywhere you want if you don't want it to be

> visible on

> screen.

>

> OK, the sheet names are listed in the range H1:H12 (on the summary

> sheet).

> Select that range, H1:H12. Now, click in the Name box and type in the

> name

> for that range, SheetList. The Name box is that little space on the far

> left

> hand side of the formula bar. It shows you what cell is currently

> selected.

>

> Now, since you want to do a lookup on several sheets (12) you would

> think

> that that is what the formula is doing. Sort of a looping lookup. But

> that's

> NOT what the formula is doing. It's doing a single lookup but part of

> the

> formula IS "looping" looking for a condition associated with the

> lookup

> value.

>

> If you only had 2 or 3 sheets then you could use a "looping" Vlookup

> formula. That is, a Vlookup formula that searches one sheet then the

> next,

> then the next. That formula might look something like this:

>

> =IF(NOT(ISERROR(VLOOKUP(C10,Sheet2!A:Z,15,0))),VLOOKUP(C10,Sheet2!A:Z,15,0),

> IF(NOT(ISERROR(VLOOKUP(C10,Sheet3!A:Z,15,0))),VLOOKUP(C10,Sheet3!A:Z,15,0),

> IF(NOT(ISERROR(VLOOKUP(C10,Sheet4!A:Z,15,0))),VLOOKUP(C10,Sheet4!A:Z,15,0),

> "")))

>

> The formula looks through sheet2, if the lookup value isn't found then

> it

> looks through Sheet3, if the lookup value isn't found then it looks

> through

> Sheet4. So it "loops" through the sheets.

>

> Since you have 12 sheets to look through this type of formula won't

> work

> because you would exceed the nested functions limit of 7. (although

> technically, you could use a concatenation technique to get around the

> nested function limit but then the formula would be REALLY long!)

>

> So, this formula:

>

> =VLOOKUP(C10,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10 )0,0))&"'!A:Z"),15,0)

>

> does a single lookup.

>

> =VLOOKUP(C10,

>

> That portion should be self explanatory. Now, we have to tell Vlookup

> where

> to look.

>

> The Indirect function is used to "build" a TEXT representation of a

> reference that can be converted into a useable reference by Vlookup.

>

> Assume the lookup value is the number 10. This portion of the formula:

>

> COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10)

>

> is "looking" on each sheet for that value. Remember now, this is an

> array

> formula. An array formula operates on each element of the array. So,

> each

> sheet is an element of the array. The formula operates on each element

> by

> doing a Countif. Here's what that would look like:

>

> COUNTIF(Jan!A2:A999,C10)0

> COUNTIF(Feb!A2:A999,C10)0

> COUNTIF(Mar!A2:A999,C10)0

> etc.

>

> What that's doing is if the lookup value 10 is found in sheet

> Jan!A2:A999

> then the logical Countif( ) 0 will return TRUE. If the lookup value is

> not

> found in Jan!A2:A999 then the logical Countif( ) 0 will return FASE.

> And

> this is performed on each element of the array.

>

> This portion:

>

> MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10)0,0))

>

> looks for a return of TRUE meaning the Countif of the lookup value is

> greater than zero. Assume the sheet Mar!A2:A999 is where the lookup

> value

> was "found". The formula would return this array based on the results

> of

> each Countif:

>

> FALSE

> FALSE

> TRUE

> FALSE

> FALSE

> FALSE

> FALSE

> FALSE

> FALSE

> FALSE

> FALSE

> FALSE

>

>

> Notice that the TRUE is in the third position. The Index function is

> used to

> reference an array of values one of which may or may not be one that we

> want

> to return in a formula. To determin which of those values we want

> returned

> we use conditions that must be met. Our conditions that need to be met

> a

>

> MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10)0,0))

>

> The array of values that we have indexed using the Index function are

> the

> sheet names:

>

> INDEX(SheetList

>

> Jan

> Feb

> Mar

> Apr

> May

> Jun

> Jul

> Aug

> Sept

> Oct

> Nov

> Dec

>

> So, this potion of the formula:

>

> INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10)0,0))

>

> looks like this:

>

> Jan FALSE

> Feb FALSE

> Mar TRUE

> Apr FALSE

> May FALSE

> Jun FALSE

> Jul FALSE

> Aug FALSE

> Sept FALSE

> Oct FALSE

> Nov FALSE

> Dec FALSE

>

> This means that the lookup value 10, is on sheet Mar.

>

> So, now the formula looks like this:

>

> =VLOOKUP(C10,INDIRECT("'Mar'!A:Z"),15,0)

>

> Indirect "converts" the TEXT string "'Mar'!A:Z" to a useable reference

> and

> we end up with:

>

> =VLOOKUP(C10,Mar!A:Z,15,0)

>

> See, it really isn't complicated at all! g

>

> I left a lot of explanation out but covered the basics of the formula.

> Hope

> you get something out of it!

>

> P.S. - the formula does not contain an error trap. That is, if the

> lookup

> value is not found the formula will return #N/A. You can suppress the

> display of #N/A if you don't want it showing up on your sheet. There

> are 2

> methods for doing this. One is building the error trap into the formula

> but

> this will make the formula twice as long. the other method is to use

> conditional formatting. If you want to do this post back and "we'll"

> fix ya

> right up!

>

> Oh, and don't ask about finding the second or third or fourth instance

> of

> the lookup value! vbg

>

> Biff

>

> "sonic-the-mouse" sonic-the-mouse.1q3ytm@news.officefrustration.com

> wrote

> in message news:sonic-the-mouse.1q3ytm@news.officefrustration.com...-

>

> Hi!

>

> This is way above anything I have done before. I entered the formula

> as

> is and remembered CTRL SHFT ENTER, came back with automatic

> correction

> of adding an * ",A1)*0,0))etc. This just returns #N/A.

>

> OK, this will work if the lookup tables on all the sheets have the

> same

> layout. -ALL WORKSHEETS FOR THE MONTHS ARE THE SAME LAYOUT

>

> Make a list of sheet names that need to be "searched". Say you put

> that

> list

> in H1:H12. Give that range a name, something like SheetList. -ENTERED

> A

> NEW WORK SHEET NAMED SHEETLIST AND TYPED IN NAMES OF WORKSHEETS INTO

> COLUMN A1:A12

>

> The lookup value is entered in A1. - IN MY WORKSHEET C10

>

> The lookup tables on all the sheets are in the range A1:C10. - IN MY

> CASE A2:Z999

>

> This example looks up the value in column A and returns the

> corresponding

> value from column 3 of the lookup table.

>

>

> =VLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:A10"),A1)0, 0))&"'!A:C"),3,0)

>

> I cant follow the formula and was wondering if you could explain each

> bit

>

> Formula goes into cell G8 which looks for the info in C10 in all the

> work books and then returns the information in the same row but in

> Column O.

>

> Sorry if I appear to be a bit thick but this appears far more

> advanced

> than anything I've done before.

>

> stm

>

>

> --

> sonic-the-mouse-

--

sonic-the-mouse

thank you for taking the time to write out the explanation of the

formula, some of it made sense to my limited knowledge.

=VLOOKUP(C10,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10 )0,0))&"'!A:Z"),15,0)

I input the above formula as an array and excel offered to correct it

as below

=VLOOKUP(C10,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10 )*0,0))&"'!A:Z"),15,0)

adding in the * near the add. Having accepted this the formula returns

#N/A

even though I have added data into the worksheets and asked to look up

something I know is there.

I know #N/A means that the formula cannot find the referenced data,

does this mean that I have entered the names of the monthly worksheets

wrong

(I tried entering them, by typing, as 'Jan'!, 'Feb'!, 'Mar'! etc). Is

there another way of entering the names of the worksheets?

Sorry about this but it is so frustrating as it appears so close to

completion

thanks again for any help

stm

Biff Wrote:

> Hi!

> -

> I cant follow the formula and was wondering if you could explain each

> bit

> this appears far more advanced than anything I've done before.-

>

> Ok, if I do a good job of explaining what the formula does after you

> read it

> you'll be thinking to yourself, Man, that's really simple after all!

>

> I'll change the formula a little using the info and references you

> provided

> in your response.

>

> =VLOOKUP(C10,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10 )0,0))&"'!A:Z"),15,0)

>

> Since you have 12 sheets for monthly data I'll take a wild guess and

> assume

> that the names a

>

> Jan

> Feb

> Mar

> Apr

> May

> ...

> ...

> Dec

>

> I think Domenic (knows his stuff!) probably figured out why the

> formula

> didn't work initally. Since one of your goals was to reduce the number

> of

> sheets in the workbook why create a sheet just to list the sheet names

> to be

> used for this formula? Just put the list somewhere on your summary

> sheet.

> You can put the list anywhere you want if you don't want it to be

> visible on

> screen.

>

> OK, the sheet names are listed in the range H1:H12 (on the summary

> sheet).

> Select that range, H1:H12. Now, click in the Name box and type in the

> name

> for that range, SheetList. The Name box is that little space on the far

> left

> hand side of the formula bar. It shows you what cell is currently

> selected.

>

> Now, since you want to do a lookup on several sheets (12) you would

> think

> that that is what the formula is doing. Sort of a looping lookup. But

> that's

> NOT what the formula is doing. It's doing a single lookup but part of

> the

> formula IS "looping" looking for a condition associated with the

> lookup

> value.

>

> If you only had 2 or 3 sheets then you could use a "looping" Vlookup

> formula. That is, a Vlookup formula that searches one sheet then the

> next,

> then the next. That formula might look something like this:

>

> =IF(NOT(ISERROR(VLOOKUP(C10,Sheet2!A:Z,15,0))),VLOOKUP(C10,Sheet2!A:Z,15,0),

> IF(NOT(ISERROR(VLOOKUP(C10,Sheet3!A:Z,15,0))),VLOOKUP(C10,Sheet3!A:Z,15,0),

> IF(NOT(ISERROR(VLOOKUP(C10,Sheet4!A:Z,15,0))),VLOOKUP(C10,Sheet4!A:Z,15,0),

> "")))

>

> The formula looks through sheet2, if the lookup value isn't found then

> it

> looks through Sheet3, if the lookup value isn't found then it looks

> through

> Sheet4. So it "loops" through the sheets.

>

> Since you have 12 sheets to look through this type of formula won't

> work

> because you would exceed the nested functions limit of 7. (although

> technically, you could use a concatenation technique to get around the

> nested function limit but then the formula would be REALLY long!)

>

> So, this formula:

>

> =VLOOKUP(C10,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10 )0,0))&"'!A:Z"),15,0)

>

> does a single lookup.

>

> =VLOOKUP(C10,

>

> That portion should be self explanatory. Now, we have to tell Vlookup

> where

> to look.

>

> The Indirect function is used to "build" a TEXT representation of a

> reference that can be converted into a useable reference by Vlookup.

>

> Assume the lookup value is the number 10. This portion of the formula:

>

> COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10)

>

> is "looking" on each sheet for that value. Remember now, this is an

> array

> formula. An array formula operates on each element of the array. So,

> each

> sheet is an element of the array. The formula operates on each element

> by

> doing a Countif. Here's what that would look like:

>

> COUNTIF(Jan!A2:A999,C10)0

> COUNTIF(Feb!A2:A999,C10)0

> COUNTIF(Mar!A2:A999,C10)0

> etc.

>

> What that's doing is if the lookup value 10 is found in sheet

> Jan!A2:A999

> then the logical Countif( ) 0 will return TRUE. If the lookup value is

> not

> found in Jan!A2:A999 then the logical Countif( ) 0 will return FASE.

> And

> this is performed on each element of the array.

>

> This portion:

>

> MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10)0,0))

>

> looks for a return of TRUE meaning the Countif of the lookup value is

> greater than zero. Assume the sheet Mar!A2:A999 is where the lookup

> value

> was "found". The formula would return this array based on the results

> of

> each Countif:

>

> FALSE

> FALSE

> TRUE

> FALSE

> FALSE

> FALSE

> FALSE

> FALSE

> FALSE

> FALSE

> FALSE

> FALSE

>

>

> Notice that the TRUE is in the third position. The Index function is

> used to

> reference an array of values one of which may or may not be one that we

> want

> to return in a formula. To determin which of those values we want

> returned

> we use conditions that must be met. Our conditions that need to be met

> a

>

> MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10)0,0))

>

> The array of values that we have indexed using the Index function are

> the

> sheet names:

>

> INDEX(SheetList

>

> Jan

> Feb

> Mar

> Apr

> May

> Jun

> Jul

> Aug

> Sept

> Oct

> Nov

> Dec

>

> So, this potion of the formula:

>

> INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10)0,0))

>

> looks like this:

>

> Jan FALSE

> Feb FALSE

> Mar TRUE

> Apr FALSE

> May FALSE

> Jun FALSE

> Jul FALSE

> Aug FALSE

> Sept FALSE

> Oct FALSE

> Nov FALSE

> Dec FALSE

>

> This means that the lookup value 10, is on sheet Mar.

>

> So, now the formula looks like this:

>

> =VLOOKUP(C10,INDIRECT("'Mar'!A:Z"),15,0)

>

> Indirect "converts" the TEXT string "'Mar'!A:Z" to a useable reference

> and

> we end up with:

>

> =VLOOKUP(C10,Mar!A:Z,15,0)

>

> See, it really isn't complicated at all! g

>

> I left a lot of explanation out but covered the basics of the formula.

> Hope

> you get something out of it!

>

> P.S. - the formula does not contain an error trap. That is, if the

> lookup

> value is not found the formula will return #N/A. You can suppress the

> display of #N/A if you don't want it showing up on your sheet. There

> are 2

> methods for doing this. One is building the error trap into the formula

> but

> this will make the formula twice as long. the other method is to use

> conditional formatting. If you want to do this post back and "we'll"

> fix ya

> right up!

>

> Oh, and don't ask about finding the second or third or fourth instance

> of

> the lookup value! vbg

>

> Biff

>

> "sonic-the-mouse" sonic-the-mouse.1q3ytm@news.officefrustration.com

> wrote

> in message news:sonic-the-mouse.1q3ytm@news.officefrustration.com...-

>

> Hi!

>

> This is way above anything I have done before. I entered the formula

> as

> is and remembered CTRL SHFT ENTER, came back with automatic

> correction

> of adding an * ",A1)*0,0))etc. This just returns #N/A.

>

> OK, this will work if the lookup tables on all the sheets have the

> same

> layout. -ALL WORKSHEETS FOR THE MONTHS ARE THE SAME LAYOUT

>

> Make a list of sheet names that need to be "searched". Say you put

> that

> list

> in H1:H12. Give that range a name, something like SheetList. -ENTERED

> A

> NEW WORK SHEET NAMED SHEETLIST AND TYPED IN NAMES OF WORKSHEETS INTO

> COLUMN A1:A12

>

> The lookup value is entered in A1. - IN MY WORKSHEET C10

>

> The lookup tables on all the sheets are in the range A1:C10. - IN MY

> CASE A2:Z999

>

> This example looks up the value in column A and returns the

> corresponding

> value from column 3 of the lookup table.

>

>

> =VLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:A10"),A1)0, 0))&"'!A:C"),3,0)

>

> I cant follow the formula and was wondering if you could explain each

> bit

>

> Formula goes into cell G8 which looks for the info in C10 in all the

> work books and then returns the information in the same row but in

> Column O.

>

> Sorry if I appear to be a bit thick but this appears far more

> advanced

> than anything I've done before.

>

> stm

>

>

> --

> sonic-the-mouse-

--

sonic-the-mouse

- Copying a formula to multiple worksheets
- Lookup Function/Formula Across Multiple Worksheets
- Complex formula over multiple worksheets
- Formula checking multiple worksheets
- Re: apply cell names to formulas in multiple worksheets
- Need Help to change formula in multiple worksheets/books
- Referencing multiple Formulas across multiple worksheets
- Apply cell names to formulas in multiple worksheets
- Formula checking multiple worksheets:streamlining a spreadsheet
- CTRL SHFT ENTER:Formula checking multiple worksheets
- Formula checking multiple worksheets:clarifies
- Place Excel file( with formulas on multiple worksheets) online
- Excel should let me refer to the same formula on multiple sheets
- Conditonal Formula checking two values
- Copy & Paste formulas to multiple Worksheets
- Formula checking multiple worksheets:logical operator
- Looking to duplicate formulas over multiple worksheets
- Summarize data from multiple worksheets with multiple criteria
- Copying relative cell references to multiple worksheets
- Formula calculation with multiple worksheets
- Complex summing of multiple worksheets
- Having a formula provide a running total from multiple worksheets
- Copying a range of data across multiple worksheets
- Formula Not Accepting Values from Multiple Worksheets

understands that you don't want to use the first worksheet formula, but the

formula on the preceding worksheet?

Example:

52 sheets (one for each week)

Cell A1 Worksheet 1 = Jan 7 (first week ending date)

Cell A1 Worksheet 2 = =+Sheet1!A1+7, giving Jan 14

If I copy Cell A1 Worksheet 2 to Cell A1 Worksheet 3, I get Jan 14; I want

Jan 21

I want Excel to consider that I'm copying a formula that references a cell

on the previous sheet, so the result should reference the previous sheet, not

the first sheet

Anyone know how to make it work?

=IF('2'!G3:G271=A3,LOOKUP(A3,'2'!G3:G271,'2'!E3:E271),0)

i have a spreadsheet that tracks documents i have sent to accounts . the workbook consists of:

sheet 1 "november"

sheet 2 "december"

sheet 3 "January"

sheet 4 "February"

sheet 5 "March"

sheet 6 "summary"

sheets 1-5 contain columns as follows:

A - Nominal Code

B - Date

C - Inv no

D - Description

E - Type

F - PO No

G - Amount

H - Date paid

In my summary sheet, i wanted to add up nominal codes against the month this i did by the following formula that works very nicely thank you

=SUMIF(November!A:A,'Contracts Summary'!B3,November!G:G)

so why am i asking for help?

well, they now tell me they want to calculate the months nominal code against the date paid, and as this may be in the next month or months (i.e. on a different worksheet) is it possible to run a formula that calculates over multiple worksheets?

so to calculate what we paid out in november i need something like:

sum if - November!A:A thru March!A:A, Summary!B2 (this is the nominal code),November!G:G thru March!G:G where November!H:H thru March!H:H is =>01/11/01 and =

previously had 28 worksheets

A sheet for each month of the year to list occurences

An audit sheet for each month and

A couple of statistc sheets.

The monthly sheets list events numerically although the numbers are not

consecutive.

I want to only have one audit worksheet which will search the monthly

sheets looking for the event that is numbered in cell C10.

The previous formula was

=if(isblank(c10),"",vlookup('Jan'!a2:t999,2,false) ).

How can I adapt this to look up all the monthly worksheets to find the

event number? There are a number of formula in the audit sheet which

will read further information in the same row. Any assistance is

greatly appreciated.

stm

--

sonic-the-mouse

the best workaround. (Find the original cell reference, e.g.,

Sheet1!$H$9, and replace [all] with the name.) You still have to

find/replace each name individually, but at least you only have to do

them once.

BBurrows Wrote:

> After many days of trying, I have finally given up and reverted to doing

> it

> all manually. I have all my data cells on different worksheets to my

> formula cells so it has meant naming each data cell then re-doing the

> formulas with the newly named data cells. With most of my formulas

> being

> at least 4-5 lines long, I am in my second week of manually converting.

> I am

> sorry to say I therefore still have no solution for anyone. If anyone

> has

> any suggestions, would all be greatly appreciated.

>

> Belinda

>

> "Mike" wrote:

> -

> Hi... I have been searching for an answer to this problem all day and

> there

> seem to be no real solution.

>

> I have a workbook with 8 worksheets that was given to me. I went

> through the

> entire workbook and named all the important cells and cell ranges with

> global

> names. Then I went to "apply" the names to cells with calculations

> where by

> the cell location would be replaced with the names.

>

> The APPLY function works just fine in replaceing cell locations with

> NAMES

> that were created on the same page. But it does not seem to work when

> trying

> to apply names to calculations that were created on a different

> worksheet

> then the named range itself was created.

>

> For example: on worksheet1 cell D7 contains =SUM(A7:C7) and is

> globally

> name MyTotal. On worksheet2, cell C4 contains =Sheet1!D7

>

> If I do Insert/Names/Apply/MyTotal on worksheet2, cell C4 I get the

> response:

> "Microsoft Excel cannot find any references to replace". And if I

> simply

> type =MyTotal in any cell on worksheet2, the proper value from

> worksheet1

> appears.

>

> Is there any way to replace these names other than manually?

>

> Thanks. Mike

>

>

>

> "Peo Sjoblom" wrote:

> -

> You have to do each sheet one by one, select the formula and do

> insertnameapply and select the name

>

> --

> Regards,

>

> Peo Sjoblom

>

>

> "BBurrows" BBurrows@discussions.microsoft.com wrote in message

> news:4EAB66AC-82FD-4E2E-9B6B-163985809C11@microsoft.com...-

> If you have already developed an excel spreadsheet with multiple

> worksheets,

> and formulas that refer to cells on these multiple worksheets, how

> do you

> change the cell references to names and make sure they are applied

> to each

> relevant worksheet and formula-

>

>

> --

--

jlefeaux

I am having trouble figuring out if i can change a formula in specific cells across multiple worksheets and possibly workbooks.

I have been using VLOOKUP on my sheets and i added a new column to a data sheet that VLOOKUP pulls data from. So now on all of my worksheets i need to expand the range by another column and expand the rows to account for future data on the sheet.

see example

=VLOOKUP(I58,'C:Documents and Settingsrstevens1Desktopfolder name[pricing list.xls]Truss Pricing'!$A$1:$B$1200,2,FALSE)

here is another wrinkle into my problem. The cell in each worksheet that this formula is in is variable based on how many rows are above it. (Some sheets have more data above these cells)

In the example above I58 will be variable on each sheet. the part of the formula i want to change is the section ($a$1:$b$1200,2,false)

I want to change it to read ($a$1:$c$12000,2,false)

Is there a macro or another function i can run to fix all these sheets?

I have ALOT of sheets to fix this formula on and i am looking for a quick way to edit them.

Thanks in advance any advice

I think I may have crossed the threshold of my own abilities by trying to link cells to formulas in other worksheets within my workbook.

Let me try to explain myself:

I have 20 to 30 worksheets within my workbook. I would have created multiple workbooks, but so much of the data is linked between the sheets and I wanted to have it all in one file location. Anyway, I have a cell with the following formula in cell A6:

=IF(AND(E40>89, E41="Go", E42>89, I40>89, I41="Go", F8=0), "X", " ")

E40 is 80 which does not meet the first criteria to yield the TRUE value of X (all other criteria are met) - yet alas, there is an X in my cell.

It gets more interesting... I intended of having this data pulled from another worksheet.

While E40 shows 80, it is actually referencing a cell from another worksheet within my workbook. E40's fx ='M4 Scores'!F12

So - we go over to Worksheet 'M4 Scores' to cell F12 and this cell is also a formula:

=IF(E12<23,"No-Go",IF(E12<30,"70",IF(E12<36,"80",IF(E12<=40,"100"," "))))

This formula is dependent on E12's value, which is 35.

All the formulas work, except the first formula I mentioned:

=IF(AND(E40>89, E41="Go", E42>89, I40>89, I41="Go", F8=0), "X", " ")

The strange thing is that if I go back to the cell that alerted me to the problem (E40), and I manually type in 80 (which it already had displayed as a result from the formula), it instantly works.

If I evaluate the formula, the evaluation tells me that E40 is True - meaning that it is over 89, when it is clearly not. It is displaying an 80.

I am extremely confused. I have had other issues like this when referencing multiple formulas across multiple cells.

Can anyone help?

Thanks

and formulas that refer to cells on these multiple worksheets, how do you

change the cell references to names and make sure they are applied to each

relevant worksheet and formula

previously had 28 worksheets

A sheet for each month of the year to list occurences

An audit sheet for each month and

A couple of statistc sheets.

The monthly sheets list events numerically although the numbers are not

consecutive.

I want to only have one audit worksheet which will search the monthly

sheets looking for the event that is numbered in cell C10.

The previous formula was

=if(isblank(c10),"",vlookup('Jan'!a2:t999,2,false)).

How can I adapt this to look up all the monthly worksheets to find the

event number? There are a number of formula in the audit sheet which

will read further information in the same row. Any assistance is

greatly appreciated.

stm

--

sonic-the-mouse

This is way above anything I have done before. I entered the formula as

is and remembered CTRL SHFT ENTER, came back with automatic correction

of adding an * ",A1)*0,0))etc. This just returns #N/A.

OK, this will work if the lookup tables on all the sheets have the

same

layout. -ALL WORKSHEETS FOR THE MONTHS ARE THE SAME LAYOUT

Make a list of sheet names that need to be "searched". Say you put that

list

in H1:H12. Give that range a name, something like SheetList. -ENTERED A

NEW WORK SHEET NAMED SHEETLIST AND TYPED IN NAMES OF WORKSHEETS INTO

COLUMN A1:A12

The lookup value is entered in A1. - IN MY WORKSHEET C10

The lookup tables on all the sheets are in the range A1:C10. - IN MY

CASE A2:Z999

This example looks up the value in column A and returns the

corresponding

value from column 3 of the lookup table.

=VLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:A10"),A1)0, 0))&"'!A:C"),3,0)

I cant follow the formula and was wondering if you could explain each

bit

Formula goes into cell G8 which looks for the info in C10 in all the

work books and then returns the information in the same row but in

Column O.

Sorry if I appear to be a bit thick but this appears far more advanced

than anything I've done before.

stm

--

sonic-the-mouse

I am trying to look up a number that could be on any one of the twelve

worksheets. I hope this clarifies what I'm looking for.

stm

Biff Wrote:

> Hi!

>

> It's not clear what you want.

>

> Do you want to lookup a value that is on each sheet or do you want to

> lookup

> a value that may be on any sheet?

>

> Biff

>

> "sonic-the-mouse" sonic-the-mouse.1q0q5o@news.officefrustration.com

> wrote

> in message news:sonic-the-mouse.1q0q5o@news.officefrustration.com...-

>

> I have been tasked with streamlining a spreadsheet used at work. It

> previously had 28 worksheets

> A sheet for each month of the year to list occurences

> An audit sheet for each month and

> A couple of statistc sheets.

>

> The monthly sheets list events numerically although the numbers are

> not

> consecutive.

>

> I want to only have one audit worksheet which will search the monthly

> sheets looking for the event that is numbered in cell C10.

>

> The previous formula was

>

> =if(isblank(c10),"",vlookup('Jan'!a2:t999,2,false)).

>

> How can I adapt this to look up all the monthly worksheets to find

> the

> event number? There are a number of formula in the audit sheet which

> will read further information in the same row. Any assistance is

> greatly appreciated.

>

> stm

>

>

> --

> sonic-the-mouse-

--

sonic-the-mouse

I would like to place this online, so visitors can fill in the fields and get a total (but can't see the data and formulas).

The data is on multiple worksheets and there are no macros in the file.

Is this possible (without any complex programming)?

when a change needs to be made to the basic fomula, the results on the other

worksheets will be updated.

'pseudo code if D2>$V$3 and d2<$W$3 "true" then D2-$q$2 "false" then if D2> $W$3 and d2<$X$3 "true" then D2-$R$2 "false" then if d2> $x$2 and d2< $y$2 "true" then d2-$s$2 "false" then if d2>=$y$2 "true" then d2-$t$2, "False" "Something went wrong"so basically the formula checks multiple conditions then outputs the value when its true.

An additional problem is employees at my warehouse have two start times 5pm and 8pm. So I would need someway of telling the formula if it was checking for 5pm or 8pm...however if the cell used to check that was empty then it would assume it was for 5pm start time. Obviously if the user starts at 8pm the formula would change but that shouldn't be to hard of a fix as long as the code/formula allows for this exception.

Thanks

Karamazov

Entered the greater than logical operator and hey presto it works like

a dream. Thank you for your patience and time it is most appreciated.

stm

Domenic Wrote:

> In article uxb30ZfaFHA.604@TK2MSFTNGP10.phx.gbl,

> "Biff" biffinpitt@comcast.net wrote:

> -

> Here's what's really strange, I'm

> replying to your post and in the previous quoted response from me the

> formula is also missing the greater than logical operator. But in my

> reply

> that is the explanation of the formula, the operator is there.-

>

> We must be in the 'twilight zone'.

--

sonic-the-mouse

I have a workbook with 54 tabs. The first tab is a summary "at-a-glance" information sheet. The following 53 tabs contain invoice dates and amounts for various contracts. Each tab is a separate contract. I want to find a way to pull the information from cell B33 on each of the 53 contract tabs, and enter into Column E on the summary sheet.

For example - I want contract1B33 to appear in SummaryE2, contract2B33 to appear in SummaryE3 and so on without having to manually enter the formula each time. Does that make sense?

I am sure there is a simple way to do this, but I can’t figure it out. Any help is greatly appreciated. Thank you.

So my summary tab would have the company numbers across the top and the titles along column A. If the detail page matches the company number and has the same title, it would drop in the amount. Each detail tab looks like the summary tab except with only one column of data

I was able to do this using index and match when the data was on one sheet but now that I have expanded by using '1000':'3000' for example, it doesn't seem to work properly. I also need to use IF(ISERROR to get rid of the #N/A if a certain detail sheet doesn't have that specific title so I can sum each company at the bottom of the summary tab. This was also working ok when it was all on one sheet so I need to figure out what I am doing wrong and if you are able to use this formula across multiple worksheets! If there is a better formula to use to achieve this goal please let me know. I am new to these sorts of more complex formulas so any help would be greatly appreciated! Thanks!

I have a workbook containing 40 worksheets. In Sheet1 I have a cell, say C1 containing the following formula:

=SOW!$D$8+(SOW!A20*7)-7.

The formula represents a date, 23 August 2010. I want to copy the cell containing the formula to the other 39 worksheets so the relative cell reference, A20, incrementally changes to A21, ..... A22 etc to change the date respectively to 30 August 2010, 06 September 2010 ................. etc. I can/could copy the formula to the 39 worksheets and then change the relative cell reference A20 manually but this will take time. Is there a method of copying the formula to multiple worksheets, which automatically increments the cell reference A20?

Mant thanks

For example:

In L15 on sheet 1 I have “=myFunc1($G15)”. It returns a value of .73.

When I switch to sheet 2, which has the same formula in L15, the value displayed is .73. I have to force a recalculation to get the correct value of 1.05.

Then when I switch back to sheet 1, L15 contains the value 1.05 from sheet 2.

I have tried making myFunc1 volatile and tried using the Workbook_SheetActivate event to force each sheet to recalculate upon activation. Neither option worked.

ABCDEFG1**Aug-02Sep-02Oct-02Nov-02Total2Revenue******3*Book*******2,288*******15,389*******29,965*******46,926********94,568*4*CD*******4,900********4,814********5,377********5,580********20,671*5*DVD**********250***********238***********226***********214*************927*6*Tape**********654***********758***********868***********857**********3,137*7*******8Gross*Revenue********8,092*******21,198*******36,436*******53,578*******119,303*9*******10Expenses******11*Bank*charges************52*************85*************63*************87*************287*12*Consulting*Fees*******4,000********7,520********8,740********9,168********29,428*13*Depreciation**********644***********225*************79*************28*************976*14*General*Office*************54**************65*************119*15*Professional*fees**********478***********555*************85***********657**********1,775*16*******17Total*Expenses********5,174********8,439********8,967*******10,004********32,584*18*******19Net*Income********2,918*******12,759*******27,468*******43,573********86,719*20*******Happy*

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box

PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Hi, I have multiple worksheets each containing a financial statement like the above. Each worksheet has a different company name. Each worksheet's type of revenue and expenses could be different.

I need to combine all of them into a worksheet of predetermined format, thus pivot table is not a consideration. All the worksheets and the summary worksheet is in the same workbook.

In the summary worksheet, I have a check box to select which company I want to be included in the summary. So everytime I check a company, the name would appear in A1:A10. The way the formula is setup now is a multiple IF statements hard coded in each cell. ie. IF A1=HAPPY,HAPPY!D14,0)+IF(A2=SAD,SAD!D14,0+IF(A3=MAD,MAD!D14,0)+IF(A4=JOY,JOY!D14,0)

So the more companies I have, the more IF I have to add-on. Now, I can use the INDEX(MATCH()) to grab the info I need from each company to the summary worksheet.

The problem is how to sum each number according to which companies are selected.

I hope I'm explaining it clearly. Is there a formula or VBA procedure to look at a range of worksheet names and sum a particular cells from the different worksheets?

provide a return of the prior 3 months. Each month, when I add the next

month data, I have to update these formulas to refer to the data in the prior

two months worksheets i.e. this month I had to update last months formula

from pulling data from the Mar, Apr, & May worksheet to the Apr, May, and Jun

worksheets. How can I get a formula to automatically update and pull the

prior two months data to complete the return?

index and see if it will help.

http://www.rondebruin.nl/tips.htm

"Isaiah Melton" wrote:

> Does any one have a formula or code for "Copying a range of data across

> multiple worksheets?

1. I click in a cell and type "=" to start a formula in worksheet 1.

2. I then click a cell in that same worksheet to select my first figure and then type "+".

3. I open another worksheet that I'll call worksheet 2 (in the same workbook) and click a cell to select my second figure and then type "+".

4. I repeat step 3 (except the "+")with a third worksheet.

5. Finally, I press Enter to add all three figures together and Excel returns me to the first worksheet but leaves the original cell, containing the formula, completly blank.

The same problem occured with Excel 2007 before I was upgraded to 2010.