Copying formulas #12

Hi all,

I have a row of forumulas, which relate to columns of data. Each formula 
needs to be copied down the sheet, but I need the column value to change, not 
the row value.

eg C3 : =sum(c2:c31)
     C4 : =sum(d2:d31)
     C5: =sum(e2:e31)

etc.

How can I replicate the formulas down, but get the Column values to change ?

TIA

Stu
0
StuWast (5)
11/16/2005 11:30:04 AM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
531 Views

Similar Articles

[PageSpeed] 26

Ignoring the issue of Circular references (the formula in C3 references a 
range that includes C3)...

I *think* this will work for you

Put the formula   =SUM(C$2:C$31) into an empty row in your spreadsheet and 
copy it across to all the columns you want to sum.  Now copy all those 
formulas, select cell C3 and use Edit->Paste Special->Transpose

"Stu - Wast" wrote:

> Hi all,
> 
> I have a row of forumulas, which relate to columns of data. Each formula 
> needs to be copied down the sheet, but I need the column value to change, not 
> the row value.
> 
> eg C3 : =sum(c2:c31)
>      C4 : =sum(d2:d31)
>      C5: =sum(e2:e31)
> 
> etc.
> 
> How can I replicate the formulas down, but get the Column values to change ?
> 
> TIA
> 
> Stu
0
DukeCarey (494)
11/16/2005 11:45:02 AM
Hi Duke,

The circular reference ignored the fact that the source cells are actually 
on a different sheet....., which I ommiteted from the formula.

But I like the idea of the transpose - I think it should work as well...

So, just the 25 columns on 12 spreadsheet to go then...

THanks

Stu

"Duke Carey" wrote:

> Ignoring the issue of Circular references (the formula in C3 references a 
> range that includes C3)...
> 
> I *think* this will work for you
> 
> Put the formula   =SUM(C$2:C$31) into an empty row in your spreadsheet and 
> copy it across to all the columns you want to sum.  Now copy all those 
> formulas, select cell C3 and use Edit->Paste Special->Transpose
> 
> "Stu - Wast" wrote:
> 
> > Hi all,
> > 
> > I have a row of forumulas, which relate to columns of data. Each formula 
> > needs to be copied down the sheet, but I need the column value to change, not 
> > the row value.
> > 
> > eg C3 : =sum(c2:c31)
> >      C4 : =sum(d2:d31)
> >      C5: =sum(e2:e31)
> > 
> > etc.
> > 
> > How can I replicate the formulas down, but get the Column values to change ?
> > 
> > TIA
> > 
> > Stu
0
StuWast (5)
11/16/2005 12:16:03 PM
Hi Duke,

It did work for a simple formula.

However, some of the formulas are quite complex :

=COUNTIF(Planner!C2:C31,"s04")+COUNTIF(Planner!C2:C31,"s05")+COUNTIF(Planner!C2:C31,"s06")+COUNTIF(Planner!C2:C31,"s07")+COUNTIF(Planner!C2:C31,"s08")+COUNTIF(Planner!C2:C31,"s09")+COUNTIF(Planner!C2:C31,"s10")+COUNTIF(Planner!C2:C31,"s11")+COUNTIF(Planner!C2:C31,"s12")

I can copy this, repeate it horizontally, but when I re-copy & transpose it 
back to the original cells, the "C2:C31" beocmes #REF!. I've tried pasting 
with different options - all, formula, formats, but none of them seem to copy 
just the formula as is - they all want to change the values.

Any further help would be much appreciated.

Thanks, Stu

"Stu - Wast" wrote:

> Hi all,
> 
> I have a row of forumulas, which relate to columns of data. Each formula 
> needs to be copied down the sheet, but I need the column value to change, not 
> the row value.
> 
> eg C3 : =sum(c2:c31)
>      C4 : =sum(d2:d31)
>      C5: =sum(e2:e31)
> 
> etc.
> 
> How can I replicate the formulas down, but get the Column values to change ?
> 
> TIA
> 
> Stu
0
StuWast (5)
11/16/2005 1:00:08 PM
You're right that it's more complex.  Howver, it does not look as though you 
used absolute row references as suggested.  Change all the C2:C31 references 
to C$2:C$31 and try again.

"Stu - Wast" wrote:

