Grabbing Data (figures) From Different Worksheet?
May 23, 2014i am currently constructing a table on excel and need it to grab a figure which is constantly changing from another worksheet.
View 2 Repliesi am currently constructing a table on excel and need it to grab a figure which is constantly changing from another worksheet.
View 2 RepliesBasically I have 3 Worksheets (well I have more but lets not go into that)
- Worksheet A
- Worksheet B
- Worksheet C
Worksheet A has alot of data in rows:
Name, Amount, Sort ID being the main items
Worksheet B has a lot of named cells:
DATA NAME1: Cell A2
DATA NAME2: Cell B2
DATA NAME3: Cell C2
Worksheet C also has a lot of named cells:
DATA NAME1: Cell A2
DATA NAME2: Cell B2
DATA NAME3: Cell C2
etc
Essentially what I need to do is retreive "Name" values from worksheet A if the Sort ID on that row = 1 and insert that value in A2 on Worksheet B, If A2 is full then into B2 and if B2 is full in C2 etc.....
and do exactly the same for Worksheet C, but only for the "name" values where the sort ID = 2.
Now i am not a lazy programmer but i am not very good either. So I have made a Excel file that you can download and view and maybe one of you guys will be nice enough to download it and show me the light. Please excuse the data contained here its just made up but i think it brings the point across.
P.S DATA NAME cells in Worksheet B & C must be horizontally aligned on the top row.
This seems simple but I can't figure it out. I want to grab the name of a worksheet and place the name in cell A1.
View 9 Replies View RelatedI am seeking a method of collecting data from one sheet in a workbook and displaying it on another, based on a search.
e.g.
Sheet1
Garcia Joe 11111
Garcia Matt 22222
Hernandez Efrain 33333
Matias Jorge 44444
What I want to do is to have Sheet2 be a query for Sheet1--that is, in a cell in Sheet2, the user can type 'Garcia' into a cell and thus display the matching entries from Sheet1, e.g.
Sheet2
'Garcia' displays
Garcia Joe 11111
Garcia Matt 22222
My code inserts charts and then assigns them data series. The problem is that sometimes it likes to grab 5 or 6 extra sets of data series and throw them in there too. I've done some research and it seems that Excel likes to guess what data is going in to a chart when you first add it in and grab some of that, rather than just waiting for me to assign it the data. The sheet I'm doing this on has multiple sets of similar data along row 1, the code crawls row 1 and creates a new chart for each set of station data it finds.
Code:
Sub Make_Chart()
'create bar chart
'prep
Set chrt = ws.Shapes.AddChart.Chart
'add and format chart
[Code] ........
Example data set below. RefCell is set to the top left cell (4.02), n is the length of the raw data in rows.
4.02
ON
OFF
2
0:36:10
0:38:03
0:01:53
[Code] ..........
I have a late report due @ work.... I have people who have worked for 4 days to get me numbers i need and they are not right.....
Anyways I have a spreadsheet with a whole lot of numbers on it... I need to add 30% to each number. What is the easiest way to set this up.
I am in the process of creating a spreadsheet to document employee holidays taken over 2014. Each month is shown on a different worksheet with a 'Total' column on the far right which calculates the number of holidays (represented by 'H') taken by individual employees. The formula I have used to achieve this is =COUNTIF(D6:AH6,"H").
I have transferred the figures in the 'Total' column across the other worksheets, however I now need to add any further holidays taken to the figure that has been carried over from the months previous.
I have added a couple of images. In January, the total number of days leave taken by Thompson is 6. This figure has been carried over onto the February worksheet as shown, however if I place more 'H's' on the same row then I need the total column to reflect both these and the ones from January.
Jan.JPG
Feb.JPG
I'm trying to to take a picture from a Worksheet and save it back to file in a Windows folder via macro.
I've been searching thru Excel help and internet and I didn't see any solution.
I have a worksheet with monthly sales figures by associate and by store. The store has a monthly goal as do the associates. If the store hits it's goal then the overall sales total is multiplied by 1% and then divided by the percentage of each associates involvement to reach that goal. (ie...150,000*1%=1,500, John sold 35,000=23%, so John gets $345 extra commission). If Johns goal was $25,000 and sold $35,000 he gets 1% or $350 commission. In turn, if he meets 1 or both sets of criteria those will be added together. If he doesn't meet either one then the result is Zero.
I have the store goal and Johns goal in separate cells to reference against. The actual sales cell is formula based.
This is basically what i'm trying to do:
If criteria 1 is met then % of 1% of store goal, if criteria 2 is met then 1% of individual goal, if both are met result1+result2. if neither is met then zero. I think?
I've attached a sample sheet to this message.
What I'm trying to do is grab data from the raw data sheet and paste it into the master sheet based off of certain criteria. I want to list any accounts that are not correct.
For example, I want to look through the Raw Data sheet, find the account that contains campaigns which are not correct, and then list the name of the account on the Master sheet. I only need the account name listed once on the Master sheet.
Sample Data.xlsx
DATE
PRODUCT
BALANCE
1/2
KEY CHAIN
4
1/2
KEY CHAIN
8
1/2
KEY CHAIN
27
[code]....
How to get final figure each product from sample data above:
the result:
Key chain = 27
Gift = 4
Hamper = 1
I receive monthly expenditure returns from different departments which I have to consolidate. The problem I'm encountering is that some departments submit their data as a monthly figure and some as the cumulative position. It would be useful if I had a formula that identified what data type was submitted and from that calculate both the monthly and cumulative figure.
If you look at the attached example I'd like to input a formula in columns I and J that uses the data contained in columns B to E to calculate the monthly and cumulative expenditure figures.
I have a file which included monetary amounts in many currencies, which appear in Excel 2007 as:
232.44 EUR
103,796.10 CZK
18,071.08 PHP
I need to use the value and currency code, but unfortunately the currency code is part of the cell format (so I can't use, say =right(3) to grab it.
The cell format is set to: #,##0.00 "EUR" , with each cell set according to the currency within.
Of course I will be going back to the source to see what I can do to get something more useful directly, but is there any way I can grab the currency code so I can use it ?
I am copying data from a webpage and pasting it into Excel, unfortunately the copy/paste is grabbing some "objects" from the webpage. I am running a macro to format the data and as part of that need to delete the "objects" (which can be as many as 40).
Here is the code I'm using for this particular function:
I have a number of image controls on a user form, each being loaded with an image from file.
Image1 being loaded with a picture of a bus image2 being loaded with a picture of a school
And so on
Now what I am trying to do is when that image is clicked the name of that image is passed to a textbox
So the action would be
1 image of bus loaded into image control1 , the name of that image being bus.jpg
2 image is clicked
3 the name bus.jpg appears in a text box
Here is what I have so far
Code:
Private Sub Image1_Click()
DataInput.TextBoxItem.Value = bus.jpg
DataInput.TextBoxItem.Value= Image1.Name - this does not work
End Sub
Which works but as you can see this is static, as I have supplied the value , were as I would like the clicking action to pick up the name of the jpeg being displayed
So expanding upon this if say image1 is loaded with say a picture of a car instead of a bus
LValue4 = "E:Car.jpg"
Image1.Picture = LoadPicture(LValue4)
Now when it is clicked the name of the image being displayed in image control1 would be Car.jpg
And it is this value I wish to transfer to a text box for now will do more with it later
In addition to this I am not sure if the best idea is to have a number of image controls and load each with an individual picture or have one image control and swap the picture by clicking a command button
I need to indicate if the figures for this week has gone up or down compared to last weeks figures.
So looking at the attached, I need to add a column (I5) to sheet Admitted Patients WE 20081005, so it compares the % treated in <18 wks figure by specialty to previous weeks figures on sheet Admitted Patients WE 20080921.
Ideally I would love an arrow either point up if the figures has increased or down if they have reduced.
I need to produce three different formats of accounts data monthly. I attached a file with each accounts description in Columns A, E and G. Is there a way to match the data with those accounts name in a faster way than manual linking?
I would like the last 6 figure in cell a1 to be highlighted cut and pasted into B1 - the amount to be cut will vary
View 4 Replies View RelatedI am trying to make an inventory cover page - that shows data of separate products on different sheets with the following;
Product
Date
Previous Total
Amount Taken
New Total
and occasionally there is a Re-issue of stock to the previous total.
This is at the moment an historical record as anyone can see the usage across the dates for any particular product by looking at the sheet. Each time there is a change to the inventory it is recorded on a new row. But what I am trying to do is to copy the last row of data from each sheet to the cover page to show an overall inventory.
The part where I get stuck is making the selection of the last entered row of data [from each sheet] automatically updating the coverpage, when each sheet will get new data added frequently.
I thought about somehow making a duplicate of the last row and locking it in place and linking that to the coverpage - but I still do not have a clue on how to get it to automatically select the last row of the data.
Dim figure from the cell b4,b5,c4,c5 to accumulate add total b11,b12,c11,c12, respectively.
View 5 Replies View RelatedHow to make space between the figures ex:11480831525 i want like: 11480 831525
because i want to do it for thousand of transactions in one time by using only one formula.
I have the following ...
.Offset(3, 0).Value = "For " & P & " numbers there are " & Format(tly, "###,###,##0") & " x different values of " & cmb & " numbers. "
... which includes figures upto and including millions.
The thing is that the above code ONLY produces figures that are relevent.
I want to calculate the sum of a column but I have 4 lots of figures going down the column and I only want it to calculate the some of 2 of those 4.
The 4 figures: £4.80, £3.80, 31 & 25.
These 4 figures are within the column multiple times and I just want to have one cell that sums up the £3.80's and £4.80's within the column and then another cell to do a separate sum of the 25's and 31's.
Is this possible, if so how? Does it require an IF SUM formula of some sort?
I have a large number that has 6 figures.I want to display this number to 3 significant figures on the spreadsheet, but I have more formulas that need to use the unrounded number.So is there a way to DISPLAY the number to 3 sig figs whilst keeping it as the complicated number for the rest of the formulas?
View 6 Replies View Relatedhow can I total three columns together, one with the value of #N/A, and two with figures in, without the total coming back as #N/A ?
View 10 Replies View RelatedI'm wanting to Rank a set of figures in one column but use criteria in another column to pick up the array which is used. I don't want any figures to be ignored so will have multiple rank 1's 2's etc. Would also be handy if I could use multiple criteria.
View 9 Replies View RelatedI am using a Hlookup to retrieve Words and Figures but when retrieve words it is failed and output is figures ONLY while I need the same word to be shown in the cell value?
View 2 Replies View RelatedI have 2 spreadsheets, one is on crew costs and the other one is a summary page. On "crew cost" worksheet, say A1= IBM, B1 =Microsoft, C1=HP and D1=Pioneer. I want to link A1 to D1 to cells A1 to D4 on "summary page".
However, I do not want to use "paste special", "transpose" function as I may need to add in columns to "crew cost" spreadsheet.
Instead I hope that I could link A1 on "crew cost" spreadsheet to A1 on "summary page" spreadsheet. Then I hope to drag the formula from A1 down to D4. The result will be A1= IBM, B2 =Microsoft, C3=HP and D4=Pioneer on "summary page" spreadsheet.
However, it does not work as dragging only occurs through columns or through rows. Is there any formula that I can use?
I am working with percentages, and trying to avoid #DIV/0! Errors at the same time.
Lets assume column A holds last years figures, and column B holds this years figures, column C calculates the difference in percentages.
I am having dificulty where say A1 = 0 B1 = 10 (C1 Should be 100%)
then what happens if A2 = 5 and B2 = 0 (C2 Should be - 100%)
Is there a common formula that will account for all possible + or - figures in A and/or B that will also suppress #DIV/0! Errors?
I have a list of 30 numbers representing receipts due. I have one receipt which I know must represent the sum of some of these numbers. Is there anyway in Excel that I can highlight the range of 30 numbers and ask Excel to sample all possible combinations to find which numbers add up to my total receipt?
View 9 Replies View Related