microsoft excel training - excel cell references skip

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » microsoft excel training - Excel cell references with a skip

microsoft excel training - Excel cell references with a skip

resolvedResolved · Low Priority · Version Standard

Minnaar has attended:
Excel Advanced course
Excel VBA Intro Intermediate course

Excel cell references with a skip

Cell A1 of worksheet1 must refer to cell A1 of worksheet2

Cell A2 of worksheet1 must be empty

Cell A3 of worksheet1 must refer to cell A2 of worksheet2

Cell A4 of worksheet1 must be empty

Cell A5 of worksheet1 must refer to cell A3 of worksheet2

Cell A6 of worksheet1 must be empty

Cell A7 of worksheet1 must refer to cell A4 of worksheet2

and so on...

How do I use a formula to do this? Would macros be advisable?

RE: Excel cell references with a skip

Hi Minnaar. Sorry for the delay. The simplest way to achieve this would be to create the following list in a text editor:

=Sheet2!A1
0
=Sheet2!A2
0
=Sheet2!A3
0

(You could create the A1-A100 list in Excel, paste that out into Word and then using find & replace, replace every "A" with "=Sheet2!A". Then replace every paragraph mark with a paragraph mark followed by a "0". Sounds fiddly, but it's the quickest way to create this list)

Then select A1 in Excel and paste as text from the clipboard. The formulae should be active. Then do using find and replace, replace the 0 with " ". Then you have you worksheet.
Hope this helps

Anthony


 

Excel tip:

Wrapping Text in a Cell in an Excel 2010 Workbook

When you have a lot of text you want to put in a particular cell but you can't decrease the font size to fit because the text will then become ineligible, then manually wrap the text in a cell by simply pressing ALT+ENTER.

View all Excel hints and tips


Server loaded in 0.08 secs.