I am trying to find a way to have a cell look into a group of other cells and display the first available things it comes to. Then have the next cell look in that same group and display the next item.
cells A1:A5 have 3 pieces of information in them scattered among that column (A1, A3, A5 might have the info in it one day, then A2, A3, A4 the next day)
I want B1:b4 to find the info in the A1:a5 and display it in order as it appears in the A column.
A board member helped me with a macro, but my example had the wrong columns, and I did not know how to adjust the macro.....
I have 3 columns.
Column 'W' Needs to return a value derived from 'fourth and fifth character' of Column K.
Column 'X' needs to return a value for the Sixth and 7th Character of Column K.
Column 'K' is a 7 Alphanumeric part #. example AI3-HDSS.
(first three alphanumeric characters change, but are not relevant.
The 11 combos are all the combos in the spreadsheet.
Col. Y
Col Z
Col AA
Col AB ...
code is pulling data for forecasts for the following 10 days, and the code is the following:
WHERE (((dbo_ACTUAL_HDD_DAILY. DATE)>=Now()-1 And (dbo_ACTUAL_HDD_DAILY.DATE)<Now()+9)
All he wants modified is to pull data for the entire current Month (Ex. if it is in the middle of July, he would want the data from July 1-July 31, or if February from Feb 1-Feb 28) It would be nice to do this without having to change the VBA every month.
I am building a sheet of sales targets for 2009. With each month allocated a certain percentage of the annual target.
I wish to be able to take into account a change of target at some point in the year.
If i were to change the annual target in June, i need the spreadsheet to only change the monthly targets from June onwards, January - May are finished.
In the example attached there is a change in annual target in June. How do i calculate what the remaining month's targets need to be in order to meet the annual target while taking into account what has already been achieved and the shape of the budget as indicated by the percentages??
I'm trying to determine how to indicate which month an adjustment will post to an invoice.
Column A= billing cycle date
Column B= Market
Column C= Adjustment Approved Date
Column D= Adjustment Amount
Column E = Which invoice will credit post to:
So I'm trying to build a formula in Column E that will look at the cycle date in Column A compared to the Adjustment approved date in Column C and then kick out which invoice the adjustment will appear on. The values in Column E were placed mannually to show what I'm trying to accomplish. if the adjustment approved date is = to a cycle date it will show up on the same invoice. ie if approved on the 1st and the cycle date is the 1st the invoice will reflect the approved adjustment.
ABCDE1Cycle Day of MonthSales MarketAdjustment Approved DateAdjustment Amountposted invoice21Salt Lake12/15/2011-$1,300.00Jan '1232Denver12/22/2011-$3,802.01Jan '12411Atlanta1/12/2012-$5,292.00Jan '1255Dallas1/23/2012-$6,000.00Feb '12628New York2/1/2012-$5,000.00Feb '1272Denver12/5/2011-$500.00Jan '1283Seattle2/4/2012-$440.74Mar '12912San Diego1/4/2012-$500.00Jan '12101Phoenix1/17/2012-$257.87Feb '12112Denver1/18/2012-$1,220.92Feb '12123Seattle2/5/2012-$911.03Mar '12134Spokane1/30/2012-$20,391.86Feb '12145Dallas12/6/2011-$45.63Jan '12151Phoenix12/7/2011-$7,176.14Jan '12
The code below puts a green border around the cell that is beneath 10 in my chosen range, however I wish to add the border to the row of information instead of just the cell. My columns of data are from columns E to M, but the criteria for whether or not the data gets a green border is in column D....so lets say D15 is less than 10, I would want a border to go around E15:M15.
Sub Test()
For Each c In Range("D2:D350")
If c < 10 Then
c.select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
I need to paste a picture from the Clipboard to my Worksheet. I select the origin and paste it with the macro.
I need to adjust that picture to fit in a defined space from left corner of Range($J$10) to the right corner of
Range($BJ$35)
Actually, i'm using this procedure
ActiveSheet.Unprotect
RANGE("graphique_PL").Select
ActiveSheet.Paste
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 358.25
Selection.ShapeRange.Width = 725.
The problem with it is, the Height and Width is arbitrary to the size of the cells at the moment. I would like to had a procedure to calculate does value. They represent the distance between the defined cells location for the image. Actually, if cells width or height change, the picture is misplaced.
Im having alot of difficulty preventing the result FALSE when one or more of my >20 count within an index table doesnt have a result to display.
Is there anyone able to understand the following? That can perhaps provide a solution that returns no FALSE word??
=IF(ISERROR((VLOOKUP($A22,'C Number'!$A:$N,B$1,0)))=FALSE,VLOOKUP($A22,'C Number'!$A:$N,B$1,0))
Ive tried ISNA but I always get an error appear when i try to use it, perhaps you could edit the command above so that ISNA works whenever FALSE is the result?
I have Office 2007 and i use this code on my word.docm to insert selected photos. the problem I'm having is that it insert photo at top of page. can additional code be added so that it will insert photo in same table as command button. and in front of button, so that it will hide button
Private Sub CommandButton1_Click()
Dim sFileName As String
Dim ilImage As InlineShape
With Dialogs(wdDialogInsertPicture)
.Display
If .Name "" Then
sFileName = .Name
Set ilImage = ThisDocument.InlineShapes.AddPicture(sFileName, , True)
With ilImage
'set any additional properties such as left, top, etc., here
End with
Else
Exit Sub
End If
End With
End Sub
when "Update"(code is under "Update"button) button is pressed to copy the data from userform to the database sheet exactly into columns where both column heading match, for example if userform has heading "Qty Received " all data from that column should be in the database column with the same header "Qty Received"
I attached my file when you will open the file you will find screenshot how it should look.
Have this formula which works fine for finding the largest sequence in a list. (c/o Domenic from [url]
=MAX(FREQUENCY(IF('Overs-Unders'!B3:B1827"",IF(ISNUMBER(MATCH('Overs-Unders'!B3:B1827,{0,"n/a"},0)),ROW('Overs-Unders'!B3:B1827))),IF(('Overs-Unders'!B3:B1827="")+ISNA(MATCH('Overs-Unders'!B3:B1827,{0,"n/a"},0)),ROW('Overs-Unders'!B3:B1827))))
Now i need to:
(a) from the cells B5:CC5 that this formula runs through find the highest figure and return the name in Row 1 of that column.
(b) adjust above formula to get something now that ignores any run that contains 7 or more consecutive "n/a"s
(c) get a formula that counts the latest run. eg. from the bottom up (at the moment data only goes down to row 200)
Using the search macro code below, could someone please help to add in more codes what I'm currently using, and also where to insert it. The Search function works well for what I need and it helps me to locate data. When using the search function somehow it search all sheets within the workbook but I only want it to search an array of sheets when using this macro that is needed to complete the task for what I'm after.
Macro
Public Sub FindText()
'Run from standard module, like: Module1.
Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer
myText = InputBox("Enter the text that you want to search for:", "Start Search!")
If myText = "" Then Exit Sub...................
Is it possible to modify the attached code so that it will copy bold text and border as shown in attachment sample1 and paste in sheet Shop. Currently the code just copy's and pastes without bold text and borders.
Sample1.PNG
I've adjusted a jonmo code to add an item in col B which is not in col A to the bottom of col A. - fab code, thanks jonmo.
But.. i want to:
insert rows beneath those in column A to accommodate the added items and shade those cells in list A once they been added ( so the users now they've been moved )
I've posted the code below ( including my attempts at colour change where it shade the right cell but in the wrong column ) ...
Assume I have a cell M24 with a formula like
=M10 + $H24 - $I24*0.35
As you can see B10 is a fix reference (due to omitted $) which should NOT be auto-adjusted but be kept.
Now I want to copy the formular to lots of cells below cell M24. therefore I mark cell M24 and click copy in context menu.
Then I drag/expand the blinking cell border to lets say the 20 cells below. As I result I expect e.g. in cell M25 a formula like
=M10 + $H25 - $I25*0.35
Unfortunately I got
=M11 + $H25 - $I25*0.35
So the fix reference is adjusted as well.
How can I tell Excel 2007 to NOT auto-adjust fix references in formulas?
I have two worksheets. Sheet 1 has 2 columns, Column A the restaurant's name and Column B contains the review score. So sheet 1 is kinda like this:
Restaurant |Score
Ruby Tuesdays 80
TGIF 78
Outback 92
Sheet 2, Row 1 column B-E contain restraurant names (only on the top row, like field names).. i.e. I manually put the date in because typically the projected date is different from the actual review date.
-A----------- B ----------------C ------D-------- E-----
Date |Ruby Tuesdays|Olive Garden|TGIF|Ruths Chris|
I need the data from Sheet 1 Column B moved to sheet 2 in the next open row (i currently have data in row 1..the field names and down to row 35). This will be continuous so each time i need it to add the score as a new row in the correct field (restaurant name), IF the restaurant isnt listed, I want a new field named with the restaurant name and then place the score in the correct row and column. So, in the example I'd need Outback added.
I have looked at previous v lookup questions however was unable to do a comparison to the queries which I have. Hope someone is able to help. Sending spreadsheet to hopefully clarify
Sheet 1 = downloaded orders
Sheet 2 = present Customer database
Q1 - sheet 2 column E - can I make the address show without the return stroke (square symbol)?
Q2 - how do I return in sheet 1 column b and c the information held on sheet 2 column b and c. I have tried using the post code as the look up but it is only returning around a 30% find, can you use post code and rest of the address (post codes could be partially different as off 2 independant databases) to find a true match, or at least increase the 30% find considerably.
I'm kind of rusty with spreadsheets and Excel 2007 is entirely new to me. I'm not even sure what I'm trying to do would be called.
I have a spreadsheet that is a list of records; a name, ID number, one text, and four numeric columns per record.
I would like to make a set of buttons or something that will automatically do a custom sort. Basically a "sort by this criteria, sort by different criteria" etc. so I don't have to manually do the sort repeatedly.
i want to use for searching a name in a colum. And copy the row of this name to another row.
I want to use this because i want to change an format to one i use all the time
person Astreet awork a
person Astreet bwork b
person bstreet cwork c
This is the situation: i want to search for person A and copy the data of the row , so copy street a. and work a. to another row
And i want to do the same for person b and so on until person z
I have a formula that looks in 2 columns for criteria and then does a count if both sets are matched.
=SUMPRODUCT(--(data!$AC$1:$AC$10000="Y"),--(data!$AV$1:$AV$10000='Summary Sheet'!B9))
Is it possible to do a search with 3 criteria?
I need a search where a third column has also to match. eg data!$AW$1@$AW$10000="Y"
Is this possible and if so what would my formula now look like?
I have a spreadsheet with multiple coulmns of data with rows which equate to each site location.
Basically I am after searching one column which says "Earth Rods Fitted" which is located in Column K which goes from K4:K767
In each the rows its either yes or no answer.
So the search would count number of "no" entries in column K.
But then it would search also on column Y (Y4:Y767) for a range of values of risk assessment.
So in column Y you could have each row with different assessment risk score from 0-300
So my search would need to count coloumn K for no earth rods fitted and then count within this range the number of cells in column Y which have risk score say between 200 to 400.
Column K Coulumn y
Earth Rods Fitted Risk Score
No 350
Yes 55
No 222
No 90
So in above case there is 3 enries with no earth rods in column K and in Column Y we are counting rows which have a value in range of 200-400 which above there is 2 entries. So basically I know there is 3 sites needed doing and my worst to based on risk is the two which are 350 and 222.
I have messed around with COUNTIF functions which I can search column K ok, but doing the range search on y in conjuction with K I am finding hard. Someone mentioned use Vlookup but not sure how to do it.
i have is that at current i have a load of data (6000 cells worth)
What i need to do is to go through the data and highlight anywhere where it may have the word "in/outsourcing" (The data is based in cell AA which is just a description of text)
Other than doing Ctrl F through each cell is there a faster way in which excel can search through the cells and even highlighting the cell where the word occurs.
I thought if the word "sourc" is searched this would then pick up both
I have a sheet which have 20000 lines of row of data populated with data from column a to column n.
I need a formula or macro to search under column F for repetition of same data and to be copy the information of the row to a new sheet.
I have a excel spread sheet that has 30 rows and single column(like A1,A2,A3....A20).I have to loop thru all these row values one by one and search for matching values in another spread sheet.If it mathches take the second column and third column values in the same row and paste it my spread sheet in the fourth column and fifth column.and put yes in the sixth column.Go to next row and do the same.Repeat this for all 20 rows .How to do that?
View 9 Replies View RelatedI have attached a small example. I have a list of data of employees. I want to be able to input a number (in column A in example) and to search the data records for this number. When the number has been found, the corresponding info data from that Row will show in columns B,C & D.
I have tried this using LOOKUP etc but find that it is "hit & miss". I can input one ID number and the corresponding details will appear, but very often if I enter any other ID numbers further down the sheet I sometimes get the correct data or I might get the "N/A" error. The error seems to occur, I think, if the next input ID number is higher than the last. The ID numbers I input down column A will NOT be in numerical oredr.
designing a macro, which can compare the sheet1 and sheet2 data (exclude E and G columns) and find duplicates rows of data in sheet1 and sheet2. The output after the macro, would be show duplicates found in sheet1 and sheet2, through highlighting the rows.
attached file for the sample data:
output_data.xls
Attached is a sample of what I need completed.
Monthly, I have to do a chart just like this except slightly more complicated.
In the Sample download, there are three charts, "Sample Chart", "Sample Input", and "Desired Result".
"Sample Chart" has a list of accounts from different companies, The first column being their number, the second being their name, and the third being the money they spent.
The "Money Spent" Column is always blank when I start for ALL companies.
I have another chart, "Sample Input", which contains the prices that I'm supposed to put in "Sample Chart, Money Spent" column.
The thing is, "Sample Input" only has the companies with prices listed.. Not all companies have prices, so this means the "Sample Input" is always a shorter list than "Sample Chart".
What I need is the prices from "Sample Input" to be put in the correct position in "Sample Chart". The "Desired Result" chart is what I want it to look like.. exactly like that!
When I do this monthly, I have to scroll through several thousand accounts doing this.
Suggestions:
- Possibly have a macro or formula take the Account # in "Sample Input".. Sample it in "Sample Chart", then copy the price and paste it in the right location.
- Possibly make "Sample Input" have blank rows inserted in the places where it should have the account with no prices.
I am looking to search in a table (say 4 columns) corresponding to multiple criterion (one for every column except fourth) and returning the values which are numerous (from column 4). I have tried the INDEX function but it only gives me one of the many cells. I am working on a table with +20000 cells per column
View 3 Replies View RelatedI'm trying to search & match data from two different spreadsheets. I will attach my workbook for reference.
The first worksheet is a list of all of my clients I have previously worked with and the second worksheet is a list using a set criteria. The criteria I am using is the UK postal code "AL10".
The clients address (Column B) will be used as a reference to match the address which is located on the AL10 worksheet which is also column B. If there is a direct match then a VLookup function will be performed to display something that can be easily referenced.
The problem I am having is that the address format is different on the clients worksheet then what it is on the AL10 worksheet. I have the feeling I will need to create a search function with multiple arrays but I have limited knowledge of how to do that.
There are some additional notes located in my workbook.
I know that two of the client addresses should match data located on the on AL10 worksheet and the other two shouldn't give a match at all as they don't exist. These are highlighted in yellow.
I have used the Find and replace function to do this but this is rather manual and slow and I would like the search feature to automate this process.
Attachment 308707
I have put a formula in excel to count how many times the word 'administration' appears in a column:
=COUNTIF(K2:K99,"Administration")
Unfortunately, the output that I am searching has mulitple words in it, separated with a colon and no space. My formula skips the count if the word Administration is not completely on it's own
e.g. Administration counts 1
Administration;Cardiology does not count