> Hi Duke,
> 
> It did work for a simple formula.
> 
> However, some of the formulas are quite complex :
> 
> =COUNTIF(Planner!C2:C31,"s04")+COUNTIF(Planner!C2:C31,"s05")+COUNTIF(Planner!C2:C31,"s06")+COUNTIF(Planner!C2:C31,"s07")+COUNTIF(Planner!C2:C31,"s08")+COUNTIF(Planner!C2:C31,"s09")+COUNTIF(Planner!C2:C31,"s10")+COUNTIF(Planner!C2:C31,"s11")+COUNTIF(Planner!C2:C31,"s12")
> 
> I can copy this, repeate it horizontally, but when I re-copy & transpose it 
> back to the original cells, the "C2:C31" beocmes #REF!. I've tried pasting 
> with different options - all, formula, formats, but none of them seem to copy 
> just the formula as is - they all want to change the values.
> 
> Any further help would be much appreciated.
> 
> Thanks, Stu
> 
> "Stu - Wast" wrote:
> 
> > Hi all,
> > 
> > I have a row of forumulas, which relate to columns of data. Each formula 
> > needs to be copied down the sheet, but I need the column value to change, not 
> > the row value.
> > 
> > eg C3 : =sum(c2:c31)
> >      C4 : =sum(d2:d31)
> >      C5: =sum(e2:e31)
> > 
> > etc.
> > 
> > How can I replicate the formulas down, but get the Column values to change ?
> > 
> > TIA
> > 
> > Stu
0
DukeCarey (494)
11/16/2005 1:24:02 PM
D'oh!

Brilliant, that works perfect.

Again, thanks very much.

Now, I don't suppose you have any ideas for my other post - "Excel Ranges & 
LEFT" ???

Cheers

Stu

"Duke Carey" wrote:

> You're right that it's more complex.  Howver, it does not look as though you 
> used absolute row references as suggested.  Change all the C2:C31 references 
> to C$2:C$31 and try again.
> 
> "Stu - Wast" wrote:
> 
> > Hi Duke,
> > 
> > It did work for a simple formula.
> > 
> > However, some of the formulas are quite complex :
> > 
> > =COUNTIF(Planner!C2:C31,"s04")+COUNTIF(Planner!C2:C31,"s05")+COUNTIF(Planner!C2:C31,"s06")+COUNTIF(Planner!C2:C31,"s07")+COUNTIF(Planner!C2:C31,"s08")+COUNTIF(Planner!C2:C31,"s09")+COUNTIF(Planner!C2:C31,"s10")+COUNTIF(Planner!C2:C31,"s11")+COUNTIF(Planner!C2:C31,"s12")
> > 
> > I can copy this, repeate it horizontally, but when I re-copy & transpose it 
> > back to the original cells, the "C2:C31" beocmes #REF!. I've tried pasting 
> > with different options - all, formula, formats, but none of them seem to copy 
> > just the formula as is - they all want to change the values.
> > 
> > Any further help would be much appreciated.
> > 
> > Thanks, Stu
> > 
> > "Stu - Wast" wrote:
> > 
> > > Hi all,
> > > 
> > > I have a row of forumulas, which relate to columns of data. Each formula 
> > > needs to be copied down the sheet, but I need the column value to change, not 
> > > the row value.
> > > 
> > > eg C3 : =sum(c2:c31)
> > >      C4 : =sum(d2:d31)
> > >      C5: =sum(e2:e31)
> > > 
> > > etc.
> > > 
> > > How can I replicate the formulas down, but get the Column values to change ?
> > > 
> > > TIA
> > > 
> > > Stu
0
StuWast (5)
11/16/2005 2:18:12 PM
How about making your formula shorter while still being able to copy down:

