Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Reference Formulas
Trying to figure out how I was able to do this in the past...reference
another wkbook (Sourcefile), include ALL wksheets in the formula either Globally or Sheet1:Sheet50. Using a Source file which has data labels say, in column A and the corresponding numbers in Columns B, C, D, E, F (these represent year1 to year5) for Product A. There are 50 products so there are 50 wksheets most of which have the same format, but not all of them. The Dependent file consists of 5 Wksheets, each with a tab named for years 1 through 5. On the first wksheet (Year1) Col A consists of the Product ID ex. [01AA, 01AB, 01AC, 02AA, 02AB, 02AC...]; wksheet 2 (Year2) Col A has Product ID ex. [101AA, 101AB, 101AC...]; wksheet 3 (Year3) Col A has Prod. ID ex. [201AA, 201AB, 201AC...]. Col E is where I want to use the Reference formulas that retrieve the values (Prices) from the Source file range (A3:P80)...in which Col E is where Prices are. Some of the formulas I've used are Standard Vlookup; Vlookup&Indirect cominations; INDIRECT(CONCATENATE("'[1XLSourceFile.xls]",A6,"'!$E$49")) where e49 is where the Total Price is in some of the wksheets...Since all wksheets don't have the Total on row 49 I thought I would need to incorporate some type of Lookup in the formula...Whew! Finally, All this needs to be acheived by ONLY sending the Dependent File to Customer, NOT with the Source File... As I've stated before, I've been able to do this in the past somehow, but don't have acces to any of my old files with these types of examples...Sorry for the long explanation...Thanks for any help that can be provided!!! BMW |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with formulas changing cell reference | Excel Discussion (Misc queries) | |||
Is there a way to list the formulas that reference a name in workb | Excel Discussion (Misc queries) | |||
Excel: hold a reference to a single cell when copying formulas? | Excel Worksheet Functions | |||
Formulas: Keeping same row/column reference when columns are inser | Excel Discussion (Misc queries) | |||
Relative worksheet reference in 3-D formulas? | Excel Worksheet Functions |