Forum home » Delegate support and help forum » Microsoft Excel Training and help » microsoft excel training - Excel cell references with a skip
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
microsoft excel training - Excel cell references with a skip
Resolved · Low Priority · Version Standard
Reply Tue 28 Aug 2007, 16:48Delegate Minnaar said...
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?
For upcoming training course dates see: Pricing & availability
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
|