Repeating cell-contents

A1     contains "Smith"
A53    contains "Jones"
A459   contains "Smith"
A1072  contains "Peterson"
A23405 contains "Smith"
A45234 contains "Samantha"

What formula can I use to put:

"Smith" in B1 thru B52
blanks  in B53 thru B458
"Smith" in B459 thru B1071
blanks  in A1072 thru B23404
"Smith" in A23405 thru B45233
blanks  in A45234 thru A65536

              Thanks,

              Gary
0
gcotterl (63)
2/7/2004 4:47:29 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
888 Views

Similar Articles

[PageSpeed] 18

Hi Gary
try the following:
In B1 enter
=A1
in B2 enter
=IF(A2="",B1,IF(B1="",A2,""))
and copy down

HTH
Frank

Gary wrote:
> A1     contains "Smith"
> A53    contains "Jones"
> A459   contains "Smith"
> A1072  contains "Peterson"
> A23405 contains "Smith"
> A45234 contains "Samantha"
> 
> What formula can I use to put:
> 
> "Smith" in B1 thru B52
> blanks  in B53 thru B458
> "Smith" in B459 thru B1071
> blanks  in A1072 thru B23404
> "Smith" in A23405 thru B45233
> blanks  in A45234 thru A65536
> 
>               Thanks,
> 
>               Gary


0
frank.kabel (11126)
2/7/2004 5:45:30 PM
Reply:

Similar Artilces:

Skipping Blank Cells
I am looking for a formula that will skip blank cells and put the nex cell with data where the blank cells are. Example: If I have info in A1, A2, and A6, I would like it to skip A3-4-5 an now A6's data will be in A3. Hope I didn't confuse anyone....very important that I figure this on out. Thanks for taking the time to try and hel -- Coltsfa ----------------------------------------------------------------------- Coltsfan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1998 View this thread: http://www.excelforum.com/showthread.php?threadid=47561 ...

Is there any way to put a pic in a cell and type over it?
I want to put faided out text in the back of a cell, which i can then type over with solid font once the hard copy of the form has been signed? ...

Repeating cell-contents
A1 contains "Smith" A53 contains "Jones" A459 contains "Smith" A1072 contains "Peterson" A23405 contains "Smith" A45234 contains "Samantha" What formula can I use to put: "Smith" in B1 thru B52 blanks in B53 thru B458 "Smith" in B459 thru B1071 blanks in A1072 thru B23404 "Smith" in A23405 thru B45233 blanks in A45234 thru A65536 Thanks, Gary Hi Gary try the following: In B1 enter =A1 in B2 enter =IF(A2="",B1,IF(B1="",A2,"")) and ...

Printing a Repeated Section Messege like "Continued"
Setting the repeated section property in a report (Access 2007) to Yes, writes the section Heading on the next page when the detail section is not complete at the page break. I would like the bottom of the first page to print "Continued on Next Page" and/or the top of the second page to say "Continued from ...)" so the report reader knows the details are part of the previous section on the previous page, and not in an entriely new section of the report. Can this be done dynamically in a report, since some pages naturally break when a section ends and other pag...

how to get Corresponding cell value
hi friends I have 2 cols displaying name and work done like Col A Col B ------------- James 20 Jack 21 If a user input Col B value in an InputBox then how should I get corresponding cell value? thanks in advance johnbes -- johnbes ----------------------------------------------------------------------- johnbest's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2969 View this thread: http://www.excelforum.com/showthread.php?threadid=49605 If you do Data > Filter > AutoFilter, and select 20 from the dropdown arrow at the top of column B, it will filt...

Format cell if cell is zero
I have Excel that show text after Calculating in the cell. I want the cell dosen't show the text before the cell is typing any value. How I do that ?? // sokoban ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ If the value is typed in cell B3, and the text is in C3, enter the following formula in cell C3: =IF(B3=0,"","This is the text") or =IF(B3="","","This is the text") When you type in B3, the text wil...

reference cell from advanced filter
I'm sure this is slam dunk, but I can't figure it out :) On worksheet 1, I have executed an Advanced filter of 900 rows, to get down to one row. No problem. Now, on Worksheet 2, I want to reference an ID number from my filtered row. Obviously, simply doing a regular cell reference won't work (=Sheet1!G271), because the filtered row will NOT always be row 271. So, how the heck do I reference the filtered cell without going back and linking to it new each time? The below formula will return the 1st entry in Column G from the filtered data in Sheet1. =INDEX...

