Lets say I had in cell $A$1 the number "36", in row2 figures for monthly spend, in row 3 cumulative spend and in row 4 the month number(1,2,3,4..). What I want to do is substract the monthly spend from $A$1 months ago from my cululative figure in row 3 so I have a rolling cummulative hardware spend value of 36 months in this case. So in this case if I was to do it manually in cell AK3 I would have "=AJ3+AK2-A2" and drag it further right. So I add my monthly figure of spend each month to the cummulative figure in row three but also deduct the monthly spend from 3 years prior.

So in order to use the figure in $A$1 I could do something in row 3 like: starting from B3: "=if(B4>$A$1,A3+B2-[$A$1rows_further_back_thanB2],A3+B2). So when I get to month 37 in row 4 I deduct the figure in row 2 (in the formula in row 3 - the cummulative row) from 36 months prior. I hope this all makes sense. Can you help me please (see attached spreadsheet).

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link __ http://downloadreimage.com/download.php__. (This link will automatically start a download of Reimage that you can save to your computer.)

I need to provide seqential row numbers from 1 to N that relate to cells in another column when the cell value is greater than zero. For instance, if the column with data is column d and I have data greater than zero in cells d3, d4, d5, and d7 I'd like the row count in column c to read number 1 for c3, 2 for c4, 3 for c5, and 4 four c7. Cells c1 through c3 as well as c6 should remain blank. What formula do I use?

I have an Excel workbook with 400k rows Each set of rows contains a table name with the fields in the table. The number of fields is not the same for each table. For example:

COLUMN A COLUMNB

AV_ACTN_RSLT_VWACTN_RSLT_CDAV_ACTN_RSLT_VWDESCRAV_ACTN_RSLT_VWDESCRSHORTAV_ACTN_RSP_VWEMPLIDAV_ACTN_RSP_VWEXT_ORG_IDAV_ACTN_RSP_VWINSTITUTIONAV_ACTN_RSP_VWNAMEAV_ACTN_RSP_VWRSPL_ID

AMT_X_SAL_TYPEWCS_PLAN_DESCR20AMT_X_SAL_TYPEWCS_TOT_AMOUNT

I need to convert the field names so that they are in a row like this:

AMT_X_SAL_TYPEWCS_PLAN_DESCR20WCS_TOT_AMOUNT

Is there any way for me to do this programatically? I'm using the transpose feature but it is time consuming and I have 400k rows. So basically, I want something like if the field in column A row 1 matches row 2, move that field to the right of row 1 and so on until the table name changes.

Or maybe i should dump the spreadsheet in access and try there...

Thanks,

Amy

Hi,

I am having data structure as below. I need some macro or some means to populate formula in Column D based on column C (as and when it changes) . All other column data are populated. This is beyond me. Any help or direction is greatly appreciated. Thanks in advance

Col | A | B | C | D | E

------------------------------------------------------------------------

Row | 1 | Test1 | | | 10

| 2 | Test2 | =A1 | =MAX(E2) }| 15

| 3 | Test3 | =A2 | =MAX(E3) | 20

| 4 | Test3 | =A1&","&A3| =MAX (E1,E3)| 30

Hello,

I am attempting to clear 2 cells, based off the value of another. I am pretty sure the code is correct, because it works within another Macro. Thanks.

Code:

Sub REMOVE()

Dim p As Long

p = Cells(Rows.Count, "a").End(xlUp).Row

For i = 1 To p

Range("k2").Select

If InStr(1, Range("k" & p), "None") > 0 Then Range("L" & p) = "" And Range("M" & p) = ""

'If no Issue, Location/Obsevations should be blank

Next i

End Sub

hi

try this variation;

Sub REMOVE()

Dim p As Long

p = Cells(Rows.Count, "a").End(xlUp).Row

For i = 1 To p

If InStr(1, Range("k" & i), "None") > 0 Then

Range("L" & i) = ""

Range("M" & i) = ""

End If

'If no Issue, Location/Obsevations should be blank

Next i

End Sub

Excel 2007 for PC

Rookie Excel user requesting help writing a macro that will overwrite information in one column, based on information in a second column. See attached file.

Read other 7 answers

I need some help. I am trying to come up with a formula that counts the times that a "string" occurs based on a value in another column.....

