How to change the Tab reference in a forumla multiple times

**akberpaf** · 12-24-2015, 12:47 PM

Hi,

I am NEWBIE so please excuse my ignorance. I have searched the form for this issue and was only able to fine one reference and that was not very clear.

http://www.excelforum.com/excel-gene...t-numbers.html

I am trying to change the "#MN0001" to #MN0002, #MN0003, #MN0004.... etc. by dragging the formula I have to change this 48 times and in 8 different tabs! so if there is a formula that can help me do that It would be great.

Here is my current formula: =IF(OR(ISBLANK('#MN0001'!$B$6)), "", '#MN0001'!$B$6)

So if can just change the '#MN0001' to '#MN0002' (twice in the formula as stated above) that would be very very very helpful

Please help !!! and Marry Christmas and Happy Holidays to all

**ben_hensel** · 12-24-2015, 01:02 PM

Can you just mass-replace with Replace (CTRL+H)?

If you want it draggable I think you need something like this:

Please Login or Register  to view this content.

(I removed the superfluous OR statement)

How that works is, it concatenates three strings together:
'#MN
TEXT( ROW(A1), "0000")
'!B6

Where the middle line is the number of row A1 (so, it's 1) and because it's a relative reference it will pull down to A2, A3, etc.
Meanwhile, the TEXT function forces the format to be a four-digit number with leading zeros:
0001, 0002, ... , 0011, etc.

Then INDIRECT tells excel to read that directly as a positional reference, so it will go look for cell B6 on sheet #MN0001 or whatever.

**akberpaf** · 12-24-2015, 01:13 PM

Thanks a lot. Much appreciated sir.

How to change the Tab reference in a forumla multiple times

LinkBack

Thread Tools

Rate This Thread

Display

How to change the Tab reference in a forumla multiple times

Re: How to change the Tab reference in a forumla multiple times

Re: How to change the Tab reference in a forumla multiple times

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Need to change a forumla to look at multiple columns against a drop down selection.

[SOLVED] VBA - Compare and replace multiple times based on matched reference numbers

[SOLVED] Formula Fill to Cells: Only change certain cell references in a multi-reference forumla

[SOLVED] Puzzled by a simple forumla to sum times based on date criteria

[SOLVED] copy template multiple times and change a cell on each

VBA to change chart Source Data multiple times

[SOLVED] Is there a forumla to link an absolute cell reference in multiple.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions