I have a problem where I am linking data from two worksheets say: worksheet 1: A1 = 10, A2 = 20 worksheet 2: A1 = =Sheet1!A1, A2 = =Sheet1!A2
I have filled this formula through a certain number of cells on sheet 2 so that if i add more data to sheet1 it will appear in the appropriate place on sheet2. Is it possible to show the formula results in the formula bar, i.e. sheet2: A1=10, A2=20, such that i can used 'Find' or 'Autofilter' on the cell values rather than the formula?
I have a range of names (say A1:A10) and another range (J1:J3) which contains text strings such as "og", "alle", "ins".
What I need to do is to check each of the names in A1:A10 to see if the cell contains any of the values in J1:J3 (an "exclusion list").
For example:
A1 contains the name John Smith, so I'd need to return a value of false as none of the text strings in J1:J3 is contained in that name. But A2 contains the name Elizabeth Allen, so I'd need to return a value of true as that name contains the text string "alle" which is in the range J1:J3.
I could do three different checks using the formula =SEARCH(J1,A1), =SEARCH(J2,A1),, =SEARCH(J3,A1) and then copy those three formulas down A1:AA10. If I only had three exclusions to check on my actual data that's what I'd do, but my real-life exclusion list contains over 50 different values, and it's a bit time consuming to create 50 different search formulas!
I try to make a macro that pastes 'Data'!K4 into 'Data'!L3 and then have that formula functioning in cell 'Sheet1'!A31.
The problem is when I try to make a macro to do this it will always paste the values that were recorded during the macro rather than the unique formula that is created via cell 'Data'!K4 at the time.
Is there any way to have A31 actively using the values that are created with 'Data'!K4? at all times?
One of my longest running problems with Excel is how to use a cell value to point to a reference. If I have three sheets, 'Main' and 'Data 1' and 'Data 2'. In the Main sheet I'd like to be able to have a value of either 1 or 2 in cell A1. Then in cell B1, use the value of A1 to point to cell A1 in either Data 1 or Data 2 sheets.
I've got a sample spreadsheet that I'm trying to get the MAX from a Range of cells that only begin with "TMP". Problem is, if I use MAX or SUM to try to calculate the largest number (or a sum, just to test), it returns "0" for the caluculated range. If I Copy and Paste Values, the MAX or SUM formulas still don't work.
Only if I edit the cell and hit the ENTER key will the Formula(s) work, for each cell in the range that I edit/ENTER. All cells are formatted the same (GENERAL), and I'm not working it into a VBA Macro (just yet).
I have a cell with a fairly long formula for concatenating a large number of other cells. Let's call it cell "A". Due to the formatting and end use of the data in cell "A", I have to copy and only paste the values of that cell into another cell, which I'll call cell "B". What I need to know is whether there is a way for the values in cell "B" to automatically update in the same way they do in cell "A" when data changes in any of the cells that are being concatentated.
I basically have this worksheet which has Cell D3 Being Dynamic. It puts the lowest cost supplier from columns H:W. If i change the prices in H:W it will bring up the lowest cost supplier in D3. Once D3 is chosen i want it to self populate F3 and G3 based off of what is in D3. This data should be pulled off from what is in the H:W columnns corresponding to the supplier in D3.
I am trying to do the same kind of formula with a user form where the user is picking the range for the needed columns. I am not sure how to make it work.
Private Sub test()
'declare variables Dim wb As Workbook Dim iSheet As Worksheet Dim apSheet As Worksheet Dim glSheet As Worksheet Dim x As Long Dim apA, apB, apC, apD, apE, apF, apG, apH, apI, apJ Dim LstAPRow
'set variables Set wb = ThisWorkbook Set iSheet = wb.Worksheets("Instructions") Set apSheet = wb.Worksheets("AP Query")
With apSheet LstAPRow = Range("A65536").End(xlUp).Row End With..............................................
i need a formula that will sum values in Col H based on the criteria set in CELL B1. The tricky part is that i need the summed values to be converted using the FX rates in Cell M:N, i have provided an answer on what the formula should return in CELL C2....
I have two worksheets in the same workbook. In Sheet1, I've got a whole bunch of stuff that I need listed, like names, addresses, phone numbers, etc. What I want to do is pull the values of only the names, which are listed every 42 spots, and put them into a list on Sheet2. So, for example, in Sheet1, A1 is a name, A43 is a name, A85 is a name, etc. In Sheet2, I want to list only the names. And if there isn't a name yet, I want a blank cell. I've sorta figured out how to do it, but I get a problem with the values not incrementing correctly.
So, example code would be =IF(ISBLANK(SHEET1!A1),"",A1) =IF(ISBLANK(SHEET1!A43),"",A43) etc.
The problem is when I drag the auto fill, the next values it gives are =IF(ISBLANK(SHEET1!A3),"",A3) =IF(ISBLANK(SHEET1!A44),"",A44)
is there anyway to get the values to go up like I want them to, or do I have to enter them all individually? That would really not be fun for me, so I hope that's not the case.
In range B4:M4 some cells contain values and some are blank. I am trying to write a formula that will omit the blank values and just give me the nonblank values. The code below returns the FIRST nonblank value, but I can't seem to figure out how to drag it across a row to get the others. I want to ensure that the numbers stay in the same order (so no largest/smallest formulas), but the blank cell values need to be removed.
This would then need to be repeated for (B5:M5, B6:M6, ETC).
The "Jul" which stand for July, it is possible to have a formula where I can have certain text in a formula that is based on the value in B1 (In this case, B1 being JUL in bold writing.)
Like this;
=('Input &B1 2014'!$BI$3)/1000
Or must I manually go in and replace all formulas for each month?
I am designing a spreadsheet for my colleagues and I have run into a problem. Range U16 tu U and the first blank row has values TRUE or FALSE. The problem is you will never know if it's U16:U21 or U16:U90. The thing is to come up with a formula (no vba) that will count all TRUE values in that range. I found a formula on the internet to find the first blank column: =MATCH(TRUE,INDEX(ISBLANK(U16:U300),0,0),0) + 16 - 1
The formula works great and if I put it in, say, cell P2 it will return the correct result.
Now, the cell K2 has the following formula: =COUNTIF(U16:U300,TRUE). It Counts incorrectly. I do not want it to run to U300 but to whatever P2 returns. How can I do this?
I have a spreadsheet that give me the percentage difference of two cell say a1 and a2 (=a2/a1)[format as % two decimal places]. The result is on say b5 as a %[format as % two decimal places]. Now on B6 I want to do this:
if B5 is >3.01% then b6 =" Market Test Required" if B5 is +3% then b6 =110 if B5 is +2% then b6 =106 if B5 is +1% then b6 =103 if B5 is 0% then b6 =100 if B5 is -1% then b6 =96 if B5 is -2% then b6 =93 if B5 is -3% then b6 =90 if B5 is >-3.01% then b6 ="Market Test Required"
So on and so on.... I would really appreciate your help on this issue.
I have used excel for some time now but not with complex formulas or any vb.
I have the need within a spreadsheet to automatically compare the value of 2 cells and if they match exactly to move the cursor to a group of cells. If they do not match then move to another group of cells. The only way that I can see to do this is via Macros. However I still need to do the automatic compare first.
I'm quite a novice at Excel. I have a column of values that I sum as follows;
A 0 0 0 0 0
0 <----------------sum of A1:A5
A formula may change one of the values in the above column to a '1' which means the sum will become '1'. The sum can only be '1' or '0' and only one value in the column will ever change. I need to add a value of 2 to another cell (say, C1) when the sum of A1:A5 changes from a value of '0' to '1'. I know this will probably involve the worksheet change event but am having a problem implementing it.
1. Using a formula, I am trying to to obtain a list of unique values (string) (caveat: see #2) from the range E2:E10000 (arbitrarily chose 10000 - the row number is variable)(see #3).
I currently have a formula that seems to work for this purpose but I don't know how to add the condition in #2 (below)
2. To include all unique string values except those starting with the letters "IC"
3. Is there a way to make this formula so that it can only seek values up to the last row, and not go to the 10000th row if not necessary? The E column has no empty cells until after the last row that contains data.
Here is the formula I currently use which serves #1 (above):
[Code] .....
Any way to improve/simplfy this formula for the purpose describbed in #1? How can I add the condition in #2? Can you see a way to include #3? The most important issue here is #2.
Example of desired results:
Column A | Column B AA | AA DD | CC AA | DD CC | DD | DD | IC | IC |
Is there a formula that would allow you to take the average of all values within a range but not count the zero values? I thought something like this might work but it's not. Neither one worked.
Trying to find the sum of all cells in the array described in the formula that are equal to the values inside the quotations. I used this exact (as far as I can tell) formula to find the sum of values that were NOT equal to my quoted values and it worked just fine. Any ideas why formula 'A' will not work but formula 'B' does work? I have a feeling I'm missing something simple here!
Formula A - Does not work: =SUMPRODUCT(--('Master Lead Sheet'!$J$2:$J$10000=$B2),--('Master Lead Sheet'!$N$2:$N$10000="REJECTED"),--('Master Lead Sheet'!$N$2:$N$10000="CONDITIONED"),--('Master Lead Sheet'!$N$2:$N$10000="APPROVED"))
Formla B - Works: =SUMPRODUCT(--('Master Lead Sheet'!$J$2:$J$10000=$B2),--('Master Lead Sheet'!$N$2:$N$10000"No Answer"),--('Master Lead Sheet'!$N$2:$N$10000"Disconnected"),--('Master Lead Sheet'!$N$2:$N$10000"Wrong Number"),--('Master Lead Sheet'!$N$2:$N$10000"EMAILED"),--('Master Lead Sheet'!$N$2:$N$10000"needs to be emailed"),--('Master Lead Sheet'!$N$2:$N$10000"Refund"),--('Master Lead Sheet'!$N$2:$N$10000"REFUNDED"))
I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.
I have one column of data. there are currently ~10k rows, but this will increase over time. each row has either a single value (example: pepsi) or a comma separated value (google,samsung) with up to 6 commas. instead of the rows looking like this:
pepsi google,samsung coca-cola
I want them to look like this in one column (preferably via a formula):
pepsi google samsung coca-cola
I would like the above output to be a unique, alphabetized list.
write a script to match a cell value then copy and paste the matching row across two worksheets - "Master List" and "Demographics".
These two worksheets have the same cell values in Column A, the key difference is the Column A cell order in the second worksheet "Demographics". This Column A cell order is set arbitrarily by another system and will vary over time.
I would like to match the rows, then paste the three columns from the second worksheet into the first worksheet.
For example:
"Demographics" Worksheet Cell A2 = "Master List" Worksheet A45 - then copy cells A2, B2 and C2 and paste into A45, B45, C45 respectively.
"Demographics" Worksheet Cell A49 = "Master List" Worksheet A12 - then copy cells A49, B49 and C49 and paste into A12, B12, C12 respectively.
I have created a sample spreadsheet here; - List Schema.xlsx
I request you to write a code for me to fill the cell values as "Not Applicable" in Column "AZ", if the "B" Column cell values = "Justified", "Approved LSAR" & "Approved SDAR".
I have attached the work book of what I am trying to accomplish.