Example:

Everytime column a = "Jones", check if column e = "tom".....if so count it.

The EASY way to do this is to concatenate columns A and E using (for instance) this formula:

=A1&E1

or

=A1&" "&E1 (puts a space between two words)

Then count the individual field. Make sense? Will it work for you?

------------------

~dreamboat~

Brainbench MVP for Microsoft Word

Brainbench

I have a database that i'm creating that has 3 different tables. Its for safety violations at work. One table is the innitial one that has all the pasic information on it. I set it up so that it automatically assigns a number to each complaint filed. The other two are for follow up. One is for how the problem is going to be fixed and who is supposed to do it, And the other one is for verification to make sure that the problem has been fixed. My problem is that i want the two follow up tables to automatically import the complaint number when a new complaint is filed. I tried to set everything up myself, but had no luck. Can someone please help? Thanks.

I have an Excel 2003 spreadsheet that has three columns that are populated by dropdown lists. I have attached a sample based on my real problem for you to see. Depending on the selection in Column A there could be a required entry in column B or a required entry in column C or it may require that an entry be made into either B or C. If an entry is made in column A and none made in B or C then I need a message box to pop up to remind them to make the additional selection. Thanks in advance for your assistance.

Hello,

Is it possible in one cell to show the total of both the column and the row? The idea is that it would be a double check because they should add up to the same thing. I am working with Windows 2007.

Thanks.

I have a very large spreadsheet with thousands of rows and about 20 columns

Col A has either of 4 values North, South, East, West

The other Cols have numbers

I want to get a total for North, South, East, West for Col B, C etc

Is there a formula I can use something like (if Col A = North , total ColB is )

Don't want to keep resorting by Cola and putting in Subtotals

Hope I have explained it well enough

Thanks

Brian

Dublin, Ireland

yes you can use

SUMIF()

SUMIF(A:A, "north", B:B)

OR if you put the values North, South etc into a new column - Say Z2,Z3,Z4,Z5

SUMIF(A:A, Z2, B:B)

and copy down

if not - please upload a sample

Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone

We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

Not sure exactly how to word what I am trying to accomplish. for each matching cell in Column A, evaluate the values in Column B for matching rows. In the example spreadsheet, I have highlighted the ones I want vba to evaluate. The column can also be as large as 5000 rows at times. So for Dayton, at least one % is 0 and one is more than 1%. In that case I want the 0% highlighted. Same for Los Angeles and Annapolis which has 4 matching rows. However, I don't want Sacramento and Mobile 0% value highlighted because the value(s) are all below 1% each. I don't even know where to start on coding this. Any help is greatly appreciated.

Currently I have 83 products listed in one table in Access and a huge listing of people who use those products in another table. When I run a query to find all the ones who use the products listed I get 58113 connections.

What I would like would be to randomly select 10 to 20 users of each product (the minimum number of users for any one product is 10, but the maximum is over 3000 for the most used product. I would then like to export these randomly selected names to different Excel worksheets in the same workbook - I suppose one could export them all and then break up the data into new worksheets based on the product code....

Oh, and there are 10 products for whom there are no listed users, so I will have to match them up by their reporting districts instead and then select 10-20 as well...(I have run a query for this and the smallest group this way is 15 people.)

Thanks in advance!

I am trying to loop through a column and where there are blanks look up the value with vlookup. Initially I tried looping until cell is blank but of course that stopped the loop at the first blank. I changed it to loop until last row and column but I am sure I am not using the right syntax to do it. It loops great through row but continues past the last row. Any help is appreciated.

Code:

Dim UsedRng As Range, LastRow As Long

Set UsedRng = ActiveSheet.UsedRange

LastRow = UsedRng(UsedRng.Cells.Count).Row

Range("H2").Select

Do

If IsEmpty(ActiveCell) Then

ActiveCell.FormulaR1C1 = "=VLOOKUP(LEFT(RC[-7],8),Sheet1!C1:C3,2,FALSE)"

End If

ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell.Cells(LastRow, 8)

Code:

Dim UsedRng As Range, LastRow As Long

Set UsedRng = ActiveSheet.UsedRange

LastRow = UsedRng(UsedRng.Cells.Count).Row

Range("H2").Select

Do Until ActiveCell.Row = LastRow + 1

If IsEmpty(ActiveCell) Then

ActiveCell.FormulaR1C1 = "=VLOOKUP(LEFT(RC[-7],8),Sheet1!C1:C3,2,FALSE)"

End If

ActiveCell.Offset(1, 0).Select

Loop

End Sub

You need the until element at the start of the loop and you want the loop to end having processed the last row.

I have a column of data that will be updated weekly with more data. I reference the last value in the column (which is the most current value of the column) using this formula:

=VLOOKUP(9.99999999999999E+307,'Sheet1'!G:G,1,1)

The column is column 'G' obviously.

In another cell, I would like to reference the cell 52 rows above that one (a year ago) and possibly take the average of the values inbetween as well. Any ideas?

I played around with a match/offset but I just got lost.

Hi, just wanting to know is there a formula that you can use to display the last line of data entered in a column elsewhere on the sheet?

What I am wanting to do is on "Sheet1" have a list of dates in a coloum, I would then like to have the last date entered automatically displayed on (and updated) "Sheet2"

In an existing report, I need to have a count of records that have a certain text in a particular column. How do I create this command?

Example: Need a count of records in column B that have the word "yes" or "no". So I need a count of how many yes's and how many no's. I need a count for both.

Please help me on how to put two formula in one cell.

here's my first formula

=IF(D6<C6,D6+1,D6)-C6

2nd formula

=COUNTIF(C66,"ABSENT")*0

My work sheet is all about getting number of hours work, and i want the word "absent" "off" to be counted as zero.

Im using excel 2010

I am trying to input a formula in a cell using the value within that cell. I've done this before and it worked, but now I can't seem to get it to work. What am I doing wrong?

Code:

Dim mrange As Range

Dim mcell As Range

Dim mval As String

Sheets("Step3").Select

Set mrange = Range("J12:J2000")

mrange.Select

For Each mcell In mrange

mval = mcell.Value

mcell.FormulaR1C1 = "=If(AND(Iserror(vlookup(RC-1,Listsource!R1C14:R100C15,2,FALSE))," & """" & mval & """" & _

",RC,(VLOOKUP(RC[-1],Listsource!R1C14:R100C15,2,FALSE)," & """" & """" & ")"

Next

Few things

1. your brackets is not properly done.

eg where is you end bracket for AND function.