=SUMPRODUCT(--(INDEX($2:$2,ROWS($1:3)):INDEX($31:$31,ROWS($1:3))={"s04","s05
","s06","s07","s08","s09","s10","s11","s12"}))

OR ... even:

=SUM(COUNTIF(INDEX($2:$2,ROWS($1:3)):INDEX($31:$31,ROWS($1:3)),{"s04","s05",
"s06","s07","s08","s09","s10","s11","s12"}))

-- 

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


"Stu - Wast" <StuWast@discussions.microsoft.com> wrote in message
news:71A5022B-6A85-4427-939B-169F2E36CE99@microsoft.com...
D'oh!

Brilliant, that works perfect.

Again, thanks very much.

Now, I don't suppose you have any ideas for my other post - "Excel Ranges &
LEFT" ???

Cheers

Stu

"Duke Carey" wrote:

> You're right that it's more complex.  Howver, it does not look as though
you
> used absolute row references as suggested.  Change all the C2:C31
references
> to C$2:C$31 and try again.
>
> "Stu - Wast" wrote:
>
> > Hi Duke,
> >
> > It did work for a simple formula.
> >
> > However, some of the formulas are quite complex :
> >
> >
=COUNTIF(Planner!C2:C31,"s04")+COUNTIF(Planner!C2:C31,"s05")+COUNTIF(Planner
!C2:C31,"s06")+COUNTIF(Planner!C2:C31,"s07")+COUNTIF(Planner!C2:C31,"s08")+C
OUNTIF(Planner!C2:C31,"s09")+COUNTIF(Planner!C2:C31,"s10")+COUNTIF(Planner!C
2:C31,"s11")+COUNTIF(Planner!C2:C31,"s12")
> >
> > I can copy this, repeate it horizontally, but when I re-copy & transpose
it
> > back to the original cells, the "C2:C31" beocmes #REF!. I've tried
pasting
> > with different options - all, formula, formats, but none of them seem to
copy
> > just the formula as is - they all want to change the values.
> >
> > Any further help would be much appreciated.
> >
> > Thanks, Stu
> >
> > "Stu - Wast" wrote:
> >
> > > Hi all,
> > >
> > > I have a row of forumulas, which relate to columns of data. Each
formula
> > > needs to be copied down the sheet, but I need the column value to
change, not
> > > the row value.
> > >
> > > eg C3 : =sum(c2:c31)
> > >      C4 : =sum(d2:d31)
> > >      C5: =sum(e2:e31)
> > >
> > > etc.
> > >
> > > How can I replicate the formulas down, but get the Column values to
change ?
> > >
> > > TIA
> > >
> > > Stu


0
ragdyer1 (4060)
11/16/2005 2:36:33 PM
Wow - I'll give it a try.

Thanks

Stu

"RagDyeR" wrote:

> How about making your formula shorter while still being able to copy down:
> 
> =SUMPRODUCT(--(INDEX($2:$2,ROWS($1:3)):INDEX($31:$31,ROWS($1:3))={"s04","s05
> ","s06","s07","s08","s09","s10","s11","s12"}))
> 
> OR ... even:
> 
> =SUM(COUNTIF(INDEX($2:$2,ROWS($1:3)):INDEX($31:$31,ROWS($1:3)),{"s04","s05",
> "s06","s07","s08","s09","s10","s11","s12"}))
> 
> -- 
> 
> HTH,
> 
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
> 
> 
> "Stu - Wast" <StuWast@discussions.microsoft.com> wrote in message
> news:71A5022B-6A85-4427-939B-169F2E36CE99@microsoft.com...
> D'oh!
> 
> Brilliant, that works perfect.
> 
> Again, thanks very much.
> 
> Now, I don't suppose you have any ideas for my other post - "Excel Ranges &
> LEFT" ???
> 
> Cheers
> 
> Stu
> 
> "Duke Carey" wrote:
> 
> > You're right that it's more complex.  Howver, it does not look as though
> you
> > used absolute row references as suggested.  Change all the C2:C31
> references
> > to C$2:C$31 and try again.
> >
> > "Stu - Wast" wrote:
> >
> > > Hi Duke,
> > >
> > > It did work for a simple formula.
> > >
> > > However, some of the formulas are quite complex :
> > >
> > >
> =COUNTIF(Planner!C2:C31,"s04")+COUNTIF(Planner!C2:C31,"s05")+COUNTIF(Planner
> !C2:C31,"s06")+COUNTIF(Planner!C2:C31,"s07")+COUNTIF(Planner!C2:C31,"s08")+C
> OUNTIF(Planner!C2:C31,"s09")+COUNTIF(Planner!C2:C31,"s10")+COUNTIF(Planner!C
> 2:C31,"s11")+COUNTIF(Planner!C2:C31,"s12")
> > >
> > > I can copy this, repeate it horizontally, but when I re-copy & transpose
> it
> > > back to the original cells, the "C2:C31" beocmes #REF!. I've tried
> pasting
> > > with different options - all, formula, formats, but none of them seem to
> copy
> > > just the formula as is - they all want to change the values.
> > >
> > > Any further help would be much appreciated.
> > >
> > > Thanks, Stu
> > >
> > > "Stu - Wast" wrote:
> > >
> > > > Hi all,
> > > >
> > > > I have a row of forumulas, which relate to columns of data. Each
> formula
> > > > needs to be copied down the sheet, but I need the column value to
> change, not
> > > > the row value.
> > > >
> > > > eg C3 : =sum(c2:c31)
> > > >      C4 : =sum(d2:d31)
> > > >      C5: =sum(e2:e31)
> > > >
> > > > etc.
> > > >
> > > > How can I replicate the formulas down, but get the Column values to
> change ?
> > > >
> > > > TIA
> > > >
> > > > Stu
> 
> 
> 
0
StuWast (5)
11/16/2005 4:30:04 PM
Reply:

Similar Artilces:

How to specify numeric format in formula?
I have a cell with the following: ="The value is $" & A6 & "." Cell A6 is an integer and it's formatting is set to look like "4,000.00". In the cell above that references A6 the value looks like "4000". How can I explicitly specify formatting in my "=" statement above? Thanks! ="The value is " & TEXT(A1,"#,##0.00") & "." Forgot: add the $ sign like this: ="The value is " & TEXT(A1,"$#,##0.00") & "." Use text ="The value is "&TEXT(A6,...

Locked for Editing 03-12-10
There is an excel workbook that is locked for editing and it has at this state for over a week. I need to be able to forcibly delete this file, renaming as a different file name is not an option. Is there anyway that I can forcibly delete this file? ...

Copying and pasting values error 1004
Whenever i run this piece of code i get the error "paste method of worksheet class failed" runtime error 1004 This is in excel 2003 and i did not have this problem in 2000. Coul anyone shed any light on this?? Range("A1").Select Sheets("atl").Select Range("A1").Select Selection.AutoFilter Field:=9 Selection.AutoFilter Field:=8 Selection.AutoFilter Field:=11 Selection.AutoFilter Field:=9, Criteria1:="1" Selection.AutoFilter Field:=13, Criteria1:="3" Columns("J:J").Select Range("J:J,B:B").Select Selection.Copy Sh...

Long Delay copying xls files in Windows Explorer
I have created several Excel files ranging between 2Mb & 4Mb i filesize. Why does Windows Explorer treat xls files differently from every othe file type, in that when I single click on one of those xls files, o right click on one of them, it seems to take forever (up to over minute) before I can do anything else. For instance, I want to make a backup copy of one of my xls files, so go to the folder using Windows Explorer, then I right click on an xl file, I then have to wait for up to a minute or so, before the dro down list appears, then I can choose "Copy", and the drop down ...

Formula pop-up box
Using Excel 2002, when I start to enter a formula in a cell, for example, =sum( I get this little box that pops up right in my way, showing me how I might like to complete it the formula. Does anyone know how to turn this thing off so it doesn't pop up all the time? Thanks very much. Choose Tools>Options Select the General tab Remove the check mark from 'Function tooltips' Click OK NKH.UofW wrote: > Using Excel 2002, when I start to enter a formula in a cell, for example, > =sum( I get this little box that pops up right in my way, showing me how I > might lik...

Copy paste code for a #REF
Hi, I am looking for a workbook code that runs when the file is opened. In col H I have a formula (=INDIRECT(G8)) which searches data from another series of folders. If the source folder is open it returns either a value or 0. If the relevant source file is closed it returns a #REF. What I would like is a code that looks through all the cells in Col H, if it finds a numerical value or 0 it does a copy/paste values on the cells, if it finds a #REF it does nothing. The file is a update folder so the REFs will be in the future and the values in the current/past which i nee...

Formula #24
I have a workbook that is divided by dates. I want to create or use an existing formula that updates my workbook. When I choose a date, I want the formula to link certain columns of information and transfer the data to the corresponding sheet for that date. The column in question is a series of dates in a drop down box. Is there any hope of doing this? ...

+= prefix to formula
I've received an Excel sheet which has many cells with formulae prefixed by +=. I cannot find any explanation of the significance of putting plus before equals. Any pointers to an explanation? Thanks JJ is it += or =+? When I type formulas I always use +, like +if(a1, etc) and they change to =+if(a1, etc) "JJDuffin" wrote: > I've received an Excel sheet which has many cells with formulae prefixed by +=. > I cannot find any explanation of the significance of putting plus before > equals. > > Any pointers to an explanation? > > Thanks > >...

Formula Problem
I am trying to create a scheduling formula which alerts me if there are scheduling conflicts. I have 4 different dates which a 5th date cannot match. In addition, it cannot conflict with the 4th date and including 35 days prior. For example: B2 = Inventory Date (cannot conflict with this date and 35 days prior) C2 = Reset 1 Date - (cannot match this date) D2 = Reset 2 Date - (cannot match this date) E2 = Reset 3 Date - (cannot match this date). The formula I have is: =IF(F2="","",IF(F2=C2,"Reset 1 Conflict",IF(F2=D2,"Reset 2 Conflict",IF(F2=E2,"...

copy data from one sheet to another using a combo box
I Have 2 sheets, a database of items sheet and a form with a combo box to put the record I get from the database. I want to pick a record from the database sheet and add it to my form and have it bring the whole record with it to the form.both sheets have the same fields. -- Thank you Investigate the help files for VLOOKUP. I think that's what you need. "Tomwireless" <Tomwireless@discussions.microsoft.com> wrote in message news:DA29DC49-0E8D-4A74-B61E-B448BDF067CD@microsoft.com... >I Have 2 sheets, a database of items sheet and a form with a combo bo...

Shortening the Formula
This formula works: =SUMPRODUCT(--((E3:E1000=6)+(E3:E1000=6.5))*(M3:M1000="w")) I tried a lot of different ways, but I couldn't find a way to shorten this part: (E3:E1000=6)+(E3:E1000=6.5) I'm sure there is a way to make that more efficient. Thanks You do not really need the double negation. It is used to transform TRUE/FALSE to 1/0 - Excel does this whenever a math operation is performed on a Boolean value. But you are multiplying so the double neg is redundant. either =SUMPRODUCT(((E3:E1000=6)+(E3:E1000=6.5))*(M3:M1000="w")) or =SUMPRODUCT(--((E3:E1000=6)+...

Half way there, deleting numbers but keeping the formula
Thanks Paul and Niek...I need to clarify. I have some blocks with numbers and some with numbers and formulas. I want to keep the formulas, and remove the numbers, so I and just go in and add this months numbers and start with a clean sheet....but not have to replace the formula. I hope I am explaining this correctly. When I did the edit...., special...., it only removed either or but would not leave the formula. Thanks Vince, There's no way to do what you're asking, per se. The values that you see are the result of the formulas. One way around this is to rework all of your for...

Copying Subreports
CAN YOU COPY A SUBREPORT AND CHANGE THE DATASOURCE? -- phm HISTORY Phm question: Hello, I have eight regional subreports to developed. I would like to develop one; and, for the others, just copy the original one and change the datasource. Is this possible? So far, all I see on the copied, pasted report is a blank, subreport control. Please help! phm - darlyS answer: It would be easier to create one report that will work for all regions, and include a parameter to choose which region to run the report for. Only one report to maintain that way. Daryl S Phm ...

Formula question in a report
I have a formula to add travel hrs and another to add work hrs. =Sum([TRAVELREG]) =Sum([WRKREG]) Can you give me the formula to add the two together? Thanks Try this -- =Sum(Nz([TRAVELREG], 0) + Nz([WRKREG], 0)) -- Build a little, test a little. "Fly Boy 5" wrote: > I have a formula to add travel hrs and another to add work hrs. > > =Sum([TRAVELREG]) =Sum([WRKREG]) > > Can you give me the formula to add the two together? > > Thanks ...

Slow copying
Hi, I've been trying to copy about 60GB of small files (tens of thousands of them) in a deep file structure from a NAS box to a Virtual Server 2008 using both Robocopy and Explorer. Both servers are in the same rack. The speed varies from 3MB/s to 15MB/s, but averaging under 10MB/s. To an ignorant person like me that seems a bit low. Does that seem correct? I should mention that I was pretty much the only person on the LAN at the time. I'm afraid I don't know what I'm talking about here... Cheers, James Hello JimLad, See here about slow copy problem...

complicated IF THEN formulas
Hi: I am a newbie that has been struggling to try to figure out how to create some more complicated IF THEN formulas in Excel. For example; I want to get a value of 2 for 1<A30 <= 200, 3 if 200<A30<=300, and 4 if 300<A30<=500. I have spent hours trying to figure it out. I can't understand what I am doing wrong. I have tried all of the Help files etc. etc. Maybe I am just too dumb to get the syntax correct. Would a kind soul please send me a message with the formula. Thank you very much. jcasilio@comcast.net. -- jcasilio -------------------------------------------...

Rewriting a formula...
I have a spreadsheet that calculates totals and has formulas in certain columns. Is is possible to have the formulas work, but not have any numbers in the cells before I input them? For example, in column D, I have the simple formula =D9+E9, and so on and so forth down to =D25+E25. I may only put itmes in until D11. So in D12 thru D25, I have $0.00. Can I rewrite this formula, possible an IF/THEN statement to add the two columns, but not show the $0.00 in D12 thru D25, since I did not have to do any addition? My main goal is to not have this info in D12 thru D25 print to make the sheet ...

Have you selected "Leave copy of message on server"?
Several times I've seen this asked in response to a request for help, but I don't recall ever seeing advice on the matter. Which can cause the problems... leaving a copy, or failing to do so? On Sat, 20 Feb 2010 20:57:47 -0330, "J. Earthling" <earthling@planet.earth> wrote: >Several times I've seen this asked in response to a request for help, but I >don't recall ever seeing advice on the matter. Which can cause the >problems... leaving a copy, or failing to do so? > The main reason for leaving a copy on the server is to make it...

Text in Cell as Formula #3
Peo + Julie Many thanks for your answer with the last query. I have another similar question; I would like to use values form a different spreadsheet, and this method [INDIRECT()] fails. i.e. I type the link (as text) from a different spreadsheet in a cell in the current spreadsheet and use this method to call a value in a cell. Appreciate your help. ...

if cell starts with characters formula
Hi I need to count cells in a column starting with certain characters. each cell's data varies in length. I have tried with @countif( but does not work if the cell contains other characters after the "prefix". eg. row 20 cell 5 apples row 21 cell 5 apples red row 22 cell 5 apples green row 23 cell 5 plums green row 23 cell 5 plums purple totals required for apples = 3 (regardless of colour) total required for plums = 2 (regardless of colour) @countif(C20:c30,"plums") gives answer of 1 require answer of 2 @countif(C20:c30,&quo...

Help with Date/array formula
Have 2 columns that could contain dates. Column A and B. Need to count the number of each month in both columns. If column B is empty, use month in column A. If B not empty use B. This formula works in every month except JANUARY. (Array) =SUM(IF(MONTH(IF(B1:B5="",A1:A5,B1:B5))=1,1,0)) Appears to read blank cells in range as 1(true). Have been fightin this for way toolong and will appreciate any help ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com One way ...

Formulas #22
I need the average for a set of survey questions and I cannot figure out how to get the answers I need and write the formulas. I have 11 survey questions, 5 choices for each question(1 is bad--5 is best), a combined # of students(30) for 3 classes...1st class=11 students, 2nd class=9 students, 3rd class=10 students. My boss says he wants the averages to be in the 4%-5% range, but my calculations came up with ranges more like 10%, 11%, etc. And I am no mathemetician or a "formula girl", so any help would be greatly appreciated. Thanks! "Newby07" wrote: > I...

Slow Performance on Site-to-Site using Folder Redirection 12-23-09
I have a situation that is forcing me to stop using Folder Redirection on the remote site due to poor performance, mostly slowness. I, on the other hand have set up a laptop to use offline files on the same domain. At this point it seems to me that offline files will be the best option here. The domain consist of an SBS2008 Premium with a remote Standard Server domain controller. The site to site is using a Sonicwall appliance. I love using FR, but cannot sacrifice performance and will hate to think that it was only meant to be used for authentication purposes. Any ideas? Thank you f...

Formula #35
I need to get the value of the cell in the last populated row in a column on another sheet, and I use a formula like this to get it: =LOOKUP(9.99999999999999E+307,Sheet4!E3:E30) My problem is that I now need to calculate the value from the E column, so until it is calculated, Excel shows the value to be $0.00 (the result of the formula before any other values have been entered), so I'm picking up $0.00 as the last value. How do I get the last value that has been calculated or greater that 0? Hi, You are going to need VBA, the Excel spreadsheet can't determine if a cell is ...

If-formula in series-range of chart-datasource
Hello there, depending on the "True" or "False"-value of a checkbox in a worksheet, I need to show one or the other dataseries for the X-axis in an XL-chart. However, when I try to integrate an If-formula into the series-definition in the datasource-dialogbox, Excel doesn't accept that. Is there a way to make a dataseries shown dependent on a condition ? Thank you in advance, Kind regards, H.G. Lamy Hi look at the following examples http://peltiertech.com/Excel/Charts/ChartByControl.html http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html ...