Repeating rows dont repeat fully when columns also repeat
I am repeating rows 1 through 4 and columns A through D. At least that is what I'm trying to do. On page two, the columns repeat but then the rows do not (beyond columns A through D). Is there a way to make it so that they both repeat? In re-reading the above, I'm not sure I'm making myself clear. Assume that XX's represent the repeating rows and ZZ's represent the repeating columns. This is what I'd like to see: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX ZZZZZZZZZZZ YYYYYYYYYYYYYYYYYYYYYY ZZZZZ...

how do I separate different points within a cell in Excel
within a single excel cell, I want to start each point on a new line. For example: complete at least one publication per year disseminate all materials identify new material Alt+Enter provides a paragraph return within a cell. Is that what you mean? ******************* ~Anne Troy www.OfficeArticles.com "Niceness" <Niceness@discussions.microsoft.com> wrote in message news:9109ADA5-8361-4C5A-ABFF-2CF08C9EA4A4@microsoft.com... > within a single excel cell, I want to start each point on a new line. For > example: > > complete at least one publication per year &g...

Linked cells...
Help! Here's what I want to do... Sheet 1: Summary of Sheets 2 - 12. Sheet 2 - 12: Set of assessment data for a class of children - each sheet a different subject, but all sheets identical in every other way (except, of course, for assessments recorded in the boxes). The rough look of sheets 2 onwards is as follows: Christian Name. Surname. Group/Set. Average Mark. Unit 1. Unit 2... Joe. Bloggs. 1 =AVERAGE(B5:B23) 2. 3. So, as you can see, the 4th column shows the average score so far, based on how many units have been comple...

Extract multiple numbers from a cell
Here are some examples of text with multiple numbers embedded: A1 = 3031 // 2841;1886-ring road location A2 = 3305 //1455-historical A3 = //3491;3492 A4 = //inactive location; historical = 1790; enter new locaion Result should be: B1 = 3031;2841;1886 B2 = 3305;1455 B3 = 3491;3492 B4 = 1790 Try this UDF (User Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. =ExtractNumbers(A1) Function ExtractNumbers(strData As String) As String Dim intTemp As Intege...

autofill according to cell index.
for example I fill B1 cell with D3.And I want to fill the other B cells(B2,B3...) with the D(3+n) .The n is a constant.For example n=2. That is why B2=D5 or B3=D7.How can I do that??Thank you Hi in B1 enter =OFFSET($D$3,(ROW()-1)*n,0) -- Regards Frank Kabel Frankfurt, Germany "honour" <honour@discussions.microsoft.com> schrieb im Newsbeitrag news:28D8E85A-9AF7-40EC-BEA9-F50CE8C5024D@microsoft.com... > for example I fill B1 cell with D3.And I want to fill the other B > cells(B2,B3...) with the D(3+n) .The n is a constant.For example n=2. That is > why B2=D5 or B3=D...

random numbers, how can you utilize it without repeating them
How can you use random numbers without repeating them on a worksheet? The way it is set up now is, example: =RANDBETWEEN(1,8) I would like to be able to use the random between but I don't want the numbers to repeat themselves within the worksheet. Any suggestions or Ideas? http://www.mcgimpsey.com/excel/udfs/randint.html -- Regards, Peo Sjoblom (No private emails please) "RS" <RS@discussions.microsoft.com> wrote in message news:75427E72-3EAA-4105-A65B-53F0F1213414@microsoft.com... > How can you use random numbers without repeating them on a worksheet? The >...

Selecting a cell based on counting visible cells from the top
I want to be able to select a cell in column "J" where the row to select is the 3rd visible row from the top. I want to be able to freeze the pane at this point. Filtering is employed on this sheet and it is likely the 3rd visible row down can be any row between 3 and 2400. Thanks, Alan Doesn't this make the data act pretty funny when you unfilter the range? In fact, say you have the same data (xxx) in rows 2:55. You filter to show yyy. You apply your freeze panes. Then you data|Filter|showall. It might be very difficult to explain what the user should do so that they can...

"Auto tabbing" to a certain cell
Hi, I hope I explaining this right. There is a form at work that needs data entered into certain fields. The fields are all over the page (very much like a tax form) and cannot be lined up in a row. Is there a way I can automatically jump from one cell to another by hitting the tab button once? Example: The first cell that needs to be entered is G4. From there I want to be able to tab and be in M4. After that I would like to hit tab and be in D6, ect. It seems to me that there has to be a way to do this. I hope someone knows how. Thanks, Eddie --- Message posted from http://ww...

Iterate named range & clear contents
Excel 03 Hi all I have a bunch of named ranges (Insert > Name > Define) which I wan to iterate through, & then delete their contents. I'm trying this : For Each nm in ThisWorkbook.Names nm.ClearContents Next nm can someone please show me the correct way to do this. thanks paul For Each nm In ThisWorkbook.Names On Error Resume Next Range(nm.Name).ClearContents On Error GoTo 0 Next nm -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paul Wagstaff" <paulwagstaff@blueyonder.co.uk> wrote in mes...