2. over use of double quote (")

when you shorten " & """" & mval & """", you end up with ""mval""

3. you have reference to RC which is a circular reference.

You can redo the formula in cells and use the immediate window to view the property

?activecell.formular1c1

Hello,

I cant seem work out a solution for what I'm trying to do. I have an Excel workbook that has multiple sheets. On sheet 1 i want the data from cell "G3" to be copied onto sheet 2. But i want the location on sheet 2 to be based on whatever was entered into cell "D3" on sheet 1.

For example: Sheet 1, cell D3 I have the name John, in cell G3 i have 68. I want "68" to be pasted in sheet 2 in cell B26.

But if the name in Sheet 1 cell D3 is Suzie, then I want G3 to be pasted in Sheet 2 in cell D26. So I would need to identify the paste location for each person.

I want the data to paste to the next cell so that the next entry can be pasted below the last entry for that person (for John the first entry would go into cell B26, then the next entry would go into cell B27 and so on).

But i want it to be a specific range, i dont want data to be pasted past 20 cells (cell B45). If possible a message box could be created to let the user know that the max is reached.

I would appreciate anyone's help with this as i have been struggling for awhile to try to get this. Thank you

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.

I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.

I am using excel 2013 on windows 10. Any help would be appreciated.

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.

I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.

I am using excel 2013 on windows 10. Any help would be appreciated.

Hi guys,

I've attached the dummy file. You see the report groups all the relevant data pertaining to the person's name and puts it under the name of the person. I need a macro that will copy the name of the person in column A across all the rows of data pertaining to the person's name in column Z. I will not know which row the name of the person will be in or how many rows of data each person will have, so the macro needs to search through all the rows to find specific names.You'll see an example of what I need in column AA.

I then wrote a macro just based on reading in the net to remove the blank rows in column A. For example A6 is blank, so the macro I wrote deletes that column. The macro also deletes the "Total No. of Cases" row.

This is the macro:

Sub Deletejunk()

'

' Deletejunk Macro

' To delete rows containing *No. of Cases* & Blanks

'

' Keyboard Shortcut: Ctrl+Shift+O

'

With ActiveSheet

.AutoFilterMode = False

With Range("a1", Range("a" & Rows.Count).End(xlUp))

.AutoFilter 1, "*No. of Cases*", xlOr, ""

On Error Resume Next

.Offset(1).SpecialCells(12).EntireRow.Delete

End With

.AutoFilterMode = False

End With

End Sub

I would like that after the previous macro copies the names to column Z this macro could run straight after that. Even better if this macro could delete those names in column A. I'm thinking we would then have to make an array of things to... Read more

i want a single cell to contain two formulas by which i'm very confused to derive this

A(cell):1(value), B:3, result-C:3(a*b), if i again change A cell to 3 the C should come as 6, that is i dont want to multiply, instead i want to double the value of previous C result. Can someone help me?

Hi,

So you have this:

A B C (= A*B)

1 3 3

and you want this:

A B C

3 3 6

This will do it but I sure don't know why you want it.

In C put:

=If(A=1,A*B,A+B)

with row numbers of course.

Hello. I am having trouble with a workbook I am creating. I have five columns with the potential to contain data (it is a gradebook for my class). The problem is that not all columns currently contain scores (I haven't graded a writing assignment, for example). Because those columns not used return the #DIV/0! error, it also screws up my other cells where I calculate GPA and Letter Grades. How do I get it to skip a columns sum if no data has been entered for that column?

Thanks?

I have the spreadsheet if anyone feels they need to "see" it to help.

Jeff See

Read other 8 answers

Hey all:

I'm have really no experience with coding, so I'm out of my depth trying to do this myself. Hopefully someone can help, so here goes!

Essentially, my excel sheet keeps track of dates of when files need to be renewed. They need to be renewed every 60 days. So I have an "IF" formula that tells me if it's time for renewal.

What do I need to do to get an email notification whenever a file comes up for renewal? I've attached a sample sheet.

Best,

Nick

PS.

Using Excel/Outlook 2010

ClickYes Pro is installed (don't know if it will be necessary or not)

Does anyone know a way to manipulate the countif formula to count how many cells are "green" or "red" (with varying text in each cell), rather than the value of the cells?

Can this be done in VB?

Hi guys,

Can you please help me further expand the macro below? I need it to also look between the ranges of C24 and Z24 and hide the respective column. (i.e need some sort of loop)

i.e

if C24 is 0, then hide column C

if D24 is 0, then hide column D

etc...

Sub Hide_Column ()

If Range("H24").Value = 0 Then

Columns("H").EntireColumn.Hidden = True

Else

Columns("H").EntireColumn.Hidden = False

End If

End Sub

How do I create a vlookup formula that also makes any #N/A answer a blank cell?

If that's not possible, how do I add values in a column that also includes cells with #N/A in it?

Um, that would be, for instance, table is A1 to B20, value to be looked up is in D2, column to return is B, formula is in cell C2:

=IF(ISNA(VLOOKUP(D2,A1:B20,2,0),"",VLOOKUP(D2,A1:B20,2,0)))

Whatever is between the quotes is returned, whether it's nothing, or it's text like "NOT FOUND"

The zero looks for an exact match. If you want to look for the closest match, delete the comma-zero from the vlookup formula.

I'm working out percentages of pupils in our school who are above, below or under achieving in all of our subjects. As not all pupils take all subjects I have some blank cells which I need the formula to ignore.

The formula I am using at the moment is

=COUNTIFS(B2:B163,"F",en,">0")/(COUNTIF(B2:B163,"F"))

This is in Windows 7

Thanks

I don't quite get what you're after here. What's in the B column?

Would it be possible to upload a copy of your Excel sheet as it is now, using dummy data if it's at all sensitive.

PS. While it is always nice to have information and too much is better than too little, it doesn't actually matter whether you're using Windows 7, Windows XP, or even a Macintosh. What would be a tad more useful information for this question is what version of Excel you're using.

Hi there

I am currently busy with a project. However I face difficulty in trying to allow formula's to read the value of a specific cell (instead it is reading the formula in that cell).

I have 6 accounts, 3 are NOSTRO (begins with 0) & 3 are VOSTRO (begins with 7). When I select a Vostro account I want the letter V to populate in another cell & when I select a Nostro account I want N to populate. Hence I used a formula in F3 LEFT(E3,1) to retrieve the first digit of the account number (0 or 7). And in cell G3 I used formula =VLOOKUP(F3,NV,2,FALSE) to read the contents of F3 and select either N or V. However my problem is that the Vlookup formula is not reading the 0/7 value in cell F3, its reading the text of the formula...

Please assist.

Welcome to the board.

Either I'm missing something or that's weird.

What do you get for this (which cuts out the "bridging" formula)?:

=VLOOKUP(LEFT(E3)*1,NV,2,FALSE)

It works for me, with the "*1" seeming to somehow force the number "nature" of the first left character.

Hello i am trying to figure out how to have to formulas (or conditions) in one cell. For example I have a cell where i want to have one condition insert 7%, and one condition to insert 5%. The current formula i have now is

=IF(O198<1+O198,7%), but i also want the column p198 to be 5%, and i want the formula in the Q cell. (if cell o198 is greater than 1.00 i want it to insert 7% in column Q, if P cell is than 1.00 i want it to insert 5% in the Q cell. Both columns will not have a dollar amount, only one or the other.

Please advise, i'm pulling my hair out. I know the first formula is correct, but how do i get the second half of it.

Thank you

mmdmalta

Hello,

I am desperately trying to figure out how to select a cell with a formula. For example, I'd like cell [B,i] to have the value of cell [C, 15*i]

The cell formula would roughly look like : =C[15*this_line].

Has anyone an idea on how to do this properly on excel (got excel 2007) ?

Any help would be appreciated !

Take a look at the Indirect function in Excel.

Regards,

Rollin

I am trying to hide rows of my worksheet that show a date in the completed column.

The orders that are complete have a date in column F up to this point I highlight them green so I know they are completed but it would be nice if they highlighted green and were hidden when a date was entered into the completed column.

Can someone assist me with creating a macro that will do this?

I am very new to macros so please forgive my lack of knowledge.

I have attached a sample of what I am doing however inf has been changed.

I am using Excel 2007

Thank you,

Hi, welcome to the forum,

I put some simple code in the Sheet's vba

Make sure you allow macro's to be run

Just enter a date in the last row of column F for testing.

For the other green rows, just click in column F and update the date by retyping it

Hi,

Is it possible to hide the entire row if the formula result is "x". (in Excel)

In my spreadsheet, I have vast numbers of results that show up as N/A. This is the correct answer, but it's making the spreadsheet hard to read because of how many there are. It's easy enough to sort afterwards, but I was hoping a a more elegant solution existed?

Thanks for any help!

I know that this is probably a pretty trivial problem, but I can't seem to come up with the answer. I am trying to set the selection focus to a cell in a column, based on an offset variable. The following is a little macro that I created to experiment with. As written, it positions the selection focus on cell C6. But I can't find the way to set it to C6 + Offset. The only reason for the MsgBox statement is to prove that I passed the argument as expected.

Sub MvTo(ByVal Offset As Long)

Range("C6").Select

MsgBox "Offset " & Offset

End Sub

I thought I could use RnCn cell references and write something like Range("R6[+Offset]C3").Select, but I get an error return, so I'm missing something somewhere. Run-time error '1004', Method 'Range' of object "_Global' failed.

I am using Excel 2000.

=IF(AND(H73>=(H9-H11),H74<=(H9+H10)),"OK","Not to Print")

Using the formula above, when all cells 9 thru 11 are blank, the formula is outputting OK as the solution. I would like the formula to output nothing in that situation and leave the cell blank. How do I achieve this?

Thanks in advance

I have a large workbook (over 4 meg) with multiple sheets and lots of formulas. Today, I was trying to change a cell formula and instead of placing the result in the cell, the formula was placed in the cell and displayed as text, complete with the '=' sign in pos 1. The Evaluation tool states that the cell contains a constant.

The following is an example. The original formula and the mod with the changed component in red.

Original cell formula: =SUM(INDIRECT("T$"&MATCH($V1265,$V$2:$V1265,0)&":T"&ROW())). The cell result is a number.

modified cell formula: =SUM(INDIRECT("T$"&MATCH($V1265,$V$1:$V1265,0)&":T"&ROW())) . The cell displays the formula, exactly as shown, as text and therefore no result.

Has anyone ever encountered this before and is there a solution?

Thanks, Tom

I have a sales report that shows number of leads by sales person and date. It's organized into a monthly calendar view (with dates running down instead of across) and I've used formulas so that I only need to change the year to autopopulate the dates to fall within the corresponding month. What I want to do is only show the actual business days in each month - so auto hide any Sunday or Saturday rows as well as the blank rows (just the rows that have formulas in column A, not the blank spacer rows) so that when I update for each year I don't have to manually hide the rows with no data. Can I do this with VBA code? I'm not sure if I've explained myself well, so please let me know if this doesn't make sense! I've attached my spreadsheet.

For Windows XP, Excel 2010

Thanks!

Hi

I have an Excel 2003 pivot table based on an ODBC query linked to a Sage 200 (accounting software) database which works fine, but I had to add two formulated columns to the data returned by the query because the logic in them is too complex for me to replicate in the query design editor.

When I refresh the query the formulae in the two columns are not automatically copied down past the row which represented the end of file when it was last saved and so have to be copied down manually and I then also have to go into the Pivot Table Wizard to expand the data range.

Although my VBA is (very) rusty I could probably write a few lines of code to automate these two steps but is there a way to do it without VBA?

Thanks

Garry

i have a spreadsheet that updates with data from a number of different sources. i need to filter only the relevant data that i need. i'm almost there but need to find out how to pull 2 more pieces.

in the attached spreadsheet the cells i need are in column R and T. I need to know the corresponding "name" and "size" of the data that is in column S.

unfortnuately the actual values don't show up in the spreadsheet b/c they are live data feeds but the formulas written up until now are still there.

in a nutshell what i'm doing is getting 5 price feeds and i need to filter out which is the highest updated within the last 2 minutes. i actually have the only the price figured out in column S, but i also now which name and how big the size is related to that price.

your help is very appreciated.

regards,

I have an excel sheet with two tables for customers to know which products the customer is using based on a Contract table.

The enclose sheet has two tables:

1. Customer

2. Contracts

All what I need is to fill the columns of "Product A", "Product B" and "Product C" of every customer looking at the table "Contracts" where the data is available based on the "Customer ID"

How can I do it?

I would just use a simple countifs formula to check if the customer ID and Product type match (ie the count of matches is greater than zero)

See attached.

Hi all,

I have a friend who's having a problem with a macro and it's been WAY too long since I've worked much with macros so I told her to post here. She's intimidated by you guys so I told her I'd be her proxy, but I'll send her a link to the thread and try to flush her out if you need clarification. Basically, she has a pivot table which produces a grand total. When the pivot table is refreshed, the grand total cell reference can change (for example, from D16 to F23). She has a formula that finds the reference of the grand total cell:

=CELL("address",INDEX($B:$AZ,MATCH("Grand Total",$B:$B),MATCH("Grand Total",$B2:$AZ2)))

She would then like to have the macro select the grand total cell, based on the reference provided from the formula, so that the macro can continue on using that grand total cell reference. I'm sure I've made a mess of the question, so let me know if I'm not making any sense. Thanks!

What can I use (formula, etc) to check if a cell has a fill color or not? I have thousands of records with some with a yellow fill color. I need to move those that are filled to another sheet.

Hi,

Need some help on the below (Don't know if this has been already addressed)

I have the Formula to find a text in a cell & return the value of another cell corresponding to that row. For this I have used the formula IF(SEARCH("F",R218),S218,0), this works fine if the text "F" is found in the cell but if there are no texts the formula results in a #VALUE, what I am looking for is that in absence of the text it should return zero value. please suggest.

Thx

Ab

Good day everyone.

Basically I need to have a formula in excel that includes auto hide and unhide of rows based on the result value. Attached is my sample exercise for quick reference. In this exercise, I want to hide automatically the rows under "REPORT OUTPUT" that contains "0" ZERO value. Basic guide: Once you enter value (from 1 to 5) in cell D3, report output will automatically calculate...... The missing condition in the formula is to automatically hide ZERO value.... Please help.

Thanks in advance.