If a range of cells = to a name

Closed

Stormdronk - Apr 11, 2011 at 10:26 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Apr 12, 2011 at 12:41 AM

Hello,

Hope someone can help me?

I have a list of text in 1 sheet, lets say:

A1 Image Gallary
A2 Welcome Message

This list are generated from drop down lists, so the order and text can change.

In sheet 2 I have the same names, but would like to generate values next to each name if they are displayed in the top range.

=If(a1:a3=Image Gallary, then value next to Image Gallary in sheet 2 = 8, or calculate), something like that.

See example of sheets below.

Main categories:

Home
- Image Gallary (dropdown)
- Welcome Message (dropdown)
- Website Introduction
- Transitional Banner

About
- Company Profile (dropdown)
- History

News
- Announcements
- General Publications

Item Description Hours Cost p/h Total Cost
- Image Gallary 8 R 350 R2800
- Welcome Message 10 R 350 R 3,500
- Website Introduction 5 R 350 R 1,750
- Transitional Banner 8 R 350 R 2,800
- Company Profile 5 R 350 R 1,750
- History 5 R 350 R 1,750
- Announcements 5 R 350 R 1,750
- General Publications 10 R 350 R 3,500
- Company Contact Info 6 R 350 R 2,100
- Google Map 5 R 350 R 1,750
- Submit Enquire 5 R 350 R 1,750

72 R 25,200

Thanks for the help, I am lost!!!

If a range of cells = to a name
If a cell has text then return value ✓ - Excel Forum
Insert a new sheet at the end of the tab names and paste the range names starting in cell a1. autofit columns a:b and name the worksheet as range names. ✓ - Excel Forum
Excel conditional formatting if another cell contains specific text ✓ - Excel Forum
If cell contains date then return value ✓ - Office Software Forum
Conditional formatting if cell contains text - Excel Forum

1 response

Answer 1 / 1

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Apr 12, 2011 at 12:41 AM

I have given sheet1 and sheet2 in html format. I hope it will come on your reply correctly. see formals in column B in sheet 2 confirm whether you get the data correctly.

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:185px;" /><col style="width:145px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Image Gallary</td><td > 8 R 350 R2800</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Welcome Message </td><td >10 R 350 R 3500</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Website Introduction</td><td > 5 R 350 R 1750</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Transitional Banner</td><td > 8 R 350 R 2800</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Company Profile</td><td > 5 R 350 R 1750</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >History</td><td > 5 R 350 R 1750</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Announcements</td><td > 5 R 350 R 1750</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >General Publications</td><td >10 R 350 R 3500</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >Company Contact Info</td><td > 6 R 350 R 2100</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >Google Map</td><td > 5 R 350 R 1750</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >Submit Enquire</td><td > 5 R 350 R 175</td></tr></table><br /><br /><b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:231px;" /><col style="width:151px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Image Gallary</td><td > 8 R 350 R2800</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Welcome Message </td><td >10 R 350 R 3500</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Website Introduction</td><td > 5 R 350 R 1750</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Transitional Banner</td><td > 8 R 350 R 2800</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Company Profile</td><td > 5 R 350 R 1750</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >History</td><td > 5 R 350 R 1750</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Announcements</td><td > 5 R 350 R 1750</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >General Publications</td><td >10 R 350 R 3500</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=VLOOKUP(A1,Sheet1!$A$1:$B$11,2,0)</td></tr><tr><td >B2</td><td >=VLOOKUP(A2,Sheet1!$A$1:$B$11,2,0)</td></tr><tr><td >B3</td><td >=VLOOKUP(A3,Sheet1!$A$1:$B$11,2,0)</td></tr><tr><td >B4</td><td >=VLOOKUP(A4,Sheet1!$A$1:$B$11,2,0)</td></tr><tr><td >B5</td><td >=VLOOKUP(A5,Sheet1!$A$1:$B$11,2,0)</td></tr><tr><td >B6</td><td >=VLOOKUP(A6,Sheet1!$A$1:$B$11,2,0)</td></tr><tr><td >B7</td><td >=VLOOKUP(A7,Sheet1!$A$1:$B$11,2,0)</td></tr><tr><td >B8</td><td >=VLOOKUP(A8,Sheet1!$A$1:$B$11,2,0)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>

Similar discussions

Excel if range of cell contain a text put a cell value in

help me to add particular cells

Counting cells containing only numbers

Formula to Find&replace blank cells in excel

Condition formatting on changing cell color based on another cel

Similar discussions

Subscribe To Our Newsletter!