# Return Pictures Based On Cell Contents?

Jul 25, 2014

I have inlcuded my workbook that I use for tracking the NFL season and I'd like to add in some team logos just to make it look a little cooler.

The attached workbook includes three sheets, the first of which uses a drop-down for selecting picks. The second sheet has the team logos and the third sheet feeds off the first.

I'd like for team logos to populate into the third sheet based on what is selected in the first sheet.

I've already tried a few methods, all unsuccessfully. I tried this [URL] which was a great idea but it didn't allow for the same image to be repeated potentially 100 times. I also tried a method involving named ranges and indirect lookups to the linked picture image but that would require a different named range for every person and every pick which isn't feasible.

if it's possible to just do a massive nested if statement with a vlookup to return an image but I haven't been successful in that either.

## Return Contents Of 1 Cell Based On Contents Of Another Cell?

Mar 12, 2014

I thought this was a pretty simple formula but I am having difficulty creating it. I am attaching a little test spreadsheet. Sheet 1 is where the data will be entered. The Reimbursed column has a drop down choice of yes or no. The next 2 columns are the cost of registration and the cost of accommodations. On sheet 2 is where I would like the formulas. So in cell A4 I would like a formula that says if B3 on sheet 1 is Yes populate this cell with the contents of Cell C3 only, B4 of sheet 2 would then be B3 if A3 on sheet 3 is Yes and so on with the Not reinbursed if sheet 1 the Reimbursed column is no.

## Return Value Based In Contents Of Another Cell With A Twist?

Jan 7, 2012

I would like cell CM2 to look at cell G2 and get the value there. Here is where it gets interesting. I would like it to reference sheet "LookupTables" in column A.

Cell G2 will be a 4 digit number.

Cells in column A of sheet "LookupTables" will be formatted like this: 3 digit number-4 digit number-4 digit number, like this "001-0001-0001".

If cell G2 exists in column A of the LookupTables sheet, (but it needs to ignore the first 4 digits and the last 5 digits of that number) then it should return that value in cell CM2. I would like it to look through the entire column A of the LookupTables sheet and return all numbers that match the above criteria, and separate them with a comma.

So, if G2 is 0001 and column A of the LookupTables sheet contains several values including:

001-0001-0006
004-0001-0187
098-0001-0874
098-0002-4356 (it should ignore this since the middle numbers don't match the value of G2)

then CM2 should display: 001-0001-0006,004-0001-0187,089-0001-0874

Also, if there are no matches, then it should just return a blank cell.

## Inserting Pictures Based On A Cell

Nov 21, 2007

I want to be able to a place a picture on a workbook based on a cell. In column A, I will run a sql query to pull a list of items. In column B, I have the items formated thus they have a the .jpg. next to item of the picture i.e Hammer.jpg. whereas column A lists Hammer and Column B Hammer.jpg. In column C, I would like the pictures to reside in this column.

So far I have this macro but I can only get two pictures to come up even though my list has 4 items on it.

Sub pic()
Dim x, c, d, a As Long
d = 2
c = 40
For a = 2 To 6
b = Cells(a, 2)

Jul 17, 2006

Is it possible to have an Image Box which opens different images based on the value of a cell. Say when A1 = 1, the picture of one thing comes up....when A1=2 and different picture and so on?

## Insert Pictures Based On Cell Values From One Specific Folder

Mar 14, 2014

where i have a products list and respective pictures in a folder. What is should do is to get the relevant pictures appear on the screen when i select the relevant name.

## How To Return Cell Contents If It Contains Partial Text

Jan 2, 2014

Below is a small sample of Column A from a spreadsheet I'm working with. All cells in the column begin with a 6 digit number:

115383_BOSTON
115384_NEW_YORK
115385_PORTLAND
115402_LOS_ANGELES
115403_WACO
115010_SAINT_PAUL
115011_SAN_DIEGO
115130_WEST_NYACK
115021_CHAPEL_HILL

What formula would allow me to enter only a 6 digit number and return the entire cell contents, i.e, search for 115403 and get a return of 115403_WACO ? I have tried INDEX and MATCH, but I'm not getting the results I need.

## How To Return Contents Of Last Non-blank Cell In Column

May 7, 2014

I am trying to figure out how to return the contents of last non blank cell in column B, based on the name in column A. So, if I choose "Sam", the result I am looking for is "blue". If I choose "Pete", the result I am looking for is "orange".

Sam
red
Sam
blue
Sam
Pete
orange
Sam

## Sumif: Return The Contents Of The Respective Cell

Nov 5, 2009

In Column A, in each cell in rows 1-10, there is various text that may or may not include "ABC" and "DEFG" as the first four letters. In Column B, rows 1-10, there is numerical data. What I would like to do in Column C, rows 1-10, is place a formula that would return the contents of the respective cell in Column B, but only where the first three or four letters in the respective cell in column A is "ABC" or "DEFG".

## Find Value & Return Contents Of Offset Cell

Sep 3, 2006

On Sheet2 I have a table where I want M2 to do the following:

1.) Look at cell A2 where the name "John" is and B2 where the name "smith" is.

