Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
TLK40us
 
Posts: n/a
Default Dynamic Column VlookUps Based on Week Number

I have a YTD spread sheet that I'm building using a VlookUp table. The
LookUp table is built from a cell referenced worksheet from an AS/400 static
spreadsheet. Each week this I download the AS/400 information then copy and
special paste sheet to the cell referenced worksheet. This information is
feeds up to the YTD sheet that provides trend data for the year. By entering
the next Week number how do I build a dynamic VlookUp column that by entering
a the week number the data will dynamically populate the next column builidng
to the prior weeks YTD spreadsheet.
  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi
not really sure about your data layout. Could you provide some example
rows of your data and your expected result?

--
Regards
Frank Kabel
Frankfurt, Germany


TLK40us wrote:
I have a YTD spread sheet that I'm building using a VlookUp table.
The LookUp table is built from a cell referenced worksheet from an
AS/400 static spreadsheet. Each week this I download the AS/400
information then copy and special paste sheet to the cell referenced
worksheet. This information is feeds up to the YTD sheet that
provides trend data for the year. By entering the next Week number
how do I build a dynamic VlookUp column that by entering a the week
number the data will dynamically populate the next column builidng to
the prior weeks YTD spreadsheet.


  #3   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

You could use IF with your VLookup formulas, to return an empty string
if the week's data hasn't been uploaded yet. That way, you could enter
all the formulas for the year on the YTD sheet, and they'll populate as
data is uploaded.

There's an example he

http://www.contextures.com/xlFunctions02.html#IF

TLK40us wrote:
I have a YTD spread sheet that I'm building using a VlookUp table. The
LookUp table is built from a cell referenced worksheet from an AS/400 static
spreadsheet. Each week this I download the AS/400 information then copy and
special paste sheet to the cell referenced worksheet. This information is
feeds up to the YTD sheet that provides trend data for the year. By entering
the next Week number how do I build a dynamic VlookUp column that by entering
a the week number the data will dynamically populate the next column builidng
to the prior weeks YTD spreadsheet.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

  #4   Report Post  
tlk40us
 
Posts: n/a
Default

Frank, thanks for the reply. I have multiple data sheets that moves my
informtion up to my ended results.

First is my AS/400 Spyview report. This information is static with reports
results set in specific cells. To extract the 20 numbers needed, I have a
second worksheet that references the spyview report. It's nothing more then
B13=Datafile!AY36, B14=Datafile!AV36, etc.

This Worksheet has two rows, Text and Numbers. It provides the same formate
as the weekly trend on another worksheet. This table is defined with a name
for the lookup.

A thrid work sheet is where I'm having the trouble. I can do a lookup to
populate the information in to the next week, however what I'd like to do is
by indicating the week number, the data would populate the correct week
column. I know this isn't a lot of data, however I have 120 branches in
individual spread sheets

I hope this my help.
"Frank Kabel" wrote:

Hi
not really sure about your data layout. Could you provide some example
rows of your data and your expected result?

--
Regards
Frank Kabel
Frankfurt, Germany


TLK40us wrote:
I have a YTD spread sheet that I'm building using a VlookUp table.
The LookUp table is built from a cell referenced worksheet from an
AS/400 static spreadsheet. Each week this I download the AS/400
information then copy and special paste sheet to the cell referenced
worksheet. This information is feeds up to the YTD sheet that
provides trend data for the year. By entering the next Week number
how do I build a dynamic VlookUp column that by entering a the week
number the data will dynamically populate the next column builidng to
the prior weeks YTD spreadsheet.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I need week number in excell from a date, first week must be mini. jPeich Excel Discussion (Misc queries) 4 January 5th 05 02:21 AM
Putting text in a column based on variable text from another colum Jacky D. Excel Discussion (Misc queries) 1 December 16th 04 07:09 PM
Convert week number into calendar month? WickyWick Excel Worksheet Functions 2 November 9th 04 10:01 PM
GradeBook WannaKooky Excel Worksheet Functions 1 November 4th 04 03:23 PM
returning a text cell based on a number cell Josh7777777 Excel Worksheet Functions 2 November 2nd 04 08:42 PM


All times are GMT +1. The time now is 09:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"