You can have range names with the same names on different sheets, and Excel
won't confuse them.
Deleting your old sheet is going to orphan its range names and/or invalidate
them from the VLookUps. Once that's been done, it isn't going to matter
that you bring in a new sheet with the same range names ... the damage is
already done.
You probably want to simply copy the new data over the old, keeping the same
sheet. However, you might then need to re-establish the range names, if the
number of rows differs.
You haven't said whether you are using VBA code here. If you are, then you
simply need to get the VBA to redefine the range names. Or work without
range names at all ... they really don't add much when using VBA.
If your range names are purely intended to find the row extent of the data,
then dynamic range names might be the way to go, if you can't simply
dispense with them entirely and determine the data extent directly from the
VBA.
Regards, Dave S
----- Original Message -----
From: "mickey11030" <mickey11030@yahoo.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Wednesday, November 02, 2011 8:42 PM
Subject: [ExcelVBA] Replacing a Sheet with One of the Same Name
>I have a workbook with 2 sheets. On Sheet1 I have a VLOOKUP formula that
>references a range name FlightNumbers on the second sheet named Flight
>Numbers. I have received an updated list of flight numbers. I thought the
>easiest way to incorporate this new data would be to first define the range
>name FlightNumbers on the new sheet to include all the data, then delete my
>FlightNumbers sheet and finally to drag the new FlighNumbers sheet into my
>workbook. But all my VLOOKUP formulas now show #REF.
>
> How should I incorporate the new data into my workbook?
> Thanks,
>
>
>
> ------------------------------------
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
> here: http://www.mousetrax.com/techtrax to enter the ezine, then search
> the ARCHIVES for EXCEL VBA.
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 10.0.1411 / Virus Database: 2092/3990 - Release Date: 11/01/11
>
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar