Over 1 million tech questions and answers.

Solved: Can I use the value of a cell in a formula based on row and column co-ordinat

Q: Solved: Can I use the value of a cell in a formula based on row and column co-ordinat

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).

Read other answers
RELEVANCY SCORE 200
Preferred Solution: Solved: Can I use the value of a cell in a formula based on row and column co-ordinat

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.)

RELEVANCY SCORE 98

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?
 

A:Solved: sequential row numbering based on value in another column/cell

Read other 7 answers
RELEVANCY SCORE 88.8

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
 

A:Move data from column to row based on value of cell?

Read other 6 answers
RELEVANCY SCORE 84

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
 

A:Excel macro or formula - change field based on other cell

Read other 7 answers
RELEVANCY SCORE 76.4

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


 

A:Solved: Clearing Cell Contents Based off other Cell

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
 

Read other 2 answers
RELEVANCY SCORE 74.4

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.
 

A:Solved: Macro that overwrites info in 1st column based on info in a 2nd column

Read other 7 answers
RELEVANCY SCORE 73.2

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.
 

A:Counting strings in one column based on Condition in another column in Excel

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
 

Read other 3 answers
RELEVANCY SCORE 72.4

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.
 

A:Solved: update the column in one table based on the column of another table

Read other 16 answers
RELEVANCY SCORE 72.4

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.
 

Read other answers
RELEVANCY SCORE 71.2

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.
 

A:Solved: adding row and column in same cell

Read other 9 answers
RELEVANCY SCORE 71.2

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
 

A:Excel Total in a column based upon value in another Column

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.
 

Read other 3 answers
RELEVANCY SCORE 70.8

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.
 

A:For each matching cell in column A, evaluate Column B value

Read other 6 answers
RELEVANCY SCORE 70.8

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!
 

A:Solved: Select some listings based on one column - need VBA

Read other 7 answers
RELEVANCY SCORE 70.4

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.
 

A:Solved: Referencing Cells Above the Last Cell in a Column

Read other 15 answers
RELEVANCY SCORE 70.4

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)
 

A:Solved: Excel VBA Loop Until Last Cell in column

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.
 

Read other 2 answers
RELEVANCY SCORE 70

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"
 

A:Solved: Formula to read last line in column for excel?

Read other 8 answers
RELEVANCY SCORE 69.2

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.
 

A:Solved: Access07 - Count records based on column text

Read other 10 answers
RELEVANCY SCORE 68.8

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
 

A:Solved: Two formula in one cell

Read other 7 answers
RELEVANCY SCORE 68.8

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
 

A:Solved: VBA formula in cell

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
 

Read other 1 answers
RELEVANCY SCORE 68.4

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

A:Solved: Macro to copy text from a cell to a column with specific criteria

Read other 8 answers
RELEVANCY SCORE 68.4

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?
 

A:Solved: two formula in single cell

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.
 

Read other 3 answers
RELEVANCY SCORE 68.4

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.

Read other answers
RELEVANCY SCORE 68.4

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
 

A:Excel - Copy paste cell into range based on another cell

Read other 12 answers
RELEVANCY SCORE 68.4

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.

Read other answers
RELEVANCY SCORE 67.6

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
 

A:#DIV/0! Error How do I get formula to skip a cell where the formula returns a sum 0?

Read other 8 answers
RELEVANCY SCORE 67.6

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
 

A:Solved: Two separate formula conditions in one cell

Read other 13 answers
RELEVANCY SCORE 67.6

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?
 

A:{SOLVED} Lookup formula that changes #N/A to blank cell

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.
 

Read other 2 answers
RELEVANCY SCORE 67.6

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.
 

A:Solved: Cell content reflects formula not value

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.
 

Read other 3 answers
RELEVANCY SCORE 67.6

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 !
 

A:Solved: Excel : selecting a cell with a formula

Take a look at the Indirect function in Excel.

Regards,
Rollin
 

Read other 3 answers
RELEVANCY SCORE 67.6

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
 

A:Solved: How do I ingnore a blank cell in a formula?

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.
 

Read other 1 answers
RELEVANCY SCORE 67.6

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)
 

A:Solved: Emailing from Excel Based off Cell Value

Read other 16 answers
RELEVANCY SCORE 67.6

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?
 

A:Solved: countif based on cell format, not value?

Read other 10 answers
RELEVANCY SCORE 67.6

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
 

A:Solved: Hide Columns based on value of cell

Read other 9 answers
RELEVANCY SCORE 67.2

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,
 

A:Solved: Macro for excel 2007 to hide rows based on any data entered in a column

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
 

Read other 3 answers
RELEVANCY SCORE 66.8

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!
 

A:Solved: Hiding rows based on formula result

Read other 13 answers
RELEVANCY SCORE 66.8

=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
 

A:Solved: Excel - need formula to leave cell blank

Read other 7 answers
RELEVANCY SCORE 66.8

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
 

A:Solved: Excel 2003: Formula Cell Becomes Text After Mod.

Read other 7 answers
RELEVANCY SCORE 66.8

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.
 

A:Solved: Set cell selection based on a variable offset?

Read other 6 answers
RELEVANCY SCORE 66

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
 

A:Solved: Excel Pivot Table (based on query with formula)

Read other 16 answers
RELEVANCY SCORE 66

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!
 

A:Solved: Auto hide rows based on formula results

Read other 7 answers
RELEVANCY SCORE 66

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
 

A:Solved: Formula to find a Text in a cell & return zero value in it's absence

Read other 6 answers
RELEVANCY SCORE 66

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!
 

A:Solved: Excel macro: selecting a cell from a formula reference

Read other 12 answers
RELEVANCY SCORE 66

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.
 

A:Solved: Excel: Formula to check cell color highlight?

Read other 8 answers
RELEVANCY SCORE 66

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?
 

A:Solved: MS Excel / Filling a cell with YES based on data from another sheet

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.
 

Read other 3 answers
RELEVANCY SCORE 66

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,
 

A:Solved: excel - data returned based on the value of a specific cell

Read other 10 answers
RELEVANCY SCORE 65.6

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.
 

A:Solved: Formula with auto hide and unhide rows based on result value

Read other 16 answers