ANmarS - Search results

Excel/VBA expert since Sep 1997 (21 years 2 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

24 Q & A posts tagged with cell

Mon
Feb 6
2012

Copy pictures from sheet to another

i have two workbooks, lets say, workbook A and Library Workbook
Workbook A is where all the code has been going, Library has no code in it. My project in Workbook A needs to look up and pull a worksheet out of Library Workbook - just a direct copy. Simple enough in general, only one problem. Most of the sheets in Library Workbook have images embedded in them that dont get copied when doing a direct Workbooks(Library)Cells.Copy
i can get all of the formatting, column widths, and values/formulas, but not the images
now, there is one thing i havent tried that I think will work, but it has drawbacks...i could copy the entire sheet, not just the cells, but the sheet itself and put it in Workbook A. I think that this would work in theory, but Im reluctant to try it for one reason
I have embedded sheet code in Workbook A in the sheet that I want to be the destination for the copied library contents. The sheet code is event-based, and Id like it to stay in the code. So I was trying to paste the library contents into this sheet to retain the sheet code, where if I paste the entire sheet directly from the library, I dont get to keep the sheet code, because the library, by definition, is supposed to be (vba free)

so...Ive been chasing two different possible solutions, neither one with much luck yet.
(1) Learn how to copy cells from a worksheet with the images included
i know you can use vba to copy images, but the sheets have varying numbers of images, so theres no good way to programatically handle all sheets
shapes(1).copy, ...
might be more than one image


Answer

you still can use shapes.count and start a loop, did you tried that?
however, there might be a downside, when you copy and paste, they will all be over each other, but you can override it by using:
Sheet1.shapes(x).left = Sheet2.shapes(x).left
sheet1.shapes(x).top = sheet2.shapes(x).top
assuming copying shape from Sheet2 to Sheet1
does that make sense?

Monday 2/6/2012
Mon
Nov 21
2011

Excel - How do I stop cell overlap?

How do stop text from overlapping onto the next cell WITHOUT using the wrap text feature? I don't want the row width to adjust like it does when it wraps text, nor do I want to manually adjust cell size. I just want to everything I type or enter to be hidden when it exceeds the standard cell size.


Answer

You can also put an empty string (space or " ") in the cell next to it, this way, Excel will assume it has a value and stop the overlap

This is usually how I do it

Monday 11/21/2011 5:29:51 PM
Sun
Nov 13
2011

How to assign a random number as an id to a marked text in a cell in excel?

What code can I use so that I can assign a random number as a the ID for a text in a cell. For example cell A1 has these sentences:
{I have (three) dogs and (two) cats}. My pets always fight. {My (dog) is afraid of my (cat) and they are both afraid of (mice)}.
I need an excel macro code so that it will assign a random number as an ID for the items enclosed in the parenthesis. I need this macro to assign a random number ID one at a time. In other words what I want the macro to do is to:
1. Search for curly braces in the cell
2. Get all the items enclosed in the curly braces that are enclosed in parenthesis
3. Assign a random number as an ID to the items in the parenthesis without touching the other text
4. Then move on to the next text enclosed in curly braces and continue until there are no more curly braces in the cell

So what the macro will do is that it will look at the contents of the selected cell. Then look for curly braces then look for words that are enclosed in parenthesis and then assign a random number as an ID for each then move on to the next curly braces.

The curly braces and the parenthesis may be changed to any marker.

All help is greatly appreciated.


Answer

The main command you might need is:
Range("A1").value = Replace(Range("A1").value, "(dog)", "(" & Int( Rand()*100) & ") dogs")

Doing it repeatedly is the trick that you will be using to replace all options once found, note that "Replace" works only if that text is found

And then put it inside a loop to check for them, like this

X1=1
Max1 = range("A1" ).currentregion.rows.count
do until x1>max1
Range("A1").offset(X1 ).value = Replace(Range("A1").offset(X1 ).value, "(dog)", "(" & Int( Rand()*100) & ") dogs")
Range("A1").offset(X1 ).value = Replace(Range("A1").offset(X1 ).value, "(cat)", "(" & Int( Rand()*100) & ") cats")
Range("A1").offset(X1 ).value = Replace(Range("A1").offset(X1 ).value, "(mouse)", "(" & Int( Rand()*100) & ") mice")
x1=x1+1
loop


let me know if you need more help

Sunday 11/13/2011 6:47:20 PM
Thu
Nov 10
2011

Is there a way to change the auto-assigned value of empty cells in excel?

In excel, empty cells are treated as though their numeric value was 0, and as though they had no text value.
I'm using a custom function that runs through a range of data (which contains 0 values) and gives me a result based on the range's findings.
Currently, there are no blank cells before the end the range. Therefore, I can use CountA(range) to tell my function how many times to iterate (i.e. to continue until it reaches the end of the range).
I wish to instead specify to the program that it must continue to the end of the range (another problem) and ignore blank spaces.
Rather than re-writing all of my functions to ignore these empty cells, is there an excel option that will allow me to force excel to treat empty cells as a value other than 0?


Answer

If I got you correctly you can use the formula
ISBLANK()
When you put it inside IF, you will will get if the cell is blank

CountA will not count blank cells either

let me know if that solves it or not

Thursday 11/10/2011 3:34:45 AM
Sun
May 2
2010

I need to have the auto sum function in excel calculate alphabet characters?

I would like to have the sum function include alphabet when calculating sums. How can I do this?


Answer

Did you try the formula LEN
LEN is the formula to sum how many characters in a cell

So, if you add this in column D (Assuming the column C has the cells you want to sum its chars). paste this
=LEN(C1)
Then copy it down to get the length of all cells characters

Then do a regular SUM on column D

Hope this will help

VBAXLMan

Sunday 5/2/2010 6:53:36 AM
Sat
Apr 10
2010

How do i create a dynamic chart in excel 2007 that creates a chart from ONE CELL only that is updating....?

how do i create a dynamic chart in excel 2007 that creates a chart from ONE CELL only that is updating its data from an external source every sec....i want to plot the values as they change into an excel chart live from this one cell...thanks, christian


Answer

When you create a chart, make sure you select THAT CELL when it asks for the value

Doing this will make that chart updated every time the cell is updated

That is what I got from your question

VBAXLMan

Saturday 4/10/2010 3:58:16 AM
Thu
Apr 8
2010

How can I link two separate workbooks in MS Excel 2000?

I am looking to link some formulas so that whatever I enter in January workbook will then go to February automatically then march and so on. I have tried copying and pasting special but that just copies the formulas what I really want is for them to talk to each other. Being not as computer savvy as most the answer in the most basic terms would be highly appreciated.


Answer

When you do Paste Special
Select "Paste Link"
This will do it

OR

You can go to February sheet, select a cell and type
=
then select the cell you want to link to in January sheet



VBAXLMan

Thursday 4/8/2010 12:20:38 PM
Tue
Apr 6
2010

What is the VBA code for Convert Excel Worksheet in to PDF in the Name of the Text of specified Cell?

Please Mention the VBA code to convert Excel work sheet in to PDF in the name specified in the particular cell of the Excel Sheet.


Answer

I am assuming you do know how to play around with VBA
In this case, install this PDF Printer
http://www.primopdf.com/

Then print the Excel file through VBA (Record macro and see how it does it)
Then use this VBA line to rename the exported file
Rename [originfilename.ext] AS [newfilename.ext]
Or
Save the file itself (Excel file) with that name from cell, then do the export

PrimoPDF usually saves the file in the same name as the Excel file

Good luck

VBAXLMan http://blog.soliddocuments.com/2010/01/b…

Tuesday 4/6/2010 7:34:07 AM
Tue
Jun 9
2009

In excel, is there a formula to select the last cell of worksheet to calculate values in the next worksheet?

I currently used this formula:
='NSN Motorsports'!A12+1
In this example my previous worksheet is "NSN Motorsports" A12 is the last cell in that worksheet. I used this formula to create the next number on the new worksheet. This requires me to go back to the previous worksheet and click the last cell. My workbook will have many worksheets, so doing it this way is very time consuming. Any ideas for formulas that will not require me to leave my current worksheet to get a value from the previous worksheet?


Answer

You can create a pattern for your sheet names
After that, use some formulas to determine "the previous" sheet to the one you are in...
1- In an empty cell (make it B1), paste this
=MID(CELL( "filename",A1),SEARCH( "]",CELL( "filename",A1))+1,500)
This will give you the name of that sheet, so copying that cell into another sheet will give the name of that sheet, etc
2- Now, If your 'Pattern" is NSN1, NSN2, NSN3, then paste this in B2
="NSN"&VALUE(Mid(B1,4,1))-1
This will give you the name of the "Previous" sheet to the one that you are in
So if you are in NSN4, B2 will give you NSN3, and so on
3- Finally, to get the last value of the "Previous" sheet, do this
=MAX( INDIRECT( B2&"!A:A"))+1

Good luck with that, this will be totally automated method, justcopy and paste the three cells into the new sheet

If you need more info or think that you can customize it more, please contact me here

After all, it is the VBAXLMan here

Tuesday 6/9/2009 11:08:54 AM
Sat
Apr 18
2009

Can you format an Excel cell with a formula and without using a macro or the Conditional Format menu?

Can you enter a formula directly into a cell to change the format of the cell, mainly the font color or the background shade color?


Answer

No
No formula there to change the format of another cell (If not macros nor Validation)

Forget it

Sorry!

VBAXLMan

Saturday 4/18/2009 12:52:04 AM
Mon
Apr 13
2009

Miocrosoft Excel: I want to have a drop down box of materials, which shows thew cost in the cell next to it?

I am producing a quotation system on Excel, and want the materials to be chosen from a drop down box, and the price of te material to be automatically displayed. Any assistance to
seduardo15@yahoo.co.uk


Answer

Here is a lesson showing you how to do it

http://www.file1.net/lesson.asp?id=aFhEw…



VBAXLMan

Monday 4/13/2009 7:00:19 AM
Mon
Apr 13
2009

Is it posible to change the colour of a cell by using functions in MS-Excel ?

By Using functions in Ms Excel can we change the colour in a cell.
for example: if A1=1 then RED colour, A2=2, Then YELLOW colour..


Answer

In Excel2003, go to
- Select the cell
- Format > Conditional formatting
- Enter the values and the format for each condition (maximum 3 conditions)
- Press Ok

In Excel2007
- Home > Conditional Formatting > Highlight cells rules > More rules
- Select the values and the format for each
- You can do up to 256 conditions

Enjoy it

VBAXLMan

Monday 4/13/2009 3:04:36 AM
Thu
Apr 9
2009

How do I create a formula that pulls information from one excel spreadsheet to another?

I have multiple spreadsheets in the same excel file and I am trying to create a formula or a set of formulas that will recognize a number, compute how many of the items have that number and then on a separate spreadsheet, show that sum. What I am working with are 7 spreadsheets- 4 weeks, month, quarter and year. In the week spreadsheets, an incident is labeled with a 1,2,or 3 depending on its severity. I need a formula that would calculate all of the 1s, 2s and 3s separately as their own value and then place that value in my month spreadsheet in the 1, 2 or 3 category.

Thank you in advance for your help.


Answer

If you have:
1- The week sheet name is "week"
2- The range where the 1s, 2s and 3s are in "week" sheet is A1 to A20
3- The month sheet name is "Month"
4- 1 in cell A1 in "Month" sheet
5- 2 in cell A2 in "Month" sheet
6- 3 in A3 in "Month" sheet
Then paste this in B1
=COUNTIF( Week!$A$1:$A$20, A1)
Then copy it to B2 and B3

VBAXLMan

Thursday 4/9/2009 5:20:30 AM
Thu
Feb 12
2009

How to create a function in Excel...a second question?

Thank you to the person that helped me yesterday...a huge help! The isanswer and match functions worked great!

I now need to write a formula to do the following: I need to find if the value in cell C1/Sheet 1 is in column D/Sheet 2. If true, return the value (text) in Column E/Sheet 2 that is in the same row as the value found in Column D. If false, return "null" or "false".

I tried the same isanswer and match formula as it seemed it was close to the solution but it didn't work. Can you help me again? :)


Answer

Paste this in cell E1 Sheet2
=IF( ISNA(MATCH( Sheet1!C1, Sheet2!D:D,0)), "","Text")

And fill it down

----- Edit ----
Yes, in this case, use this
=IF( ISNA(VLOOKUP( Sheet1C1, Sheet2!D:E,2,FALSE )),"Not found",VLOOKUP( Sheet1C1, Sheet2!D:E,2,FALSE))

This whould work

VBAXLMan

Thursday 2/12/2009 3:18:14 PM
Wed
Jan 21
2009

How do you make a password field in excel so when you type in one of the cells, it comes up as asterisks *****?


Answer

Excel cell does not allow you to do this, you need to do an alternative ways, you got two:
1- You can use the TextBox from Forms toolbar (If you have Excel2003) or from Developer tab (If you have Excel2007)
2- Add a VBA form and add the text box to it.

Wether you do 1 or 2 you need to change the password property of it to make it show you * when you type in it

Again Excel cells can not allow you to do that, forget it

If you need help in doing this, or you don't know how to add a VBA form or Textbox to your Excel sheet, mail me here

VBAXLMan

Wednesday 1/21/2009 2:21:07 PM
Wed
Jan 21
2009

Excel 2007 document protection & locked cells?

Hi,

I've created an excel document that numerous users will be editing, however I need for only certain users to be able to edit certain cells.

I currently am using the track changes method, however it appears that there are ways around this.

Does anybody know if there is a formula or a function I can use to only allow a user to edit a certain cell if the username = joe.bloggs for example? Excel can clearly look up the username as it does so in the "track changes" but its how do i use this information to protect my cells?

Many Thanks,
Mark


Answer

The Standard way is to give the password to modify the file (Save As > Tools > General Options) to those people only allowing them to edit these cells, while others can not

The other way is to lock these cells with password (Review > Protect Sheet) and give those the password

In addition to that you will always have the macro way, but that requires more info from you

Enjoy my profile, I am the VBAXLMan here

Wednesday 1/21/2009 6:55:16 AM
Tue
Jan 20
2009

Why does a formula remain visible in a cell on an excel worksheet?

I have many formulas in my excel spreadsheet and when I want to extend the search a few more rows, and change the number in the formula bar, the formula appears and then remains visible in the cell. Please email me if you have a solution on diddlesjnr@adam.com.au


Answer

Perhaps you have the format for that cell as text
Excel does that if you:
1- Have no equal "=" sign as the FIRST character
2- Format that cell as text

VBAXLMan is back

Tuesday 1/20/2009 8:09:30 PM
Tue
Jan 20
2009

Can someone help me with excel?

I know i should figure these questions out on my own, but i would like some help.
my questions are;
true/false
1. if you sort a last name column in ascending order, adams would come before williams
2. to fill in a series, a pattern must be established in the initial selection of cells

questions
1.What feature enables you to quickly fill in the days of the week or the months in a year
2. What menu is the sort command in
3. Does autoformat include borders, shading & data formatting
4. What must you indicate to sort data in excel

fill in the blank
1. to sort a worksheet by multiple criteria you must open the ______ dialog box
2. the ______ that indicate the boundaries of each cell in a spreadsheet do not appear on the printed worksheet
3. A(n) ______ is text that prints in the top margin of every worksheet page
4. The ________ tab in the format cells dialog box allows you to format numeric data with commas separating thousands
5. To give a worksheet a new name, you can right click the _____ and then select rename from the shortcut menu.



thankyouuuuu ! (:
17


Answer

Part one:
1- true
2- false

Part Two:
1- Edit > Fill > Series
2- Data
3- Yes
4- Sort by column

Part Three:
1- Sort
2- Grid lines
3- Header
4- Number
5- Sheet tab

VBAXLMan

Tuesday 1/20/2009 1:48:52 PM
Mon
Jan 19
2009

Display date and time together in a cell when using a DatePicker?

Hi, I added a date picker add-in from the following url. into excel
http://officeblogs.net/excel/samradDatePicker.xla

Ensured that i make a column accept dates. and now when i click on that cell i do get an icon to enter the date.
My question is the output comes as only date "01-mar-09" if i want the time also to be there along with date(in the same cell), what do i have to do.
I tried changing format in DATE in cell formating but its not working.
anyway i can get this done
Thanks


Answer

It is a "DatePicker" and not a timepicker
That was 1.
2. what time do you want to use if the user didn't select one?
3. You can do the following:
Do a validation in the next cell with the expected time (as hours or halves) then allow the user to select from it

The date usually uses the integer part on the number to refer to the date, time uses the decimal part

So if your date is Jan-19-2009 (means=39832) then the time of that would be 12:00 AM

So when you select a date in DatePicker, you are actually selecting the hour 12:00AM in the morning of that date

To add a time, you will need to do a validation or do some other macro code

VBAXLMan is back to feed your Excel needs

Monday 1/19/2009 11:37:50 AM
Sun
Jan 18
2009

Excel - how can I have a cell auto-completed with a description of another cell filter?

Hi
I have a spreadsheet with lots of orders on it that I want to filter by name, I have set up the filters fine and that is working well. However, I want another cell in the same sheet to show what I have name I have chosen to filter.
Is this possible? If so, how.

Any help appreciated.

MGB

Any help appreciated.


Answer

The AutoFilter does not do that
Or you can not do this in AutoFilter
However, you can do this if you are using Advanced filter

Advanced Filter allows you to filter range based on a criteria in another range
Meaning you can see the filter criteria when you filter the table

But, you will need to use the criteria in cells that are not affected by the filter, I mean you will need to shift the table down starting from A10 for example and put your criteria range in A3:C7 for example
This way you can see the filter settings and the filtered range

VBAXLMan is back to feed your Excel needs

Sunday 1/18/2009 7:31:54 AM
Sat
Jan 17
2009

I want to fix a pic in MS Excel 2007 cell and to use in an another sheet with Vlook up formula?

For each cell of employee I want to fix a pic and to use in forms with Vlookup formula


Answer

Excel functions are working with text and numbers
You can not do this with functions, instead you will need macros to do that

I would suggest to do a folder with employee pictures named with employee ids
Then insert a picturebox in that Excel file and do some macros to make it read the selected employee picture.

Something like this:
Sub ReadPic()
EmployeeID =Range("A1").value
PictureBox1.picture=LoadPicture( "D:\Pix\" & EmployeeID & ".jpg")
End Sub

This will read the picture in the picturebox assuming cell A1 had the employee id

I will be glad to help if you just send me the file you are talking about and more details to build the macro for you

VBAXLMan

Saturday 1/17/2009 10:34:54 AM
Fri
Jan 16
2009

I need excel spreadsheet help for my new company?

I'm starting a company based on commission only. I need an equation for excel in which I can input a $$ amount sold and have it auto read the different pay scales and generate their income. in $$s and on a line graph. The pay scale is as follows:

$612 to $1223 is 6%
$1224 to $2447 is 12%
$2448 to $4895 is 24%
$4895 to $ 8499 is 26%
and $8500+ is 27.5%

Thanks!


Answer

Do this pay scale in a separate sheet, make it Sheet1 cells A2:C6 having A2 is 612, B2 1223, C2 6%
and so on
Now, in another sheet, put your amount in cell D4, and in E4 paste this:

=INDEX( Sheet1!C2:C6,MATCH( D4,Sheet1!A2:A6,1))

and this in F4
=D4*E4

That is all

VBAXLMan is back to feed your Excel needs

Friday 1/16/2009 12:14:18 AM
Sun
Jan 7
2007

Excel formula to make input number its inverse?

I want to be able to input a number in a cell and automatically have it convert to its inverse (in that same cell). Is there a forumla for that?


Answer

No formula can do this.
Formulas are have to be entered in a cell first to work.
So You will need a VBA to do this
put this VBA macro in the code area of any sheet you want to do this:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveCell.Value = 0 - Val(ActiveCell.Value)
End Sub

need more info? mail me here in Y! Answers

Enjoy my profile, I am the VBAXLMan

Sunday 1/7/2007 9:07:50 PM
Wed
Dec 20
2006

I have data in a column in an Excel spreadsheet and I need to use it in formulae in a row on another sheet.?

I don't want to have to type the formula over and over for each cell! Is this possible? Using the '$' sign doesn't work...


Answer

You can use
1- The Copy + Paste Special (Transpose)
2- Use the COLUMN(), ADDRESS functions like this:
Your source column is in D in Sheet1, you want it in row 5 in sheet2, so in cell A5 in sheet2 paste this
=INDIRECT( "Sheet1!D"& COLUMN())
the function in cell A5 will call the value in cell D1 in sheet1 and so on.
You can change the sheet name of the source column by changing Sheet1 in the formula.
If you are not starting from D1, or you want the row to start from other than A5 then add the -1, +1 after the COLUMN() to modify it

3- Use the TRANSPOSE function

I think this will answer you

Enjoy my profile, I am the VBAXLMan

Wednesday 12/20/2006 4:36:07 AM

Tags

History