repeating cells in footer in excel
I need prepare a form with many lines. In this form I need to provide a footer (with a group of cells) for approvement. Even I can select cells to repeat in every page header there is no such a feature for footers. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http:...

Delay Calculation of A Cell
I have a cell with the following calculation =IF(AND(C62-C56>0,C70-C66>0),"Yes - Loan Should be Approved",IF(AND(C62-C56<0,C70-C66<0),"No - Speak to your Advisor.","Possible - Depending on Detailed Application, speak to your Banker")) Now it works ok but I want to stop it calculating until the "user" requests an answer. The second part is how do i tell the spreadsheet to remove all data in "unprotected cells"? The idea the user opens the spreadsheet completes a series of cells then selects answer to display the...

In Excel, I cannot move my cursor to another cell with arrow keys
In Excel, I cannot move my cursor to another cell with arrow keys. What do I do about this? Make sure Scroll Lock is not activated. -- Best Regards, Luke M "bill" <bill@discussions.microsoft.com> wrote in message news:3FF476E4-D191-4EBD-9C7F-2903F9C7D147@microsoft.com... > In Excel, I cannot move my cursor to another cell with arrow keys. What > do I > do about this? JUST PRESS THE SCROLL LOCK BUTTON AND TRY IT WILL COME bill wrote: In Excel, I cannot move my cursor to another cell with arrow keys 25-Mar-10 In Excel, I cannot m...

Reference different lines in the same cell
Hi all, I have an excel sheet were in some cells there are multiple lines created by pressing "Alt + Enter". Is it possible to reference each individual line within these cells separately in a formula? Thanks a lot To a certain extent yes. E.g. if A1 contains one Alt+Enter then =LEFT(A1,SEARCH(CHAR(10),A1)-1) returns the content before Alt+Enter, =RIGHT(A1,LEN(A1)-SEARCH(CHAR(10),A1)) after Alt+Enter. -- Regards! Stefi „Panos” ezt írta: > Hi all, > > I have an excel sheet were in some cells there are multiple lines created by > pre...

Repeats
On one of my worksheets I type the day of the week in the first column, followed by the date in the second column etc. Since the current entry is in line 3,675th when I typed the first letter of the day the rest was completed automatically. E.g. If I typed M then onday followed, as were entries of the same gist in other columns, when the first word of the information was typed. Suddenly this stopped. Why I have no idea, but would appreciate if someone told me how to restore this time and typing savings. Thanks! Tools>Options>Edit, check that Enable Autocomplete for Cell Values i...

Cell Format #10
In Column A, I have a general categories of problems. (There happens to be 12 categories, which isn't important beyond letting you know there are multiple categories). In Column B I give examples of the problem. The examples are numbered. Is there a way to set the cell format so that it acts like a bullited list i.e., the number, a tab, then wrapping from the tab to the end of the cell. Pa, Put the number in column B, and the text in column C, formatted for wrapped text. Or use the appropriate tool, a word processor - like MS Word.... HTH, Bernie MS Excel MVP "Pa Maher&qu...

Lots of text in one cell
After I click on File->New, Excel will begin with a default size empty sheet. If I type a long, say 100+ character, string into cell A1 then that text will visibly spill over in the adjacent cells. Is there a way to tell Excel to resize or redimension cell A1 so that my string completely fills the cell in a nice looking manner??? I basically want any arbitrary string that I type to completely visible and contained within cell A1 in a nice looking way. thank you Hi Tricky to do in a consistent, automated and attractive way. Excel doesn't do attractive on the whole. Y...

Making a Cell Turn Color
Hi, If I input a number in A1, such as 1, and I have the following numbers in B1 1,2,3 just like that separated by commas. When I put that 1 in A1, since it matches a number in B1, can I then have B1 automatically turn red? (or yellow or whatever) Thanks Try this: Select cell B1 Goto the menu Format>Conditional Formatting Formula Is: =ISNUMBER(FIND(","&A1&",",","&B1&",")) Click the Format button Select the style(s) desired OK out -- Biff Microsoft Excel MVP "speedy" <speedy99@msn.com> wrote in message news:fp2...

Getting the cell reference
Thanks both for the QUICK response - I am amazed!! I was hoping for simple formula as judging by your guys feedback, I am certainly novice user. I will try get my head around the INDEX formula info. Your help is much appreciated. Nick Hodge Wrote: > Duncan > > How are you trying to do this as you are in the misc group? > > Programmatically... > > LastRowInA = Range("A65536").End(xlUp).Row > > -- > HTH > Nick Hodge > Microsoft MVP - Excel > Southampton, England > nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS > > > "DuncanG&q...