2.) Look at sheet1 and look in B2 and C2 for "John" and "smith"

3.) Once it matches the name, place the contents of sheet1, cell I2 into sheet2, cell M2.

So in short, match the name on sheet2 to the name on sheet1 and return the number in I2 to M2 on sheet2.

Maybe combining the persons name in to one column would make it easier?

## Search For Text Within Cell Then Return Contents Of That Cell?

Jun 19, 2014

So right now I have a spreadsheet that looks something like this:

A
B
C
D

[Code]....

As you can see, the stock names are slightly different in columns A & C (CORP. vs CORP, CO vs CO., etc). I need a formula in column D that searches column A for the first word in column C, and then retrieves the contents of that cell. I want to do this because I will then use a vlookup in column E to get the ticker for the stock.

Right now I have: =IF(ISERROR(SEARCH(LEFT(C1,FIND(" ",C1)),A:A,1)),A:A,"")

This formula searches column A for EXXON, but does not return the contents of the cell. Instead, it returns the contents of a different cell in the column.

## How To Save Linked Pictures Into Workbook As Embedded Pictures

Apr 28, 2014

I have an workbook with pictures that are linked to external picture files, and they are placed in the workbook via macro. When I email the file to another computer and open it, the pics do not display and show an error:

The linked image cannot be displayed. The file may have been moved, renamed, or deleted, Verify that the link points to the correct file and location.

I would like to be able to save the workbook with the pictures in them (without having to archive them) so that I can open it and see them on another machine.

## Get A Cell To Look At A Row Based On Contents

Dec 24, 2008

I want to have a cell that looks at another cell based on a number that is in another cell. So imagine if you will: A1 contains the number 25. A2 contains the formula I can't work out. I want it to look at the row that is the number contained in cell A1, so in this case it would look at row 25.

Now I want to narrow it down further to a cell in that row but this will be a constant so lets say column F. So if A1=25 then A2 would display what ever is in F25. If cell A1 were 16 it would display the contents of F16 etc. The best I got was something like =IF(A1=>1,(F(A1))) but obviously that is wrong as it doesn't work.

## Add Row Based On Cell Contents

Apr 25, 2006

I want to add a row underneath a row if the value in the in B cell is not = "". Then i want to write a value in the C cell of the new row.

## Workaround For Public Variable Bug: Creates Different Cell Colors Based Upon The Cell Contents

Oct 15, 2008

I am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug.

Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.

## Macro That Will Clear Contents Of Cell Based On Format Of Text In Adjacent Cell

Feb 18, 2009

Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.

## Automatically Update A Protected Cell Based On Contents Of An Unprotected Cell

Apr 30, 2009

I am trying to find some VBA that will allow protected cells to be updated when a user enters or selects data in other cells.

For example:

Cell 'A1' starts out blank.
Cell 'B1' has a drop down to select specific items.
Cell 'C1' is the date of the order and is a locked cell.
Cell 'D1' is the date of completion and is a locked cell.
The worksheet is protected to prevent direct changes to cells C1 and D1.

If a user enters any information in A1, then C1 displays the current date.
If a user selects 'Complete' from the drop-down list in B1, then D1 displays the current date. Both of these actions are independant of one another. This format is the same for every cell in the 4 columns indicated above. I thought I was able to do this in another spreadsheet I created a year or so ago but I have not been able to figure out what I did and I do not have the spreadsheet to look at.

## Replace Cell Contents Based On Matching Data In Another Cell (On Another Sheet)?

Apr 28, 2014

I am working with a spreadsheet that has our user data; User(First) Name, User(Last) name, Computer Name, MAC address, Phone Number, Seating Position and Network Port Number.

I have let this get horribly out of sync, moved computers around, and moved them while the MAC address column was hidden, As a result, I have a bunch of users listed as being at the wrong computer. I have a second spreadsheet I generated that gives me the logged in user for about 2/3 of my computers, with the computer name(Just 2 columns, 'A' and 'B'. I would like to have Excel match the computer name and then overwrite the user name. For example, if the first computer in my correct user list is DELL-99945ty2, and the user name is "Jimbo Jones", I would like it to search the first Excel doc(The one with all of the user data), and replace the user name with "Jimbo Jones". Is this simple and straightforward?

In my main spreadsheet, the user name is in column B and the computer name is in column G, So I want to use the values from Column B in my second document to find its match in Column G of the first spreadsheet, and then replace the data in Column B with Column A in the first spreadsheet.

## Worksheet Name Based On Contents Of Cell

Oct 13, 2013

Is it possible to dynamically chance the name of at a worksheet that already exists based on the contents of a cell on another sheet?

I.e. If the name of a sheet is linked to the value of the Cell A1 in the "Data" sheet and I change A1 to read "Group1" it changes the name of the sheet as well?

## Contents Of Cells Appear Based On Another Cell

Dec 14, 2013

I am working on a sheet where I want the contents of cells D1:D10 appear if cell A1 has a text in it and the contents of cells D1:D10 disappear if cell A1 is blank. What is the right conditional formatting formula for this.

## Lock Row Based On Cell Contents

Oct 17, 2008

I wish to be able to lock an entire row after an entry has been made in a certain cell.

e.g. let's say i want to lock row 9 in its' entirety following entry of "Yes" in cell G9. If there is no entry in G9 the the entire row should be still unlocked. Likewise with any row with a corresponding entry in column G.

## Print Based On Cell Contents

Aug 28, 2009

I have put together a spreadsheet to help with the creation of work orders. I have a summary page that the user can define which category the work order falls under along with this I have a couple of macros set up that will print a specific work order pertaining to the category they selected. I have run in to a few problems (mostly training users) that I would like to eliminate up front.

I am looking for a macro that I can use to look at the contents of a cell (contains a word that corresponds directly to the name of one of the sheets in the work book) and print the worksheet that has the same name as the contents in the referenced cell.

## Sound Based On A Cell Contents

Jan 11, 2010

I have thousands of items each with a unique barcode(column A) and each is catorgorized either KEEP or DISCARD (column B). What I want to do is scan the barcode (column A), Vlookup and return KEEP or DISCARD (column B). Now based on what the vlookup returns can I have a sound file play for KEEP and a different one for DISCARD.

## Lock / Unlock Excel Cell Based On Contents Of Another Cell?

Sep 12, 2013

How to Lock/Unlock an Excel Cell Based on the Contents of Another Cell?

## Setup Dependent Cell Based On Contents Of Independent Cell

Oct 20, 2013

I'm trying to set up a dependent cell (B) based on what is in the independent cell (A)

If A is blank, B should be blank

If A has any value other than X, B should display a set string Y

If A contains X, B should contain a drop down list with a range of choices.

I've tried setting it up as a dependent validation cell and I've tried using OFFSET, but the best I can do is to get three different drop down lists to populate cell B. One with only string Y, one with only a blank, and one with the range of choices. The problem is in the first two cases above, I want the cell to fill automatically with either blank or string Y -- I don't want them to just be drop down options, with the cell displaying the last value that was input.

## Finding A Cell In A Variable Location Based On The Contents Of The Cell

May 2, 2012

I am creating a macro to automate data analysis for work, but I've become stuck.

I need to find the coordinate of the first cell in a column with word "reserved" or "extended" contained in the cell, and then assign the row number of that coordinate as a variable to use for moving data around. Basically, I want something like VLOOKUP, but instead of returning a value, I want it to return the coordinate.

## VBA To Clear Contents / Change Value Of A Cell Based On If Another Cell Has A Value And Continue L To R

Mar 9, 2013

I am relatively new to VBA. I am creating an attendance calendar that tracks employees calling sick, late etc.. It is a point based system. What I am looking for is, a way to clear the point value that was manually entered in a specific cell (I3), if there is a Value manually entered in (CU3). Each column in my worksheet is for a specific date ie; I3 is the cell where I enter the points (1.00) for that employee by calling in sick on 3/1/2013, (Column "I" is for 3/1/2013). After 90 days, this point accumulated by the employee does not count against them, so I need that point entered in (I3) to either = 0 or the cell contents to be cleared if there is a value entered in cell (CU3) which is 91 days after, so my (A3) cell does not add that point acquired on 3/1/2013.

I need this to run in a range (I3:I450) so if any value is entered into (CU3:CU450) it has the same result and continue to for (J3:J450) so if any value is entered into (CV3:CV450) and so on..

## Clear Contents Of Cell Based On Comparsion Of Cell Values

Mar 7, 2007

I am a financial administrator & every month I have down load 4 bank accts as CSV, import into xl & code (CACode) the amounts for our accountant. I have VBA that formats, adds headings & formula etc but I have a problem/s. In H col I place CAcode & I use a sumif formula in I col to sum all the amounts with that have the same CAcode. For simplicity sake I copy the sumif down & then sort H col ascendindingly (this is done by VBA). Now I am trying write a macro to clear the contents of the cell in I col

if eg h60 = h59
then I60 clearContents, Select h59
Else select H59
Do until H3 is selected

Psuedocode
Select table (A2:I Xldown)
Sort Table by CAcode (H Col)
Set Range as H3:Xldown
Select Last cell with CAcode (Xldown) in H col
For every cell in Range (H3:Xldown)
Use If/thenIf Last cell = 2nd Last cell (H Col) then
Clear contents of I col (last row)
select 2nd Last cell (H Col)
Else 2nd Last cell (H Col)Next Cell

I have to use Xldown to select range as the range will be variable each month & for each bank acct. I need to clear contents of cell to verify that all the sub-totals of unique CAcodes of the CAcoded amounts = the totals Because I may have up to 120 rows X 3 bank accts I am slowly using vba to do my work.

Sub sort_And_delete_Sumif_amounts()
Dim r As Range
'Select range to sort
Range("A2:I98").Select
'Sort CAcode in H col ascendingly
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'select range for comparisomn of CAcode
Set r = Range("H3", Selection.End(xlDown))................

## Copy Based On Cell Contents Macro

May 13, 2013

Basically lets say I have in cell A1 to A10 floor access data i.e. Users who used their ID cards to access a room.

Now the data always starts with either Rejected or Admitted then the user's name and then the card number and the access floor etc. the card currently has.

What I am looking for is that the macro should first check if the cell has Rejected or Admitted written in it - this I can do myself using =Left(A1,8) which should give me the helper column I need.

Then based on this I want it to only copy the name of the individual i.e. it should look in the cell and only copy whatever is written between "Rejected" and "(Card". The cell data is something like this:

Rejected Doe, John (Card #123456) at ABC 123 Floor1/Floor2/Floor3/Floor4 Door 1 [In] [Clearance]

## Break Contents Of A Cell Based On Character?

Dec 18, 2013

We do large loads of new part #'s/descriptions/UPC's/etc from excel into our database. The fields are limited on character count and I would like writing a formula to break the contents of a cell based on character count without breaking a word.

Examples

Column A Current Data
Row1: 12Pt HD Offset Striking Wrench 1-3/8" & 35MM (44 characters)
Row2: 12Pt 1/2" Drv Socket 27MM (25 characters)
Row3: 12Pt Satin Combo Wrench 11/16" (30 characters)
Row4: 12Pt 1/2" Drv Deep Socket 1-1/8" (32 characters)

Results I'm Looking For

Row1: Column B-12Pt HD Offset Striking Wrench Column C- 1-3/8" & 35MM
Row2: Column B-12Pt 1/2" Drv Socket 27MM Column C-No Data Returned
Row3: Column B-12Pt Satin Combo Wrench 11/16" Column C-No Data Returned
Row4: Column B-12Pt 1/2" Drv Deep Socket Column C-1-1/8"